Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read f

[Corey]

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

 

[Guest]

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]

thanks for the reply.
I have change the ProtectActive code, but i do not understand where to
change the RES to newSht in the longer code.
I tried changing all res' to newSht but got an error.
Can you highlight in the code for me what line/s ?

Thanks

Corey....

 

[Guest]

Corey, I misread your code and your problem on the first go around. After
looking at it again, I think the problem might be that you are not
unprotecting your "Enter-Exit" worksheet before attempting to make changes.
Since you did not post your Protect and Unprotect macros I can't really tell.
I do see at the beginning of the sub where you call Unprotect and
UnprotectJC, but I can't tell if either of those Unprotect "Enter-Exit".

In any event, calling the protect macro between the copy command from one
sheet and the paste command to another is not a good idea. You only need to
change the res to another variable name where it refers to the inputbox for
entering the employee name, because that is what names the sheet. You can
then use that variable (newSht if you like) to later protect the sheet as I
explained before.

Worksheets(newSht).Protect Password...etc.

And you can do this after you have finished your copying and pasting.

I did not mean for you to change all the res entries, just the ones that
apply to the one inputbox so that it will be a unique variable representing
the sheet name.

I hope this is a little more clear than my first posting.

 

[John Smith]

I had a similar problem. My vba code wouldn't run once the sheet
was protected. The following one line code solved the problem.

activesheet.protect userinterfaceonly:=true