Resetting Activex comboboxes to blank

[Susan]

I've searched posts thoroughly, and I believe I've got the syntax
right, but it won't work.
'xxxxxxxxxxxxxxxxxxxxx
Private Sub CommandButton1_Click()

Dim oleObj As OLEObject
Dim x As Long
Dim myCmbo As String

On Error GoTo Rats

Application.EnableEvents = True

x = 1
For Each oleObj In ActiveSheet.OLEObjects
myCmbo = "Combobox" & x
If oleObj.Name = myCmbo Then '<---skips right to end-if
oleObj.ListIndex = -1
x = x + 1
End If
Next oleObj

Application.EnableEvents = False

ActiveSheet.Range("h:h").ClearContents
ActiveSheet.Range("h1").Formula = "=sum(H5:H65)"

Application.EnableEvents = True

Exit Sub

'++++++++++++++++++++++++++++
Rats:
MsgBox "Error"
Application.EnableEvents = True
Exit Sub
'++++++++++++++++++++++++++++

End Sub
'xxxxxxxxxxxxxxxxxxxxxxxxxxxx

even when the Intellisense says that oleObj.Name = myCmbo (the If
test), it skips right over what I want it to do & goes right to "End
If". arrrrg! I can't even test to see if .....oleObj.ListIndex =
-1...... will do what I want because I can't get it to trigger. what
am I doing wrong?? at first I thought it was because I had
Application.EnableEvents = False (because I've got a sheet_calculate
sub that was triggering), but I deliberately turned it back on & it
still won't catch. any ideas?
thanks
Susan

 

[Dave Peterson]

Did you want to reset all the comboboxes on the worksheet?

If yes:

Option Explicit
Private Sub CommandButton1_Click()

Dim OLEObj As OLEObject

For Each OLEObj In Me.OLEObjects
If TypeOf OLEObj.Object Is MSForms.ComboBox Then
OLEObj.Object.ListIndex = -1
End If
Next OLEObj

Application.EnableEvents = False
Me.Range("h:h").ClearContents
Me.Range("h1").Formula = "=sum(H5:H65)"
Application.EnableEvents = True

End Sub

Me is a keyword that represents the object that owns the code. In this case the
worksheet with the button and comboboxes.

 

[Susan]

yes!!!!!!! hurrah!
thank you!
it was the "me" keyword that i was lacking, because i had tried that
exact coding (and i was missing the addition of .object) except i was
using activesheet.oleobjects.
thanks a lot, it works perfectly.
susan