Resetting Activex comboboxes to blank
 

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

Resetting Activex comboboxes to blank
 

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 wrote:

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

Resetting Activex comboboxes to blank
 

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


On Oct 14, 3:30*pm, Dave Peterson wrote:
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 wrote:

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- Hide quoted text -

- Show quoted text -