how to make

n00b · Feb 25th, 2002, 07:57 AM

a button so that when it is [ress it will protect the sheet, and press again to unprotect the sheet?
also is it possible so that after the sheet is protected the button text will say "click unprotect sheet" and when it is pressed again it will say " click to protect sheet"

dk · Feb 25th, 2002, 08:18 AM

Hi,

If you place the commandbutton on your sheet and change it's caption to Click to Protect Sheet. Then double click the button and use this code:-

Code:

Private Sub CommandButton1_Click()
If Me.CommandButton1.Caption = "Click to protect sheet" Then
    Me.Protect "passwordIfyouwant"
    Me.CommandButton1.Caption = "Click to unprotect sheet"
Else
    Me.Unprotect "passwordIfyouwant"
    Me.CommandButton1.Caption = "Click to protect sheet"
End If
End Sub

HTH,
D

[ This Message was edited by: dk on 2002-02-25 07:18 ]

[ This Message was edited by: dk on 2002-02-25 07:19 ]

Feb 25th, 2002, 08:30 AM

When I try this I get runtime error 1004

How can I fix it?

n00b · Feb 25th, 2002, 08:33 AM

didn't work, it says "invaild use of Me keyword" ;/

n00b · Feb 25th, 2002, 09:16 AM

*bump*

dk · Feb 25th, 2002, 09:28 AM

Are you using the command button from View, Toolbars, Control Toolbox? If you place that button on the sheet and make sure you're in Design mode (the blue set square icon on the Control Toolbox is pressed in). Now double click the button. It will take you to the code module of the button's parent - in this case the sheet code module. Put the code there - not in a standard module.

Any more problems, let me know,

Regards,
D

Feb 25th, 2002, 08:18 AM	#2
dk MrExcel MVP Join Date: Feb 2002 Location: Sydney, Australia Posts: 2,908	Hi, If you place the commandbutton on your sheet and change it's caption to Click to Protect Sheet. Then double click the button and use this code:- Code: Private Sub CommandButton1_Click() If Me.CommandButton1.Caption = "Click to protect sheet" Then Me.Protect "passwordIfyouwant" Me.CommandButton1.Caption = "Click to unprotect sheet" Else Me.Unprotect "passwordIfyouwant" Me.CommandButton1.Caption = "Click to protect sheet" End If End Sub HTH, D [ This Message was edited by: dk on 2002-02-25 07:18 ] [ This Message was edited by: dk on 2002-02-25 07:19 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 25th, 2002, 07:57 AM	#1
n00b New Member Join Date: Feb 2002 Posts: 18	a button so that when it is [ress it will protect the sheet, and press again to unprotect the sheet? also is it possible so that after the sheet is protected the button text will say "click unprotect sheet" and when it is pressed again it will say " click to protect sheet"

Feb 25th, 2002, 08:30 AM	#3
Guest Posts: n/a	When I try this I get runtime error 1004 How can I fix it?

Feb 25th, 2002, 08:33 AM	#4
n00b New Member Join Date: Feb 2002 Posts: 18	didn't work, it says "invaild use of Me keyword" ;/

Feb 25th, 2002, 09:16 AM	#5
n00b New Member Join Date: Feb 2002 Posts: 18	bump

Feb 25th, 2002, 09:28 AM	#6
dk MrExcel MVP Join Date: Feb 2002 Location: Sydney, Australia Posts: 2,908	Are you using the command button from View, Toolbars, Control Toolbox? If you place that button on the sheet and make sure you're in Design mode (the blue set square icon on the Control Toolbox is pressed in). Now double click the button. It will take you to the code module of the button's parent - in this case the sheet code module. Put the code there - not in a standard module. Any more problems, let me know, Regards, D