View Full Version : Protect radio buttons on a sheet with VBA

05-30-2019, 05:39 AM
Hello all,

I have a macro that runs fine, at the completion of the macro, I want it to protect the sheet (which it does just fine) but it does not prohibit radio buttons and check boxes from being changed, is there a way to also lock form controls through the macro?


05-30-2019, 05:46 AM
change button names as req use the same with the chk boxes
Set them to true when you want to unlock them

OptionButton1.Enabled = False
Checkbox1.Enabled = False

05-30-2019, 06:18 AM
Hello Rob, and thank you!

I have custom names assigned to each radio button/check box. When trying CustomName.enabled = false I get an error. I also tried OptionButtonX.Enabled = false (its original name) both resulted in errors.

For the second option the error was object required, the first was object doesnt support this property or method.

Thank you again, any ideas?

05-30-2019, 06:27 AM
Can you post a copy of the code routine or the workbook would be better
Are they ActiveX controls ?

05-30-2019, 06:55 AM
They are form controls - the rest of the code is embarrassing so Ill spare you the pain. This is the assignment and then my attempt at disabling them. Im assuming it has something to do with me assigning it as a shape - my use for the buttons is just an option selection. I have numerous buttons per grouping, which the user then selects one per group (designating a option they want) and the macro then takes their answer through 'xlOn' checks and continues on. After the user is done the workbook is force saved, and I want it to lock everything in the worksheet (cells and options) so that it can become a reference document from testing they completed.

Thank you again for your continued help.

'---------------------------near start of code----------------------------------
' Establish Radio Button Variables
Dim ResearchFeasability As Shape
Set ResearchFeasability = WS1.Shapes("Option Button 65")
Dim Verification As Shape
Set Verification = WS1.Shapes("Option Button 61")

'---------------------------near end of code----------------------------------

' Protect worksheet and buttons
ResearchFeasability.Enabled = False
Verification.Enabled = False

05-30-2019, 08:23 AM
You made hard work of this there are much easier ways
I'm not into shapes for controls i always use ActiveX controls, my preference as you can do a lot more with them
you could try this one option

With WS1.Shapes("Option Button 61").locked=true
End with