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

>>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" <[email protected]> wrote in message
news:[email protected]...
| 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

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" <[email protected]> wrote in message
> news:[email protected]...
> | 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
>
>
>

>>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" <[email protected]> wrote in message
news:[email protected]...
| 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" <[email protected]> wrote in message
| > news:[email protected]...
| > | 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
| >
| >
| >

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" <[email protected]> wrote in message
> news:[email protected]...
> | 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" <[email protected]> wrote in message
> | > news:[email protected]...
> | > | 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
> | >
> | >
> | >
>
>
>

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