clear combo boxes in open workbook procedure

[sulley333]

I am looking to have all the combo boxes on my dashboard sheet cleared when the workbook is opened. Is this possible?

Thanks,
Lauren

 

[Domenic]

First, open the Visual Basic Editor and place the following code in a regular module (Alt+F11 > Insert > Module)...

[font=Courier New][color=darkblue]Sub[/color] ClearComboBoxes()

    [color=darkblue]Dim[/color] Ctrl [color=darkblue]As[/color] OLEObject
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] Ctrl [color=darkblue]In[/color] Worksheets("Sheet1").OLEObjects
        [color=darkblue]If[/color] TypeName(Ctrl.Object) = "ComboBox" [color=darkblue]Then[/color]
            Ctrl.Object.Value = ""
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] Ctrl
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

Then, place the following code in the module for 'ThisWorkbook' (in the project explorer window on the left, right-click 'ThisWorkbook', and select 'View Code')...

[font=Courier New][color=darkblue]Private[/color] [color=darkblue]Sub[/color] Workbook_Open()
    [color=darkblue]Call[/color] ClearComboBoxes
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

Then, exit the Visual Basic Editor (Alt+Q), and save your workbook. Now, whenever your workbook is opened, the ComboBoxes on the sheet specified within the code will be cleared.

 

[sulley333]

Thanks so much!! That worked perfectly!!!

 

[Domenic]

You're very welcome! Thanks for the feedback!