Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations....
Code creates a new copy of an existing timesheet(sheet), prompts the user
for pay rates (x2), then names the sheet the employee name, then places a copy of the name on the Enter - Exit sheet for viewing all employee names. Code i am using: Protect code that errors code is: Sub ProtectActive() ' ' Macro2 Macro ' Macro recorded 24-11-2006 by Corey ' ThisWorkbook.ActiveSheet.Protect Password:="1234" ' <==== Active Sheet due to sheet name or number not known until sheet is created. ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub Actual code that runs then calls the macro to protect below. (Arrow indicates where protect macro is called/error occurs): Sub InputNewName() Call Unprotect Call UnprotectJC ' ADD New Employee TimeSheet Here res = InputBox("Please Enter the Name for the New Employee", "....") If res = "" Then Exit Sub Else If res = Range("B20").Text Then GoTo cc End If If res = Range("B18").Text Then GoTo cc End If If res = Range("B16").Text Then GoTo cc End If If res = Range("B14").Text Then GoTo cc End If If res = Range("B12").Text Then GoTo cc End If If res = Range("F20").Text Then GoTo cc End If If res = Range("F18").Text Then GoTo cc End If If res = Range("F16").Text Then GoTo cc End If If res = Range("F16").Text Then GoTo cc End If If res = Range("F14").Text Then GoTo cc End If If res = Range("F12").Text Then GoTo cc End If If res = Range("I20").Text Then GoTo cc End If If res = Range("I18").Text Then GoTo cc End If If res = Range("I16").Text Then GoTo cc End If If res = Range("I14").Text Then GoTo cc End If If res = Range("I12").Text Then GoTo cc End If Workbooks("TimeSheets").Activate Sheets("JC").Select Worksheets("JC").Copy After:=Worksheets(Worksheets.Count) ActiveSheet.Name = res ' Name the TimeSheet the Value in the Input Box Range("K2").Value = res Application.CutCopyMode = False Range("X3").ClearContents Range("X6").ClearContents Range("A1").Select res = InputBox("What is the NORMAL Hourly Rate of Pay to be Set At(Inclusive of Casual Loading) ?", " ....") If res = "" Then MsgBox "There MUST be a Value Placed in Cell(X3) Before the 1st Payweek.", , "...." GoTo here Else Range("X3").Value = res End If res = InputBox("What is the MINE Hourly rate of Pay to be set at ? ", "....") If res = "" Then MsgBox "There MUST be a Value Placed in Cell(X6) Before the 1st Payweek.", , "...." Else Range("X6").Value = res End If Application.DisplayAlerts = False Call ClearTimeSheetValues End If he Range("K2").Copy 'Call ProtectActive <================= Want to Protect the sheet before leaving to (Enter - Exit) Sheet, But ERROR occurs due to next pasting steps cannot be done while sheet is protected. Sheets("Enter - Exit").Select With Sheets("Enter - Exit") Select Case True Case .Range("I14").Value = "" .Range("I14").PasteSpecial xlPasteValues Application.CutCopyMode = False Sheets("Enter - Exit").Select Case .Range("I16").Value = "" .Range("I16").PasteSpecial xlPasteValues Application.CutCopyMode = False Sheets("Enter - Exit").Select Case .Range("I18").Value = "" .Range("I18").PasteSpecial xlPasteValues Application.CutCopyMode = False Sheets("Enter - Exit").Select Case .Range("B20").Value = "" .Range("B20").PasteSpecial xlPasteValues Application.CutCopyMode = False Sheets("Enter - Exit").Select Case .Range("F20").Value = "" .Range("F20").PasteSpecial xlPasteValues Application.CutCopyMode = False Sheets("Enter - Exit").Select Case .Range("I20").Value = "" .Range("I20").PasteSpecial xlPasteValues Application.CutCopyMode = False Sheets("Enter - Exit").Select GoTo bb 'Do nothing MsgBox "Please Contact Corey, to Add More Places to Link the New TimeSheet to.", , "...." Application.DisplayAlerts = True Call Protect Exit Sub End Select End With If res = "" Then Application.DisplayAlerts = False With ActiveSheet ..Select ..Delete GoTo bb End With Sheets("Enter - Exit").Select Application.CutCopyMode = False GoTo bb Else 'ActiveSheet.Name = res ' Name the TimeSheet the Value in the Input Box 'Range("K2").Value = res End If Application.DisplayAlerts = True GoTo bb bb: Sheets("Enter - Exit").Select Call Protect Exit Sub cc: MsgBox "ALERT...." & vbCrLf & vbTab & "The Employee Name Already Exits." & vbCrLf & vbCrLf & vbTab & "Please Check the Names on the Main Page.", , "...." Sheets("Enter - Exit").Select Call Protect Call ProtectJC End Sub Question is then, how can i protect the sheet just created, as i cannot use a name for the sheet as it is not named until the sheet is created? Reason i had macro protectactive refer to the activesheet. Or Is there a way to allow the pasting of the sheet name into the Enter - Exit sheet after the sheet was protected still ? Any assistance appreciated. Corey.... |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VB code to tell if my sheet is read-only | Excel Discussion (Misc queries) | |||
Code to protect sheet | Excel Worksheet Functions | |||
Protect Sheet - VB code to specify password | Excel Programming | |||
excel - macro code to open a protected sheet, enter passowrd, and then protect sheet | Excel Programming |