Protecting sheet that has radio buttons

[Ralph2]

I have a small spreadsheet that does calculations based on input of
selected cells and the selection of radio buttons. This worked just
fine when I made it in 2006. Now I have added to it and can not
protect it the way it was. All cells are protected except those that
may require input and the 6 radio buttons.

Now I can not unlock my radio buttons.. What have I forgotten to do?

Thanks for your time and any possible help
Ralph

 

[Gord Dibben]

Are the buttons from the Control Toolbox?

You will need to be in design mode to change format attributes on
those.


Gord

 

[Ralph2]

Thanks Gord
Yes the buttons were made from the Visual Basic Editor. If I go to
Design mode select one of the buttons under properties they are listed
as locked false.
On an original.. that I did not modify, the property on that is the
same. Changing the locked property to true does not fix the problem.

Here is what I am doing > select all > format >cells > select locked
and hidden. Now select those all those cells I want available for
input > format >cells > deselect locked and hidden. Then Tools

Protection > Protect sheet. select option to access unlocked cells.

Now it works.. except I get double error messages when I click on my
radio buttons. There seems no way to select those and set them to
unlocked.. that I can find or remember.

Thanks again for your help

 

[GS]

Ralph2 formulated on Saturday :

Thanks Gord
Yes the buttons were made from the Visual Basic Editor. If I go to
Design mode select one of the buttons under properties they are listed
as locked false.
On an original.. that I did not modify, the property on that is the
same. Changing the locked property to true does not fix the problem.

Here is what I am doing > select all > format >cells > select locked
and hidden. Now select those all those cells I want available for
input > format >cells > deselect locked and hidden. Then Tools

Now it works.. except I get double error messages when I click on my
radio buttons. There seems no way to select those and set them to
unlocked.. that I can find or remember.

Thanks again for your help

Why are you selecting Hidden? You can't hide individual cells; only
entire rows/cols.

What other protection settings are set?
What's the double error messages you're getting?

 

[Ralph2]

Thanks guys.
Problem resolved. It seems that my radio buttons were linked to a
locked cell (true or false) that I subsequently used with an "if"
statement. These cells were out of the way but included in my "select
all" Removing the protection from them fixed my problem.

I kept "hidden" in my protection as I thought it hides the formulae..
No?

Thanks again

 

[Gord Dibben]

Yes.


Gord

 

[GS]

You might find iit beneficial to protect the UserInterfaceOnly so your
code behind the radio buttons can update locked cells. To do this,
specify this parameter '=True' in your protect statement...

ActiveSheet.Protect Password:= PWRD, DrawingObjects:=True,
Contents:=True, Scenarios:=True, UserInterfaceOnly:=True

Note that you can only reset protect after unprotecting. Here's a
reusable procedure you can run at startup or when a workbook first
opens...

Sub ResetWksProtection(Optional Wks As Worksheet)
' Simplistic method for setting/resetting sheet protection so code can
make changes without restriction.
' If a sheet is already protected we MUST unprotect it before resetting
protection.
' Some protection parameters do not persist after closing and so must
be reset when the file reopens.
If Wks Is Nothing Then Set Wks = ActiveSheet
Wks.Unprotect PWRD: Wks.Protect PWRD, DrawingObjects:=True,
Contents:=True, Scenarios:=True, UserInterfaceOnly:=True
End Sub

To use it...

Dim wks As Worksheet
For Each wks in ActiveWorkbook.Worksheets
ResetWksProtection wks
Next 'wks

 

[Ralph2]

Thanks Gary..
A bit more involved than what I need but I appreciate you taking the
time to explain it.

 

[GS]

Ralph2 laid this down on his screen :

Thanks Gary..
A bit more involved than what I need but I appreciate you taking the
time to explain it.

You're welcome!

However, if you run code on protected sheets then IT IS what you need
unless you don't mind toggling protection off/on every time you need to
change a locked cell. It's just more efficient to set the sheets up so
you don't need to burden your project with the extra processing.<g>