Reset Combo Dropdown List to Default Values

Question

I have a worksheet with many activex combo drop down list.  When I open the workbook all combo list are set to their defaults.  What I would like to do is put a command button on the work sheet labeled "RESET COMBOS" that when selected would reset all combo drop downs to their original default values.  I am a beginner with VBA and I am not sure exactly how to do this.  Any help with this would be greatly appreciated.

Answer 1

Remove the line:

Sub SetToDefault()

Answer 2

Try this code:

Sub SetToDefault()

    Dim sht As Worksheet

    Dim cmb As ComboBox

    Dim iCount As Integer

    Dim i As Integer

    Set sht = ThisWorkbook.Worksheets("Sheet7")

    iCount = sht.OLEObjects.Count

    For i = 1 To iCount

        If TypeName(sht.OLEObjects(i).Object) = "ComboBox" Then

            Set cmb = sht.OLEObjects(i).Object

            cmb.ListIndex = -1 

        End If

    Next i

End Sub

1. Change Sheet7 (highlighted) to actual name of sheet

2. cmb.ListIndex =-1 clears the combobox value so it becomes blank, if you want to set it to the 1st item in the list, then replace -1 to 0

Answer 3

Also if you copied the code into the code for the button i.e. Private Sub CommandButton1_Click() then make sure to remove the first line in the code

Sub SetToDefault() = needs to be removed

and make sure the last line is End Sub and only one of them

i.e

End Sub

not

End Sub

End Sub

Answer 4

Check that the code's last line is: End Sub

If it's not there, enter it.

Answer 5

Thanks Howard,

The code you sent works very good.  My only problem now is that I could only get it to work in my General area in visual basic and only when I run the run script button.  I have tried entering it in the command button section Private Sub CommandButton1_Click(), but it continues to give a me a Compile error:  Expected End Sub.  Sorry for not having very much knowledge in this area.