Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add and edit a shape in VBA | Excel Discussion (Misc queries) | |||
Specify shape dimensions | Excel Discussion (Misc queries) | |||
Shape coordinates | Excel Discussion (Misc queries) | |||
my curser changed from arrow shape to a cross shape???? | New Users to Excel | |||
Automatique Shape | Excel Discussion (Misc queries) |