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!

 

[rhombus4]

so to get rid of any option boxes which are selected would it be

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

End If
Next

 

[rhombus4]

so to get rid of any option boxes which are selected would it be

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

End If
Next

or maybe i need something else ????????

 

[GTO]

Assuming the Option Buttons are from the Forms toolbar, I believe you want:

If TypeName(s.OLEFormat.Object) = "OptionButton" Then

 

[rhombus4]

Assuming the Option Buttons are from the Forms toolbar, I believe you want:

If TypeName(s.OLEFormat.Object) = "OptionButton" Then

Not sure how the two would go together to delete checkboxes and option buttons, which are both from the forms toolbar. Tried below but didnt workDim s As Shape For Each s In ThisWorkbook.Worksheets("Sheet1").Shapes If TypeName(s.OLEFormat.Object) = "CheckBox" Then s.OLEFormat.Object.Value = xlOff If TypeName(s.OLEFormat.Object) = "OptionButton" Then s.OLEFormat.Object.Value = xlOff End If NextRange("A2").Select End Sub

 

[rhombus4]

Not sure how the two would go together to delete checkboxes and option buttons, which are both from the forms toolbar. Tried below but didnt workDim s As Shape For Each s In ThisWorkbook.Worksheets("Sheet1").Shapes If TypeName(s.OLEFormat.Object) = "CheckBox" Then s.OLEFormat.Object.Value = xlOff If TypeName(s.OLEFormat.Object) = "OptionButton" Then s.OLEFormat.Object.Value = xlOff End If NextRange("A2").Select End Sub

Its ok now, Reason it didnt work was because the check boxes were grouped and wouldnt uncheck

ALthough may be able to make it slightly smaller 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
If TypeName(s.OLEFormat.Object) = "OptionButton" Then
s.OLEFormat.Object.Value = xlOff
End If
Next

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingRows:=True
Range("I2").Select


<!-- / message -->

 

[Tahas]

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.

Hi Mike,

I tried using your code in Excel 2016 but it's not working. Please help.

 

[mikerickson]

Tahas, this thread is over 5 years old.
If you post a new thread describing your problem, it would get better responses. "not working" isn't very descriptive. If you explain what do you want it to do that it isn't, the responses will be better.