Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Macro to make all checkboxes false and clear all comboxes
My macro (that someone from here helped me with a while ago) works great.
But I added a combobox and that one doesnt clear when I execute the macro. Combo 1,2,3, clear but the 4th doesnt ? Any ideas ? Here is my macro. Thanks, Yosef Option Explicit Sub testme01() Dim OLEObj As OLEObject For Each OLEObj In ActiveSheet.OLEObjects If TypeOf OLEObj.Object Is MSForms.CheckBox Then OLEObj.Object.Value = False End If Next OLEObj Range("B2:B5").Select Range("B5").Activate Selection.ClearContents End Sub |
#2
|
|||
|
|||
Combo 1,2,3, clear but the 4th doesnt ?
That's remarkable because the macro only affect checkboxes. I think you need to add combobox specifc code Sub testme02() Dim OLEObj As OLEObject For Each OLEObj In ActiveSheet.OLEObjects If TypeOf OLEObj.Object Is MSForms.CheckBox Then OLEObj.Object.Value = False ElseIf TypeOf OLEObj.Object Is MSForms.ComboBox Then OLEObj.Object.Clear End If Next OLEObj End Sub -- Jim "ynissel" wrote in message ... | My macro (that someone from here helped me with a while ago) works great. | But I added a combobox and that one doesnt clear when I execute the macro. | Combo 1,2,3, clear but the 4th doesnt ? | Any ideas ? | Here is my macro. | Thanks, | Yosef | | Option Explicit | Sub testme01() | | Dim OLEObj As OLEObject | For Each OLEObj In ActiveSheet.OLEObjects | If TypeOf OLEObj.Object Is MSForms.CheckBox Then | OLEObj.Object.Value = False | End If | Next OLEObj | Range("B2:B5").Select | Range("B5").Activate | Selection.ClearContents | End Sub |
#3
|
|||
|
|||
OK. Ill try it. But Im not sure why theother 3 clear ?
Just tried and I got an error on OLEObj.Object.clear Is the syntax correct ? Thanks again, Yosef "Jim Rech" wrote: Combo 1,2,3, clear but the 4th doesnt ? That's remarkable because the macro only affect checkboxes. I think you need to add combobox specifc code Sub testme02() Dim OLEObj As OLEObject For Each OLEObj In ActiveSheet.OLEObjects If TypeOf OLEObj.Object Is MSForms.CheckBox Then OLEObj.Object.Value = False ElseIf TypeOf OLEObj.Object Is MSForms.ComboBox Then OLEObj.Object.Clear End If Next OLEObj End Sub -- Jim "ynissel" wrote in message ... | My macro (that someone from here helped me with a while ago) works great. | But I added a combobox and that one doesnt clear when I execute the macro. | Combo 1,2,3, clear but the 4th doesnt ? | Any ideas ? | Here is my macro. | Thanks, | Yosef | | Option Explicit | Sub testme01() | | Dim OLEObj As OLEObject | For Each OLEObj In ActiveSheet.OLEObjects | If TypeOf OLEObj.Object Is MSForms.CheckBox Then | OLEObj.Object.Value = False | End If | Next OLEObj | Range("B2:B5").Select | Range("B5").Activate | Selection.ClearContents | End Sub |
#4
|
|||
|
|||
Is the syntax correct ?
Yes. I always run code before I post it. Maybe we're running different Excel versions. Something weird is happening since your code that checks for checkboxes (If TypeOf OLEObj.Object Is MSForms.CheckBox Then) before doing anything should not be clearing combos. -- Jim "ynissel" wrote in message ... | OK. Ill try it. But Im not sure why theother 3 clear ? | | Just tried and I got an error on | OLEObj.Object.clear | | Is the syntax correct ? | Thanks again, | Yosef | | "Jim Rech" wrote: | | Combo 1,2,3, clear but the 4th doesnt ? | | That's remarkable because the macro only affect checkboxes. I think you | need to add combobox specifc code | | Sub testme02() | Dim OLEObj As OLEObject | For Each OLEObj In ActiveSheet.OLEObjects | If TypeOf OLEObj.Object Is MSForms.CheckBox Then | OLEObj.Object.Value = False | ElseIf TypeOf OLEObj.Object Is MSForms.ComboBox Then | OLEObj.Object.Clear | End If | Next OLEObj | End Sub | | | -- | Jim | "ynissel" wrote in message | ... | | My macro (that someone from here helped me with a while ago) works great. | | But I added a combobox and that one doesnt clear when I execute the macro. | | Combo 1,2,3, clear but the 4th doesnt ? | | Any ideas ? | | Here is my macro. | | Thanks, | | Yosef | | | | Option Explicit | | Sub testme01() | | | | Dim OLEObj As OLEObject | | For Each OLEObj In ActiveSheet.OLEObjects | | If TypeOf OLEObj.Object Is MSForms.CheckBox Then | | OLEObj.Object.Value = False | | End If | | Next OLEObj | | Range("B2:B5").Select | | Range("B5").Activate | | Selection.ClearContents | | End Sub | | | |
#5
|
|||
|
|||
Sorry - I didnt mean to imply you didnt :-)
I am running EXcel 2003. This is my current marco -and it does clear the first 3 comboboxes. Dim OLEObj As OLEObject 'For Each OLEObj In ActiveSheet.OLEObjects ' If TypeOf OLEObj.Object Is MSForms.CheckBox Then ' OLEObj.Object.Value = False ' End If 'Next OLEObj ' Range("B2,B4,B5").Select 'Range("B5").Activate 'Selection.ClearContents When I copied yours in I get a runtime error and when I debug it highlights OLEObj.Object.Clear Any ideas ? Thanks, Yosef "Jim Rech" wrote: Is the syntax correct ? Yes. I always run code before I post it. Maybe we're running different Excel versions. Something weird is happening since your code that checks for checkboxes (If TypeOf OLEObj.Object Is MSForms.CheckBox Then) before doing anything should not be clearing combos. -- Jim "ynissel" wrote in message ... | OK. Ill try it. But Im not sure why theother 3 clear ? | | Just tried and I got an error on | OLEObj.Object.clear | | Is the syntax correct ? | Thanks again, | Yosef | | "Jim Rech" wrote: | | Combo 1,2,3, clear but the 4th doesnt ? | | That's remarkable because the macro only affect checkboxes. I think you | need to add combobox specifc code | | Sub testme02() | Dim OLEObj As OLEObject | For Each OLEObj In ActiveSheet.OLEObjects | If TypeOf OLEObj.Object Is MSForms.CheckBox Then | OLEObj.Object.Value = False | ElseIf TypeOf OLEObj.Object Is MSForms.ComboBox Then | OLEObj.Object.Clear | End If | Next OLEObj | End Sub | | | -- | Jim | "ynissel" wrote in message | ... | | My macro (that someone from here helped me with a while ago) works great. | | But I added a combobox and that one doesnt clear when I execute the macro. | | Combo 1,2,3, clear but the 4th doesnt ? | | Any ideas ? | | Here is my macro. | | Thanks, | | Yosef | | | | Option Explicit | | Sub testme01() | | | | Dim OLEObj As OLEObject | | For Each OLEObj In ActiveSheet.OLEObjects | | If TypeOf OLEObj.Object Is MSForms.CheckBox Then | | OLEObj.Object.Value = False | | End If | | Next OLEObj | | Range("B2:B5").Select | | Range("B5").Activate | | Selection.ClearContents | | End Sub | | | |
#6
|
|||
|
|||
I'm guessing that the reason you got 3 comboboxes to clear was that you used
linked cells in the B2:B5 range. And when you cleared those cells, you cleared the combobox. One quick and dirty solution would be to just clear that additional linked cell. This portion: Range("B2:B5").Select Range("B5").Activate Selection.ClearContents could be replaced with: range("b2:B5").clearcontents (and B5 isn't actually selected!) Just change that range to include the other linked cell in that statement: range("b2:B6").clearcontents or range("b2:B5,d999").clearcontents ps. I didn't get an error with Jim's code. But if I had a linked cell, it didn't get cleared. But this cleared the combobox and the linked cell: Option Explicit Sub testme02A() Dim OLEObj As OLEObject For Each OLEObj In ActiveSheet.OLEObjects If TypeOf OLEObj.Object Is MSForms.CheckBox Then OLEObj.Object.Value = False ElseIf TypeOf OLEObj.Object Is MSForms.ComboBox Then OLEObj.Object.Value = "" End If Next OLEObj End Sub ynissel wrote: My macro (that someone from here helped me with a while ago) works great. But I added a combobox and that one doesnt clear when I execute the macro. Combo 1,2,3, clear but the 4th doesnt ? Any ideas ? Here is my macro. Thanks, Yosef Option Explicit Sub testme01() Dim OLEObj As OLEObject For Each OLEObj In ActiveSheet.OLEObjects If TypeOf OLEObj.Object Is MSForms.CheckBox Then OLEObj.Object.Value = False End If Next OLEObj Range("B2:B5").Select Range("B5").Activate Selection.ClearContents End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros to delete check boxes | Excel Discussion (Misc queries) | |||
link data to new workbook | Excel Discussion (Misc queries) |