loop thru checkboxes

[mrinal saha]

Hi

I have 4 checkboxes in sheet1. want to use a loop in workbook open event to uncheck the boxes if any. Well using the below code, but seems nothing happening.

For Each o In Sheets("Summary").OLEObjects
If TypeName(bject) = "checkbox" Then
If o = True Then
o = False
End If
End If
Next o


thankssss,
Mrinal

 

[Carl Colijn]

I have 4 checkboxes in sheet1. want to use a loop in workbook open event to uncheck the boxes if any. Well using the below code, but seems nothing happening.

For Each o In Sheets("Summary").OLEObjects
If TypeName(bject) = "checkbox" Then
If o = True Then
o = False
End If
End If
Next o

Hi Mrinal,

There are 2 problems with this code;

1) TypeName(bject) returns "CheckBox", which is typographically not equal to "checkbox", so you need to compare it not just with a simple "=". Instead use:

If LCase(TypeName(o.Object)) = "checkbox" Then

or:

If StrComp(TypeName(o.Object)), "checkbox", vbTextCompare) = 0 Then

but a more straight-forward method is using:

If TypeOf o.Object Is MSForms.CheckBox Then

That should take care of selecting only checkboxes.

2) Setting your o directly doesn't do what you think it does. You need to again use bject, since .Object contains the control itself. And then tack on an extra .Value to explicitly access the value of it. Which makes:

If o.Object.Value = True Then

Hope this helps!

 

[Ruddles]

They're ActiveX controls, yes? Try:-

  For Each o In Sheets("Summary").OLEObjects
  If TypeName(o.Object) = "[COLOR=red][B]C[/B][/COLOR]heck[COLOR=red][B]B[/B][/COLOR]ox" Then
     If o[COLOR=red][B].Object[/B][/COLOR] = True Then
        o[COLOR=red][B].Object[/B][/COLOR] = False
     End If
  End If
Next o