CODEWITHSUNDEEP
excelvba
user9730643
user9730643

Reputation: 89

Clearing ActiveX dropdown boxes Error 438

I have a script that populates two ActiveX drop down menus. I have a reset button what when pressed runs a macro that populated the value field of the drop down menus. I have included the macro that is triggered when the reset button is pressed below.

Sub UnhideAll()

Application.ScreenUpdating = False
    Worksheets("Control Panel").Shapes.Range(Array("ComboBox1")).ControlFormat.Value = "Choosing Region"
    Worksheets("Control Panel").Shapes.Range(Array("ComboBox2")).ControlFormat.Value = "Choosing Office"
    Worksheets("Global").Columns.EntireColumn.Hidden = False
    Worksheets("Global").Rows.EntireRow.Hidden = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

I keep getting Run time error 438 Object doesn't support this property or method.

Upvotes: 0

Views: 211

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71207

Everything after Worksheets(...) is late-bound, because Worksheets yields an Object: you're essentially coding blindfolded. Help the compiler by telling it what interface you mean to work with - in this case, Worksheet:

Dim ctrlPanelSheet As Worksheet
Set ctrlPanelSheet = ActiveWorkbook.Worksheets("Control Panel")

Now member calls against ctrlPanelSheet will be compile-time validated, and you'll get IntelliSense/member-completion on its member calls.

That said, ControlFormat is a member of the Shape class, but you're invoking it against a ShapeRange object. When you make a [late-bound] member call against an object, for a member that doesn't exist on that object, you get run-time error 438.

But you don't need a ShapeRange when you're only working with a single control...

ctrlPanelSheet.Shapes("ComboBox1").ControlFormat.Value = "..."

Now, doing that will raise a type mismatch error - ComboBox1 looks much more like an ActiveX control than a form control (that would default to something like Drop Down 1) - you want to get that MSForms.ComboBox object, and you can get it from the sheet's OLEObjects collection:

Dim oleBox1 As OLEObject
Set oleBox1 = ctrlPanelSheet.OLEObjects("ComboBox1")

The OLEObject is wrapping your MSForms.ComboBox control:

Dim box1 As MSForms.ComboBox
Set box1 = oleBox1.Object

And now that you have a MSForms.ComboBox object, you can do everything you could normally do with a MSForms.ComboBox object - all with compile-time validation:

box1.Value = "Test"

Upvotes: 1

Related Questions