Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Not resetting all cells | Excel Programming | |||
publish activeX combobox and other activeX control | Excel Programming | |||
ActiveX comboboxes on worksheet (lost focus problems) | Excel Programming | |||
Dir function resetting | Excel Programming | |||
Used Range is not resetting | Excel Programming |