I’ve been doing a lot of Excel development to create several very advanced WorkSheet forms for data entry.

I’ve experienced some serious issues trying to use Data Validation in which Excel crashes randomly when the feature is used (and yes I tried an Office repair, and yes I tried it in Excel 2013 and 2016, …). Since I cannot deliver such an unstable product to my clients, I ended up switching over to using ActiveX Combo boxes.

Now, all that said, if you have a form, any form, you are going to need some sort of Reset button. Now you can reset each combo box individually by doing something along the lines of:

ActiveWorkbook.Worksheets("TimeSheet").YourComboBoxName.value = ""

but then if your design evolves, then you have to recode and that can becomes very tedious, time consuming and prone to mistakes and oversights!

Anyways, long story short, I wanted a simple function that would loop through all the combo boxes on a sheet and reset them all. I didn’t want to have know their names, how many of them existed, … I just wanted a function to handle it all. Below is the procedure I came up with.

'---------------------------------------------------------------------------------------
' Procedure : ComboBox_ClearAll
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Clears all the combo boxes within the specified WorkSheet
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None Required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sht         A WorkSheet Object
'
' Usage:
' ~~~~~~
' Call ComboBox_ClearAll(ActiveWorkBook.WorkSheets("TimeSheet"))
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2017-09-28              Initial Release
'---------------------------------------------------------------------------------------
Public Sub ComboBox_ClearAll(sht As Excel.Worksheet)
    On Error GoTo Error_Handler
    Dim oOLE                  As Object

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

Error_Handler_Exit:
    On Error Resume Next
    If Not oOLE Is Nothing Then Set oOLE = Nothing
    If Not sht Is Nothing Then Set sht = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: ComboBox_ClearAll" & vbCrLf & _
           "Error Description: " & Err.description, _
           vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

You could create a similar procedure for just about any other control.