|
Aborting Execution Before Error Message
Hello, My goal is to have code to, upon activating a sheet, to unprotect the
sheet if the user imputs the correct password and then complete some other tasks (the "other" portion I have figured out. The code is as follows: Private Sub Worksheet_Activate() If ActiveSheet.Protect = False Then ActiveSheet.Unprotect Rows("2:5000").Select Selection.EntireRow.Hidden = False Range("A1:L5004").Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Rows("2:2").Select ActiveWindow.FreezePanes = True Range("A1").Select End If The issue is, if the user does not value the correct password, they receive an error. My question is: What is the code to be added so if the user inputs the correct password the code continues and if the incorrect password is valued the execution aborts and the error meesage is not presented (I realize the user just needs to click "end" when then error is received, but I'd like to have a cleaner process). Any and All Help Is Appreciated - Thank You |
Aborting Execution Before Error Message
Hi MWS,
Try: '============= Private Sub Worksheet_Activate() On Error GoTo XIT: ActiveSheet.Unprotect On Error GoTo 0 Rows("2:5000").Select Selection.EntireRow.Hidden = False Range("A1:L5004").Select Selection.Sort Key1:=Range("A2"), _ Order1:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Rows("2:2").Select ActiveWindow.FreezePanes = True Range("A1").Select Exit Sub XIT: MsgBox "Your password was not recognised""" End Sub '<<============= --- Regards, Norman "MWS" wrote in message ... Hello, My goal is to have code to, upon activating a sheet, to unprotect the sheet if the user imputs the correct password and then complete some other tasks (the "other" portion I have figured out. The code is as follows: Private Sub Worksheet_Activate() If ActiveSheet.Protect = False Then ActiveSheet.Unprotect Rows("2:5000").Select Selection.EntireRow.Hidden = False Range("A1:L5004").Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Rows("2:2").Select ActiveWindow.FreezePanes = True Range("A1").Select End If The issue is, if the user does not value the correct password, they receive an error. My question is: What is the code to be added so if the user inputs the correct password the code continues and if the incorrect password is valued the execution aborts and the error meesage is not presented (I realize the user just needs to click "end" when then error is received, but I'd like to have a cleaner process). Any and All Help Is Appreciated - Thank You |
Aborting Execution Before Error Message
Norman, Thank You, this addresses the inputting of an incorrect password.
A point I failed to mention and perhaps you can help with is, if the user chooses "cancel" instead of inputting a password at the prompt an error message is displayed. Can this too be avoided with code and if so, what would it be? Thanks Again - I Appreciate Your Help "Norman Jones" wrote: Hi MWS, Try: '============= Private Sub Worksheet_Activate() On Error GoTo XIT: ActiveSheet.Unprotect On Error GoTo 0 Rows("2:5000").Select Selection.EntireRow.Hidden = False Range("A1:L5004").Select Selection.Sort Key1:=Range("A2"), _ Order1:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Rows("2:2").Select ActiveWindow.FreezePanes = True Range("A1").Select Exit Sub XIT: MsgBox "Your password was not recognised""" End Sub '<<============= --- Regards, Norman "MWS" wrote in message ... Hello, My goal is to have code to, upon activating a sheet, to unprotect the sheet if the user imputs the correct password and then complete some other tasks (the "other" portion I have figured out. The code is as follows: Private Sub Worksheet_Activate() If ActiveSheet.Protect = False Then ActiveSheet.Unprotect Rows("2:5000").Select Selection.EntireRow.Hidden = False Range("A1:L5004").Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Rows("2:2").Select ActiveWindow.FreezePanes = True Range("A1").Select End If The issue is, if the user does not value the correct password, they receive an error. My question is: What is the code to be added so if the user inputs the correct password the code continues and if the incorrect password is valued the execution aborts and the error meesage is not presented (I realize the user just needs to click "end" when then error is received, but I'd like to have a cleaner process). Any and All Help Is Appreciated - Thank You |
Aborting Execution Before Error Message
Hi MWS,
Try handling the password directly: '============= Private Sub Worksheet_Activate() Dim res As Variant res = Application.InputBox _ (Prompt:="Enter password", Type:=3) On Error GoTo XIT: ActiveSheet.Unprotect password:=res On Error GoTo 0 Rows("2:5000").Select Selection.EntireRow.Hidden = False Range("A1:L5004").Select Selection.Sort Key1:=Range("A2"), _ Order1:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Rows("2:2").Select ActiveWindow.FreezePanes = True Range("A1").Select Exit Sub XIT: MsgBox "Your password was not recognised""" End Sub '<<============= -- --- Regards, Norman "MWS" wrote in message ... Norman, Thank You, this addresses the inputting of an incorrect password. A point I failed to mention and perhaps you can help with is, if the user chooses "cancel" instead of inputting a password at the prompt an error message is displayed. Can this too be avoided with code and if so, what would it be? Thanks Again - I Appreciate Your Help "Norman Jones" wrote: Hi MWS, Try: '============= Private Sub Worksheet_Activate() On Error GoTo XIT: ActiveSheet.Unprotect On Error GoTo 0 Rows("2:5000").Select Selection.EntireRow.Hidden = False Range("A1:L5004").Select Selection.Sort Key1:=Range("A2"), _ Order1:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Rows("2:2").Select ActiveWindow.FreezePanes = True Range("A1").Select Exit Sub XIT: MsgBox "Your password was not recognised""" End Sub '<<============= --- Regards, Norman "MWS" wrote in message ... Hello, My goal is to have code to, upon activating a sheet, to unprotect the sheet if the user imputs the correct password and then complete some other tasks (the "other" portion I have figured out. The code is as follows: Private Sub Worksheet_Activate() If ActiveSheet.Protect = False Then ActiveSheet.Unprotect Rows("2:5000").Select Selection.EntireRow.Hidden = False Range("A1:L5004").Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Rows("2:2").Select ActiveWindow.FreezePanes = True Range("A1").Select End If The issue is, if the user does not value the correct password, they receive an error. My question is: What is the code to be added so if the user inputs the correct password the code continues and if the incorrect password is valued the execution aborts and the error meesage is not presented (I realize the user just needs to click "end" when then error is received, but I'd like to have a cleaner process). Any and All Help Is Appreciated - Thank You |
Aborting Execution Before Error Message
Thank You Norman - I Appreciate Your Help!!!!
"Norman Jones" wrote: Hi MWS, Try handling the password directly: '============= Private Sub Worksheet_Activate() Dim res As Variant res = Application.InputBox _ (Prompt:="Enter password", Type:=3) On Error GoTo XIT: ActiveSheet.Unprotect password:=res On Error GoTo 0 Rows("2:5000").Select Selection.EntireRow.Hidden = False Range("A1:L5004").Select Selection.Sort Key1:=Range("A2"), _ Order1:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Rows("2:2").Select ActiveWindow.FreezePanes = True Range("A1").Select Exit Sub XIT: MsgBox "Your password was not recognised""" End Sub '<<============= -- --- Regards, Norman "MWS" wrote in message ... Norman, Thank You, this addresses the inputting of an incorrect password. A point I failed to mention and perhaps you can help with is, if the user chooses "cancel" instead of inputting a password at the prompt an error message is displayed. Can this too be avoided with code and if so, what would it be? Thanks Again - I Appreciate Your Help "Norman Jones" wrote: Hi MWS, Try: '============= Private Sub Worksheet_Activate() On Error GoTo XIT: ActiveSheet.Unprotect On Error GoTo 0 Rows("2:5000").Select Selection.EntireRow.Hidden = False Range("A1:L5004").Select Selection.Sort Key1:=Range("A2"), _ Order1:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Rows("2:2").Select ActiveWindow.FreezePanes = True Range("A1").Select Exit Sub XIT: MsgBox "Your password was not recognised""" End Sub '<<============= --- Regards, Norman "MWS" wrote in message ... Hello, My goal is to have code to, upon activating a sheet, to unprotect the sheet if the user imputs the correct password and then complete some other tasks (the "other" portion I have figured out. The code is as follows: Private Sub Worksheet_Activate() If ActiveSheet.Protect = False Then ActiveSheet.Unprotect Rows("2:5000").Select Selection.EntireRow.Hidden = False Range("A1:L5004").Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Rows("2:2").Select ActiveWindow.FreezePanes = True Range("A1").Select End If The issue is, if the user does not value the correct password, they receive an error. My question is: What is the code to be added so if the user inputs the correct password the code continues and if the incorrect password is valued the execution aborts and the error meesage is not presented (I realize the user just needs to click "end" when then error is received, but I'd like to have a cleaner process). Any and All Help Is Appreciated - Thank You |
| All times are GMT +1. The time now is 08:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com