Forms Toolbar Checkbox

[Stubby]

Hi

I have a spreadsheet with various checkboxes from the Forms toolbar added to it. I canget code to run through and find each control however I can't get the code to search for just the checkboxes that have been selected. here's the code i am using

Sub chchk()


'Loop for Form controls.
Set myC = ActiveSheet.Shapes

For Each sh In myC
If sh.Type = msoFormControl Then

If sh.FormControlType = xlCheckBox Then
'**** check if check box selected
If sh.ControlFormat.Checked = True Then
myvar = sh.TopLeftCell.Address
MsgBox myvar
End If
End If
End If
Next sh

End Sub

any help much appreciated

 

[mikerickson]

Something like this

Sub test()
    Dim resultString As String
    Dim oneShape As Shape
    For Each oneShape In ActiveSheet.Shapes
        With oneShape
        If .Type = msoFormControl Then
            If .FormControlType = xlCheckBox Then
                If .ControlFormat.Value = xlOn Then
                    resultString = resultString & .Name & vbCr
                End If
            End If
        End If
        End With
    Next oneShape
    MsgBox resultString & "are checked."
End Sub

To test a forms check box, test if Shape.ControlFormat.Value = xlOn or xlOff.
But when I try to set a forms checkbox via the .ControlFormat, (e.g. Shape.ControlFormat.Value = xlOn) Excel crashes (Excel2004 bug I presume)
To set Forms checkboxes I use Shape.OLEFormat.Object.Value = xlOn or xlOff.

 

[Stubby]

Something like this

Sub test()
    Dim resultString As String
    Dim oneShape As Shape
    For Each oneShape In ActiveSheet.Shapes
        With oneShape
        If .Type = msoFormControl Then
            If .FormControlType = xlCheckBox Then
                If .ControlFormat.Value = xlOn Then
                    resultString = resultString & .Name & vbCr
                End If
            End If
        End If
        End With
    Next oneShape
    MsgBox resultString & "are checked."
End Sub

To test a forms check box, test if Shape.ControlFormat.Value = xlOn or xlOff.
But when I try to set a forms checkbox via the .ControlFormat, (e.g. Shape.ControlFormat.Value = xlOn) Excel crashes (Excel2004 bug I presume)
To set Forms checkboxes I use Shape.OLEFormat.Object.Value = xlOn or xlOff.

i'll give it a try. Thanks

 

[Stubby]

i'll give it a try. Thanks

I have now tried the code and it works really well. Many thanks