Reset a field based on selection of another in VBA

Question

I have a drop down field in VBA called cbo_deptCode. I would like another set of fields called cbo_moduleCode and cbo_moduleName to be cleared of any current entry each time the user makes a selection from the drop down cbo_deptCode. The combo boxes are form controls. How can I go about achieving this?

Answer 1

Assign a macro to cbo_deptCode.

In the macro have code like the following:

Sub cbo_deptCode_Change()
    'Update selected index for combo boxes to 0 I.E. no selection
    ActiveSheet.Shapes("cbo_moduleCode").OLEFormat.Object.Value = 0
    ActiveSheet.Shapes("cbo_moduleName").OLEFormat.Object.Value = 0
End Sub

If cbo_moduleCode and cbo_moduleName are not on the same sheet as cbo_deptCode, you'll need to specify the sheet instead of calling the ActiveSheet. Also, this will fail if those aren't the actual control names.

EDIT: If you are actually using Active-X controls (though you said you were using form controls), the format is simpler: Sheet.[controlname].value = "" I.E ActiveSheet.cbo_moduleCode.Value = "" Or the more convoluted method (not recommended) that involves Shapes:

ActiveSheet.Shapes("cbo_moduleCode").OLEFormat.Object.Object.Value = ""