WHY?
 

I have a macro call Sub UNPROTECT which unprotects a
worksheet.

I want the user to run this with a button on the worksheet.
If I use a button from the Forms toolbar it works fine,
but when I call the macro from a button from the
ControlBox toolbar I comes up with a runtime error?

I'd like to use the controlbox button as they are more
versatile and you can change the colour etc.

the unprotect macro code is simply
Worksheets(1).unprotect password:="HOLIDAY"

Thanks in advance

WHY?
 

Dennis,

Control toolboxes don't have macros assigned, they have events. So your
macro would be called from the Commandbutton1_Click event, which is in the
worksheet code module.

In design mode, double click the button, and it will create a skeleton event
code procedure for you.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"DENNIS" wrote in message
...
I have a macro call Sub UNPROTECT which unprotects a
worksheet.

I want the user to run this with a button on the worksheet.
If I use a button from the Forms toolbar it works fine,
but when I call the macro from a button from the
ControlBox toolbar I comes up with a runtime error?

I'd like to use the controlbox button as they are more
versatile and you can change the colour etc.

the unprotect macro code is simply
Worksheets(1).unprotect password:="HOLIDAY"

Thanks in advance

WHY?
 

Dennis, that should work, do you have the code in the buttons code like
this? also passwords are case sensitive
Private Sub CommandButton1_Click()
Worksheets(1).Unprotect password:="HOLIDAY"
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
"DENNIS" wrote in message
...
I have a macro call Sub UNPROTECT which unprotects a
worksheet.

I want the user to run this with a button on the worksheet.
If I use a button from the Forms toolbar it works fine,
but when I call the macro from a button from the
ControlBox toolbar I comes up with a runtime error?

I'd like to use the controlbox button as they are more
versatile and you can change the colour etc.

the unprotect macro code is simply
Worksheets(1).unprotect password:="HOLIDAY"

Thanks in advance

ANOTHER WHY?
 

Thanks I'm not sure what I was doing wrong but that bit
works now.

There is another problem though

On sheet1 i have a button.
This button produces a userform, which in turn has a
button on it.
When the button on the userform is pressed a message box
appears with Yes and No.
If Yes is clicked then I want the worksheet to be
unprotected, but it comes up with a run time error.

Code as follows

'on userform
Sub commandbutton1_Click()
If msgbox("Unprotect sheet?", vbyesno) = vbyes then
worksheets(1).unprotect password:= "HOLIDAY" 'password is
ok
end if

This is really holding me back so any help much
appreciated!
-----Original Message-----
I have a macro call Sub UNPROTECT which unprotects a
worksheet.

I want the user to run this with a button on the
worksheet.
If I use a button from the Forms toolbar it works fine,
but when I call the macro from a button from the
ControlBox toolbar I comes up with a runtime error?

I'd like to use the controlbox button as they are more
versatile and you can change the colour etc.

the unprotect macro code is simply
Worksheets(1).unprotect password:="HOLIDAY"

Thanks in advance
.

ANOTHER WHY?
 

If you are using Excel 97, change the takefocusonclick property of the
commandbutton to false.

--
Regards,
Tom Ogilvy

DENNIS wrote in message
...
Thanks I'm not sure what I was doing wrong but that bit
works now.

There is another problem though

On sheet1 i have a button.
This button produces a userform, which in turn has a
button on it.
When the button on the userform is pressed a message box
appears with Yes and No.
If Yes is clicked then I want the worksheet to be
unprotected, but it comes up with a run time error.

Code as follows

'on userform
Sub commandbutton1_Click()
If msgbox("Unprotect sheet?", vbyesno) = vbyes then
worksheets(1).unprotect password:= "HOLIDAY" 'password is
ok
end if

This is really holding me back so any help much
appreciated!
-----Original Message-----
I have a macro call Sub UNPROTECT which unprotects a
worksheet.

I want the user to run this with a button on the
worksheet.
If I use a button from the Forms toolbar it works fine,
but when I call the macro from a button from the
ControlBox toolbar I comes up with a runtime error?

I'd like to use the controlbox button as they are more
versatile and you can change the colour etc.

the unprotect macro code is simply
Worksheets(1).unprotect password:="HOLIDAY"

Thanks in advance
.