Need some help with a silly error...
 

I've created a button on a worksheet. To me the process seems rather
simple, but I'm messing it up somewhere. Supposedly what should happen
is:

Sheet 1 is protected except for 3 ranges, sheets 2 and 3 are protected
completely. When you click on the button the ranges in sheet 1 become
protected and sheets 2 and 3 become unprotected except for a range of 9
cells(A1:C3).

When ever I try and use this however it unprotects all the sheets(which
is correct), makes the changes to sheet 1(also correct) and then stops.
It seems the problem is at line 16, I can't figure out why however.
Some help would really be appreciated.

Private Sub CommandButton1_Click()
'Unprotect
Dim SH As Worksheet
Const PWORD As String = "1"
For Each SH In ActiveWorkbook.Worksheets
SH.Unprotect Password:=PWORD
Next SH
'Lock Cells
Sheets("Biography").Select
Range("F13:K13,F16:K16,F19:K19").Select
Range("F19").Activate
Selection.Locked = True
Selection.FormulaHidden = False
For n = 2 To Sheets.Count
Sheets(n).Select
Cells.Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("A1:C3").Select
Selection.Locked = True
Selection.FormulaHidden = False
Next n
'Protection
Const PWORDMAX As String = "HAHAHA"
For Each SH In ActiveWorkbook.Worksheets
SH.Protect Password:=PWORDMAX
Next SH
End Sub

Also, I would like to enquire if there is a way to protect a macro so
that only a certain person can view and alter it and if it would be
possible to delete the button I created after it has been clicked on.

Thanks in advance for any help.


--
Niddeh
------------------------------------------------------------------------
Niddeh's Profile: http://www.excelforum.com/member.php...o&userid=36062
View this thread: http://www.excelforum.com/showthread...hreadid=558482

Need some help with a silly error...
 

Maybe this

Private Sub CommandButton1_Click()
'Unprotect
Dim sh As Worksheet
Const PWORD As String = "1"
Const PWORDMAX As String = "HAHAHA"
For Each sh In ActiveWorkbook.Worksheets
sh.Unprotect Password:=PWORD
'Lock Cells
If sh.Name = "Biography" Then
With sh.Range("F13:K13,F16:K16,F19:K19")
.Locked = True
.FormulaHidden = False
End With
Else
With sh
.Cells.Locked = False
.Cells.FormulaHidden = False
.Range("A1:C3").Locked = True
.Cells.FormulaHidden = False
'Protection
.Protect Password:=PWORDMAX
End With
End If
Next sh
End Sub


Protect the VBA. In the VBIDE, goto ToolsPropertisProtection, check the
box and add a password.

Delete the button

activesheet.oleobjects("CommandButton1").delete


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Niddeh" wrote in
message ...

I've created a button on a worksheet. To me the process seems rather
simple, but I'm messing it up somewhere. Supposedly what should happen
is:

Sheet 1 is protected except for 3 ranges, sheets 2 and 3 are protected
completely. When you click on the button the ranges in sheet 1 become
protected and sheets 2 and 3 become unprotected except for a range of 9
cells(A1:C3).

When ever I try and use this however it unprotects all the sheets(which
is correct), makes the changes to sheet 1(also correct) and then stops.
It seems the problem is at line 16, I can't figure out why however.
Some help would really be appreciated.

Private Sub CommandButton1_Click()
'Unprotect
Dim SH As Worksheet
Const PWORD As String = "1"
For Each SH In ActiveWorkbook.Worksheets
SH.Unprotect Password:=PWORD
Next SH
'Lock Cells
Sheets("Biography").Select
Range("F13:K13,F16:K16,F19:K19").Select
Range("F19").Activate
Selection.Locked = True
Selection.FormulaHidden = False
For n = 2 To Sheets.Count
Sheets(n).Select
Cells.Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("A1:C3").Select
Selection.Locked = True
Selection.FormulaHidden = False
Next n
'Protection
Const PWORDMAX As String = "HAHAHA"
For Each SH In ActiveWorkbook.Worksheets
SH.Protect Password:=PWORDMAX
Next SH
End Sub

Also, I would like to enquire if there is a way to protect a macro so
that only a certain person can view and alter it and if it would be
possible to delete the button I created after it has been clicked on.

Thanks in advance for any help.


--
Niddeh
------------------------------------------------------------------------
Niddeh's Profile:
http://www.excelforum.com/member.php...o&userid=36062
View this thread: http://www.excelforum.com/showthread...hreadid=558482