Unset shape checkboxes with VBA
 

Hi NG,

I've got about 60 checkboxes on my spreadsheet (not in a user form!) and I'd
like to unset these checkboxes with VBA. So far I've come up with the
following:

Dim c As Shape

For Each c In ActiveSheet.Shapes
If Left(c.Name, 8) = "CheckBox" Then
ActiveSheets.Shapes.Object.Value = 0
End If
Next c

However, I cannot change the value, i.e. whether or not the checkboxes are
set or not. How do I address these shapes/checkboxes and set them to
unchecked?

--
Andreas

Unset shape checkboxes with VBA
 

Sorry, found the solution myself ...


Dim c As Shape

For Each c In ActiveSheet.Shapes
If Left(c.Name, 8) = "CheckBox" Then
ActiveSheet.OLEObjects(c.Name).Object.Value = 0
End If
Next c

--
Andreas

"Andreas Emmert" schrieb im Newsbeitrag
...
Hi NG,

I've got about 60 checkboxes on my spreadsheet (not in a user form!) and
I'd
like to unset these checkboxes with VBA. So far I've come up with the
following:

Dim c As Shape

For Each c In ActiveSheet.Shapes
If Left(c.Name, 8) = "CheckBox" Then
ActiveSheets.Shapes.Object.Value = 0
End If
Next c

However, I cannot change the value, i.e. whether or not the checkboxes are
set or not. How do I address these shapes/checkboxes and set them to
unchecked?

--
Andreas

Unset shape checkboxes with VBA
 

never say sorry for asking a question :)

in fact following would be a bit more efficient

a. it uses the object type thus making ik independent
of the name
b. it loops the oleobjects collection (subset of shapes)
c. it uses the variable c more efficiently
(u jump back to the entire collection using c.name, where you already
have the object c and could use c.object.value instead.


Sub UnCheck()
Dim c
For Each c In ActiveSheet.OLEObjects
If TypeName(c.Object) = "CheckBox" Then
c.Object.Value = 0
End If
Next
End Sub


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Andreas Emmert" wrote:

Sorry, found the solution myself ...


Dim c As Shape

For Each c In ActiveSheet.Shapes
If Left(c.Name, 8) = "CheckBox" Then
ActiveSheet.OLEObjects(c.Name).Object.Value = 0
End If
Next c