=EMBED("Forms.CheckBox.1","")

[John]

Hi Everyone
I 've got a form with 107 CheckBoxes from the "Control Toolbar" and would
like to have a macro to go through the list and uncheck those that are
selected ( Check).
The Macro Recorder will not do it.
Any help would be appreciated.
Regards
John

 

[Dave Peterson]

On a worksheet, right?

Option Explicit
Sub testme01()

Dim OLEObj As OLEObject

For Each OLEObj In Worksheets("sheet999").OLEObjects
If TypeOf OLEObj.Object Is MSForms.CheckBox Then
OLEObj.Object.Value = False
End If
Next OLEObj

End Sub

 

[Joel]

Sub UnCheck()

For Each shp In ActiveSheet.OLEObjects
If shp.progID = "Forms.CheckBox.1" Then
shp.Object.Value = False
End If
Next shp
End Sub

 

[John]

Hi Dave
Thank you for your time to reply but I'm getting an error "Subscript out of
range" .
I also received a reply from Joel and is macro is working.
Thank you again
Best Wishes for the Holidays
John

 

[John]

Hi Joel
Its working fine.
Many thanks and all the best to You for the Holidays
Regards
John

 

[Dave Peterson]

Did you change the sheet999 to the name of the sheet that had the checkboxes?

 

[John]

Hi Dave
Now I did and it works fine. As you can see i'm no expert with macros
I will keep your macro for future use, I intend to build myself a library of
macros and possibly start learning them.
Thanks again
John

 

[Dave Peterson]

I should have included a note telling you what to change.

But glad you have a solution or two.

Hi Dave
Now I did and it works fine. As you can see i'm no expert with macros
I will keep your macro for future use, I intend to build myself a library of
macros and possibly start learning them.
Thanks again
John