Corey, I believe that if change the variable for the input box where you name
the sheet with the employee name the something like "newSht" instead of
"res" you can then revise your ProtectActive macro as below.
Sub ProtectActive()
'
' Macro2 Macro
' Macro recorded 24-11-2006 by Corey
'
ThisWorkbook.Sheets(newSht).Protect Password:="1234" '
Sheets(newSht).Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

You will also have to change the line: ActiveSheet = res to
ActiveSheet.Name = newSht and it should work without the error.




"Corey" wrote:

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....