Trying to set Option button values in macro on protected sheets

[Guest]

ActiveSheet.Shapes("Option Button 4").Select
With Selection
.Value = xlOff
End With

Hi the above will set Option Button 4 to false as long as the sheet is
unprotected or the buttons themselves are unlocked but I would preffer both
to be protected.

Is it possible to set the value of the option button without 'selecting' it
first?

I have tried:-

ActiveSheet.Shapes("Option Button 4").Value = xlOff

Shapes("Option Button 4").Value = xlOff

OptionButton("Option Button 4").Value = xlOff

and many more with no success...

I know that Sheets can be locked and unlocked in macros but can the password
be incorporated into the macro so that the user is not promted for it when
the macro unlocks a sheet?

I am using 2003 but the file needs to work in 97 too!

Thank you in advance

Damon

 

[Norman Jones]

Hi Damon,

Try:

Sub Tester()
Dim op As OptionButton
Const PWORD As String = "opensaysme" '<<===== CHANGE

Sheets("Sheet1").Unprotect password:=PWORD

Set op = ActiveSheet.OptionButtons("Option Button 4")

op.Value = xlOff

Sheets("Sheet1").Protect password:=PWORD

End Sub

 

[jose luis]

Hi Damon


Try

ActiveSheet.Shapes("Option Button 4").OLEFormat.Object.value= xloff


The problem is that you can't select and object if the protection i
on.
But this instruction does what is needed without selecting.

If you want to use the Protect and Unprotect method could use


ActiveSheet.Unprotect Password:="xyz"

ActiveSheet.Shapes("Option Button 4").Select
With Selection
.Value = xlOff
End With

ActiveSheet.protect Password:="xyz"

The password is hard coded so the user never knows what's going on.

Hope this helps you

Regards

Jose Luis

 

[Guest]

Thank you both for your replies!

ActiveSheet.Shapes("Option Button 4").OLEFormat.Object.value= xloff

Jose, That is exactly what I was looking for.. I haven't had to use
..OLEFormat or .Obejct yet, probably why I was having trouble trying to guess
the syntax!

Both ways of protecting/unprotecting the sheet with passwords seem work a
treat

And Norman thank you for the example of how to use 'set' I came across that
today whist looking for a solution to this But I had not worked out how to
use it correctly to solve this issue.

Thank you both again

 

[Tom Ogilvy]

Just for information:

Wherever this works:

ActiveSheet.Shapes("Option Button 4").OLEFormat.Object.value= xloff


ActiveSheet.OptionButtons("Option Button 4").Value = xlOff

would work as well.