Trying to set Option button values in macro on protected sheets
 

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

Trying to set Option button values in macro on protected sheets
 

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


---
Regards,
Norman


"Damon" wrote in message
...
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

Trying to set Option button values in macro on protected sheets
 

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

Damon Wrote:


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 preffe
both
to be protected.

Is it possible to set the value of the option button withou
'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 th
password
be incorporated into the macro so that the user is not promted for i
when
the macro unlocks a sheet?

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

Thank you in advance

Damo

--
jose lui
-----------------------------------------------------------------------
jose luis's Profile: http://www.excelforum.com/member.php...fo&userid=1331
View this thread: http://www.excelforum.com/showthread.php?threadid=38584

Trying to set Option button values in macro on protected sheet
 

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
"jose luis" wrote:


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 is
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

Damon Wrote:


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


--
jose luis
------------------------------------------------------------------------
jose luis's Profile: http://www.excelforum.com/member.php...o&userid=13312
View this thread: http://www.excelforum.com/showthread...hreadid=385846

Trying to set Option button values in macro on protected sheet
 

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.

--
Regards,
Tom Ogilvy


"Damon" wrote in message
...
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
"jose luis" wrote:


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 is
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

Damon Wrote:


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


--
jose luis
------------------------------------------------------------------------
jose luis's Profile:
http://www.excelforum.com/member.php...o&userid=13312
View this thread:
http://www.excelforum.com/showthread...hreadid=385846