Check Boxes and Options Buttons

[rhombus4]

I've added to an existing macro so that checkboxes which have been ticked or Option buttons which have been pressed are then unticked.

I can do them individualy heres a sample

ActiveSheet.Shapes("Check Box 2103").Select
With Selection
.Value = xlOff
.LinkedCell = ""
.Display3DShading = True
End With
ActiveSheet.Shapes("Check Box 2104").Select
With Selection
.Value = xlOff
.LinkedCell = ""
.Display3DShading = True
End With

The above works fine, but I have alot of checkboxes and when I do them all the macro is very long.

I tried to record a macro (below) and do two at once which recorded fine but when I ran it I got an error " unable to set the value property of the DrawingObjects Class.

ActiveSheet.Shapes("Check Box 2103").Select
ActiveSheet.Shapes.Range(Array("Check Box 2103", "Check Box 2104")).Select
With Selection
.Value = xlOff
.LinkedCell = ""
.Display3DShading = True
End With
Range("A1").Select
End Sub

Is it possible to group them all like when you want to clear cells you can just list them in one line or range then clear contents. e.g if i had checkboxes 2101 to 2140. ALso is it possible to rename the checkbox number as alot of them are really random numbers!

 

[doofusboy]

This should get you headed in the right direction:

Dim s As Shape
    For Each s In Shapes
        MsgBox s.Name & vbTab & "Shape Type: " & s.Type
    Next s

 

[rhombus4]

This should get you headed in the right direction:

Dim s As Shape
    For Each s In Shapes
        MsgBox s.Name & vbTab & "Shape Type: " & s.Type
    Next s

???? Sorry im confused. DO i enter as above in my MAcro and remove everything else relating to checkboxes and option boxes

 

[GTO]

Try:

Sub exa2()
Dim s As Shape
    
    For Each s In ThisWorkbook.Worksheets("Sheet4").Shapes
        If TypeName(s.OLEFormat.Object) = "CheckBox" Then
            s.OLEFormat.Object.Value = xlOff
        End If
    Next
    
End Sub

 

[rhombus4]

Try:

Sub exa2()
Dim s As Shape
 
    For Each s In ThisWorkbook.Worksheets("Sheet4").Shapes
        If TypeName(s.OLEFormat.Object) = "CheckBox" Then
            s.OLEFormat.Object.Value = xlOff
        End If
    Next
 
End Sub

I already have a macro that unchecks all the checkboxes but is really long. I tried deleting everything in my macro relating to checkboxes then added the bit below but non of the checkboxes were unticked. Not sure what to add in my macro to replace my existing code.Dim s As Shape For Each s In ThisWorkbook.Worksheets("Tab1").Shapes If TypeName(s.OLEFormat.Object) = "CheckBox" Then s.OLEFormat.Object.Value = xlOff End If Next

 

[GTO]

When posting code, please use the [code]...your code...[/code] tags.

I am not sure what the problem is. You appear to be using checkboxes from the Forms toolbar, and as long as you have the sheet's (tab) name correct, I do not see a problem.

Maybe posting the whole procedure, as you currently have it written would help to see what's going on.

 

[mikerickson]

The .ControlFormat property needs to be used.

ActiveSheet.Shapes("Option Button 1").ControlFormat.Value = xlOff

 

[GTO]

Howdy Mike,

I certainly know very little about Shapes. Is there a reason, such as version or ??? that mine worked on my end but not the OPs'? Yours certainly works, I was just curious.

Thank you so much,

Mark

 

[mikerickson]

My code adresses the Shape object directly, the OP code works through Selection. I haven't figured out exactly the details, but shapes via Selection isn't the same as addressing the Shape object directly.

There is no advantage to using Selection. To the contrary.

 

[rhombus4]

Howdy Mike,

I certainly know very little about Shapes. Is there a reason, such as version or ??? that mine worked on my end but not the OPs'? Yours certainly works, I was just curious.

Thank you so much,

Mark

My code adresses the Shape object directly, the OP code works through Selection. I haven't figured out exactly the details, but shapes via Selection isn't the same as addressing the Shape object directly.

There is no advantage to using Selection. To the contrary.

finally managed to get it to work thanks

added your code in the middle of my macro

Dim s As Shape
For Each s In ThisWorkbook.Worksheets("Sheet1").Shapes
If TypeName(s.OLEFormat.Object) = "CheckBox" Then
s.OLEFormat.Object.Value = xlOff

End If
Next

If i want to make sure all the option buttons are deselected as well do I just copy the above and change where it says checkbox to say option button.

Not sure about the control format as didnt understand the difference between using the forms or controlbox option