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.... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect Sheet with code, but then code will not Paste error. How d
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.... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect Sheet with code, but then code will not Paste error. How d
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.... "JLGWhiz" wrote in message ... 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.... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect Sheet with code, but then code will not Paste error. How d
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. "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.... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect Sheet with code, but then code will not Paste error.How d
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 JLGWhiz wrote: 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. "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.... |
Reply |
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 |