not allowing users access to spreadsheet without correct names
 

On my spreadsheet I have a user form that loads up where their are two text
boxes on for the First Name and another for the Last Name. With the help of
Tom I have created a macro that takes the input from the text boxes and takes
the user to their corresponding range.

The problem I am having is if an incorrect user name is entered such as "Bob
Dole" due to my login button unprotecting the sheet they are given acess to
every range and can makes changes. Here is the code I have for my login
button. Can someone help me so that when a name is entered that doesn't
correspond to a range that the user has no access to the worksheet and is
exited out of excel.

Private Sub Login_click()
With UserInterface
Worksheets("Employee Tracker").Unprotect Password:="testpass"
Range("A5:N25").Select
Selection.Locked = True
On Error Resume Next
Set rng = Worksheets("Employee Tracker").Range( _
.TextboxFirst.Text & "_" & .TextBoxLast.Text)
On Error GoTo 0
If rng Is Nothing Then
MsgBox "Bad name, quitting"
Unload UserInterface
ThisWorkbook.Close Savechanges:=False
End If
End With
Application.Goto rng, True
Unload UserInterface
Call Macro3
End Sub

Thanks in advance

not allowing users access to spreadsheet without correct names
 

Don't unlock it until you know its a good name. I reorganized you code to do
that.
Private Sub Login_click()
With UserInterface
On Error Resume Next
Set rng = Worksheets("Employee Tracker").Range( _
.TextboxFirst.Text & "_" & .TextBoxLast.Text)
On Error GoTo 0
If rng Is Nothing Then
MsgBox "Bad name, quitting"
Unload UserInterface
ThisWorkbook.Close Savechanges:=False
End If
End With
Worksheets("Employee Tracker").Unprotect Password:="testpass"
Range("A5:N25").Select
Selection.Locked = True
Application.Goto rng, True
Unload UserInterface
Call Macro3
End Sub

"BrianMo" wrote:

On my spreadsheet I have a user form that loads up where their are two text
boxes on for the First Name and another for the Last Name. With the help of
Tom I have created a macro that takes the input from the text boxes and takes
the user to their corresponding range.

The problem I am having is if an incorrect user name is entered such as "Bob
Dole" due to my login button unprotecting the sheet they are given acess to
every range and can makes changes. Here is the code I have for my login
button. Can someone help me so that when a name is entered that doesn't
correspond to a range that the user has no access to the worksheet and is
exited out of excel.

Private Sub Login_click()
With UserInterface
Worksheets("Employee Tracker").Unprotect Password:="testpass"
Range("A5:N25").Select
Selection.Locked = True
On Error Resume Next
Set rng = Worksheets("Employee Tracker").Range( _
.TextboxFirst.Text & "_" & .TextBoxLast.Text)
On Error GoTo 0
If rng Is Nothing Then
MsgBox "Bad name, quitting"
Unload UserInterface
ThisWorkbook.Close Savechanges:=False
End If
End With
Application.Goto rng, True
Unload UserInterface
Call Macro3
End Sub

Thanks in advance

not allowing users access to spreadsheet without correct names
 

Thanks Tom that worked great for the unlocking part. However I do have one
small problem for some reason it is not recognizing the If rng Is Nothing
Then statement as whenever I input the wrong name I get a run time error
message that it doesn't recognize it. I have the code in exactly as you
formatted it.

"Tom Ogilvy" wrote:

Don't unlock it until you know its a good name. I reorganized you code to do
that.
Private Sub Login_click()
With UserInterface
On Error Resume Next
Set rng = Worksheets("Employee Tracker").Range( _
.TextboxFirst.Text & "_" & .TextBoxLast.Text)
On Error GoTo 0
If rng Is Nothing Then
MsgBox "Bad name, quitting"
Unload UserInterface
ThisWorkbook.Close Savechanges:=False
End If
End With
Worksheets("Employee Tracker").Unprotect Password:="testpass"
Range("A5:N25").Select
Selection.Locked = True
Application.Goto rng, True
Unload UserInterface
Call Macro3
End Sub

"BrianMo" wrote:

On my spreadsheet I have a user form that loads up where their are two text
boxes on for the First Name and another for the Last Name. With the help of
Tom I have created a macro that takes the input from the text boxes and takes
the user to their corresponding range.

The problem I am having is if an incorrect user name is entered such as "Bob
Dole" due to my login button unprotecting the sheet they are given acess to
every range and can makes changes. Here is the code I have for my login
button. Can someone help me so that when a name is entered that doesn't
correspond to a range that the user has no access to the worksheet and is
exited out of excel.

Private Sub Login_click()
With UserInterface
Worksheets("Employee Tracker").Unprotect Password:="testpass"
Range("A5:N25").Select
Selection.Locked = True
On Error Resume Next
Set rng = Worksheets("Employee Tracker").Range( _
.TextboxFirst.Text & "_" & .TextBoxLast.Text)
On Error GoTo 0
If rng Is Nothing Then
MsgBox "Bad name, quitting"
Unload UserInterface
ThisWorkbook.Close Savechanges:=False
End If
End With
Application.Goto rng, True
Unload UserInterface
Call Macro3
End Sub

Thanks in advance