PDA

View Full Version : Solved: Get state of checkboxes



JKwan
10-30-2009, 08:47 AM
I have n number of checkboxes on my spreadsheet. How do I get the state of each checkboxes? I used the SHAPES.COUNT to get the number of checkboxes, but then I got stuck in getting the state of the checkbox. This is what I have so far but I am really stuck


For Index = 1 To Sheet1.Shapes.Count
If Left(Sheet1.Shapes.Item(Index).Name, 8) = "CheckBox" Then

End If
Next


I did try using Sheet1.Checkbox1.Value, this gave me the state of CheckBox1, but I have n number of checkboxes.....

Thanks

mdmackillop
10-30-2009, 10:50 AM
Assuming ActiveX checkboxes

Option Explicit
Sub CheckBoxes()
Dim X As OLEObject, Msg As String
For Each X In ActiveSheet.OLEObjects
If X.progID = "Forms.CheckBox.1" Then _
Msg = Msg & X.Name & vbTab & X.Object & vbCr
Next
MsgBox Msg
End Sub

JKwan
10-30-2009, 11:01 AM
Thank you MD, that is what I am looking for!

mdmackillop
10-30-2009, 04:35 PM
I posted this KB Item (http://www.vbaexpress.com/kb/getarticle.php?kb_id=114)