Resetting Multiple Comboboxes To ListIndex 0

[jbrown2424]

I have a workbook with >100 comboboxes and would like to have a command button reset all of these to listindex = 0.
Do I have to list each of these comboboxes individually...

Combobox1.listindex = 0: Combobox2.listindex = 0:.....................Combobox100.listindex = 0

or is there a way to reset all of these with a simplified, shortened code?

Thank you

 

[6StringJazzer]

Where are they located? One worksheet? Many worksheets? A UserForm?

 

[jbrown2424]

Where are they located? One worksheet? Many worksheets? A UserForm?

They are located on 2 separate worksheets (sheet6 and sheet14)

 

[6StringJazzer]

This will set all comboboxes in all sheets to 0. If you want to limit it to just those two the code needs a tweak.

Be advised that setting the Listindex to 0 will select the first item in the list.

Public Sub ResetComboboxes()

    Dim WS As Worksheet
    Dim O As OLEObject
    
    
    For Each WS In Worksheets
    
      For Each O In WS.OLEObjects
      
         If TypeName(O.Object) = "ComboBox" Then
            O.Object.ListIndex = 0
         End If
         
      Next O
      
   Next WS

End Sub

 

[jbrown2424]

This will set all comboboxes in all sheets to 0. If you want to limit it to just those two the code needs a tweak.

Be advised that setting the Listindex to 0 will select the first item in the list.

Public Sub ResetComboboxes()

    Dim WS As Worksheet
    Dim O As OLEObject
   
   
    For Each WS In Worksheets
   
      For Each O In WS.OLEObjects
     
         If TypeName(O.Object) = "ComboBox" Then
          [COLOR=rgb(247, 218, 100)]  [/COLOR][COLOR=rgb(41, 105, 176)]O.Object.ListIndex = 0[/COLOR]
         End If
        
      Next O
     
   Next WS

End Sub

The code is currently setting the ListIndex of roughly one-third of the comboboxes to 0 and then I get an error:

I get the following:
run-time error '380'
Could not set the ListIndex property. Invalid property or value.

Upon clicking debug, it highlights: O.Object.ListIndex = 0

Any thoughts?

Also, how would I tweak this code to only reset the comboboxes on sheets6 and sheet14?

Thank you for your help!

 

[6StringJazzer]

I can't troubleshoot that error without your file.

For two specific sheets you can do this. Are Sheet6 and Sheet14 the tab names or the code names? I am going to assume they are tab names.

Public Sub ResetComboboxes()

   ResetSheetComboboxes WS:=Worksheets("Sheet6")
   ResetSheetComboboxes WS:=Worksheets("Sheet14")
   
End Sub

Public Sub ResetSheetComboboxes(WS As Worksheet)

    Dim O As OLEObject
    
   For Each O In WS.OLEObjects
   
      If TypeName(O.Object) = "ComboBox" Then
         O.Object.ListIndex = 0
      End If
      
   Next O

End Sub

 

[jbrown2424]

I can't troubleshoot that error without your file.

For two specific sheets you can do this. Are Sheet6 and Sheet14 the tab names or the code names? I am going to assume they are tab names.

Public Sub ResetComboboxes()

   ResetSheetComboboxes WS:=Worksheets("Sheet6")
   ResetSheetComboboxes WS:=Worksheets("Sheet14")
 
End Sub

Public Sub ResetSheetComboboxes(WS As Worksheet)

    Dim O As OLEObject
 
   For Each O In WS.OLEObjects
 
      If TypeName(O.Object) = "ComboBox" Then
         O.Object.ListIndex = 0
      End If
   
   Next O

End Sub

I can't troubleshoot that error without your file.

For two specific sheets you can do this. Are Sheet6 and Sheet14 the tab names or the code names? I am going to assume they are tab names.

Public Sub ResetComboboxes()

   ResetSheetComboboxes WS:=Worksheets("Sheet6")
   ResetSheetComboboxes WS:=Worksheets("Sheet14")
 
End Sub

Public Sub ResetSheetComboboxes(WS As Worksheet)

    Dim O As OLEObject
  
   For Each O In WS.OLEObjects
 
      If TypeName(O.Object) = "ComboBox" Then
         O.Object.ListIndex = 0
      End If
    
   Next O

End Sub

I don't expect you to go through every line of the workbook to figure this out.
Any idea what the error code "run-time error '380', Could not set the ListIndex property. Invalid property or value." typically points to so I know what type of error I am looking for?

Sheet14 and Sheet6 are the code names. The tab names are Account Information and Product Overview

 

[6StringJazzer]

Typically it would mean that the object is not a combobox (or listbox). But there is a check for that so I don't know how it's possible. When the code stops I would go into debug mode and query O.Object.Name to see what the object is and what sheet it's on.