Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
This is so silly... | Excel Worksheet Functions | |||
Help with silly error | Excel Programming | |||
Silly little annoyance | Setting up and Configuration of Excel | |||
Silly question | Excel Programming | |||
Silly I know | Excel Programming |