PDA

View Full Version : Setting Value for Checkboxes



cycle_simon
01-29-2008, 03:00 PM
I have a worksheet with both checkboxes and textboxes from the Control toolbox.

I need to cycle through these shapes to clear any text in the textboxes and set the checkboxes to false (no checkmark).

I have found a way to clear the textboxes, but cannot find a way to identify/reference the checkboxes.



Sub ClearResponseBox()

Dim responsebox As Shape
Dim boxname As String
For Each responsebox In ActiveSheet.Shapes
boxname = responsebox.Name

If responsebox.Type = 12 And ActiveSheet.OLEObjects(boxname).Object.Text <> "" Then
ActiveSheet.OLEObjects(boxname).Object.Text = ""

Else

If responsebox.Type = 12 And ActiveSheet.OLEObjects(boxname).Object.Value <> False Then
ActiveSheet.OLEObjects(boxname).Object.Value = False

End If
End If

Next responsebox
End Sub


I feel lost again :doh:

Bob Phillips
01-29-2008, 03:06 PM
Dim obj As OLEObject
For Each obj In ActiveSheet.OLEObjects

If TypeName(obj.Object) = "TextBox" Then

obj.Object.Text = ""
ElseIf TypeName(obj.Object) = "CheckBox" Then

obj.Object.Value = False
End If
Next obj

cycle_simon
01-29-2008, 06:40 PM
Thanks so much. It works great!