Clear comboboxes when opening worksheet

[Bryan_Daniels]

Hi,

I have a worksheet that has 21 comboboxes.  I use these to configure a part number that the user can copy to another program.  What I need to happen is that when the Worksheet is opened, the comboboxes are blank.  I need to make sure the user fills out each one and if there is already data there, it will be too easy to forget what you've already done.  I don't want to clear the list boxes, I just need to start with clean slate each time.

Optionally, it could be created clear, so it just opens that way, and I could make it where it can't be saved.  Either way, I don't know how to clear the comboboxes, and hence the part number that gets its information from the comboboxes.  I hope I am being clear.  I have tried the below code which I found online, but it does not work.  It skips code as if it does not see a ComboBox.   Thank you!

Option Explicit

Private Sub Worksheet_Activate()
Dim sht As Worksheet
Dim oOLE As Object

Set sht = Sheets("Sheet1")
Application.ScreenUpdating = False

With sht
For Each oOLE In sht.OLEObjects
If TypeName(oOLE.Object) = "ComboBox" Then
oOLE.Object.Value = Null
End If
Next
End With

Application.ScreenUpdating = True

End Sub

[#2 · Accepted solution]

@Bryan_Daniels 

To clear multiple CheckBoxes in a worksheet and make sure they clear each time the worksheet is opened, you can use the following code:

Private Sub Workbook_Open()
    Dim cb As OLEObject
    
    For Each cb In ActiveSheet.OLEObjects
        If TypeName(cb.Object) = "CheckBox" Then
            cb.Object.Value = False
        End If
    Next cb
End Sub

This code should be pasted into the workbook's code module. To do this, open the Visual Basic Editor (VBE) by pressing the key combination "ALT + F11". Then double-click "ThisWorkbook" in the project window and paste the code into the window.

The code uses the Workbook_Open() event, which runs automatically when the workbook is opened. This will find all checkboxes in the active worksheet and set them to the value "False" (unchecked).

Once you save the code, the checkboxes will clear each time you open the worksheet.

Example file inserted: Open the file, click all checkboxes, then close the file and open from the new one. The checkboxes will be empty.

I hope this could help you!

[#3]

Hi NicolinoDE! Thank you for your answer. Since your code was for checkboxes, I just changed the CheckBox to ComboBox and the False to Null, and it worked like a charm. I very much appreciate it!

[#4]

I'm glad I could help!
Checkbox designation is in German the combo box, forgot to rename it :).