Use userform to get input and compare to a hidden sheet
 

I am using a userform to get employees id number and passwords. I have the
id numbers and passwords on a hidden sheet. I need to code in the submit
button on the form a way to search the hidden sheet for there id number and
password match and then if it does match take them to a specific sheet.
Should I be going about this in a different manner or is this possible?

Thanks

First, worksheet protection is pretty easy to break. In just moments, everyone
can see your list of id's/passwords.

And the protection for the VBA project is easy to break so that users can see
your code, too.

If that data is really private/confidential, don't share it in excel.

But this may get you closer if you still want to use it.

I put my id's in column A, the passwords in column B and the worksheets to go to
in column C of the worksheet named Hidden:

Option Explicit
Private Sub CommandButton1_Click()

Dim HidWks As Worksheet
Dim res As Variant

Set HidWks = Worksheets("hidden")

With HidWks
res = Application.Match(Me.TextBox1.Value, .Range("a:a"), 0)
If IsError(res) Then
MsgBox "Invalid User Id"
Exit Sub
Else
If CStr(.Range("a:a")(res, 2).Value) < Me.TextBox2.Value Then
MsgBox "Invalid password for ID"
Exit Sub
End If
End If

With .Parent.Worksheets(.Range("a:a")(res, 3).Value)
.Visible = xlSheetVisible
.Select
.Range("a1").Select
End With

Unload Me

End With

End Sub



cwwolfdog wrote:

I am using a userform to get employees id number and passwords. I have the
id numbers and passwords on a hidden sheet. I need to code in the submit
button on the form a way to search the hidden sheet for there id number and
password match and then if it does match take them to a specific sheet.
Should I be going about this in a different manner or is this possible?

Thanks

--

Dave Peterson

Thanks for your help,

i am having a problem I believe with the .range ("a:a"), 0) Everytime I run
the form it comes back with invalid user id. I am probably missing something
very basic.

Thanks Again.

"Dave Peterson" wrote:

First, worksheet protection is pretty easy to break. In just moments, everyone
can see your list of id's/passwords.

And the protection for the VBA project is easy to break so that users can see
your code, too.

If that data is really private/confidential, don't share it in excel.

But this may get you closer if you still want to use it.

I put my id's in column A, the passwords in column B and the worksheets to go to
in column C of the worksheet named Hidden:

Option Explicit
Private Sub CommandButton1_Click()

Dim HidWks As Worksheet
Dim res As Variant

Set HidWks = Worksheets("hidden")

With HidWks
res = Application.Match(Me.TextBox1.Value, .Range("a:a"), 0)
If IsError(res) Then
MsgBox "Invalid User Id"
Exit Sub
Else
If CStr(.Range("a:a")(res, 2).Value) < Me.TextBox2.Value Then
MsgBox "Invalid password for ID"
Exit Sub
End If
End If

With .Parent.Worksheets(.Range("a:a")(res, 3).Value)
.Visible = xlSheetVisible
.Select
.Range("a1").Select
End With

Unload Me

End With

End Sub



cwwolfdog wrote:

I am using a userform to get employees id number and passwords. I have the
id numbers and passwords on a hidden sheet. I need to code in the submit
button on the form a way to search the hidden sheet for there id number and
password match and then if it does match take them to a specific sheet.
Should I be going about this in a different manner or is this possible?

Thanks

--

Dave Peterson

Do you have extra spaces in column A (or in the textbox)?

If you take the value in the textbox and paste it into D1 (say) of the Hidden
worksheet, what does:
=match(d1,a:a,0)
show?





cwwolfdog wrote:

Thanks for your help,

i am having a problem I believe with the .range ("a:a"), 0) Everytime I run
the form it comes back with invalid user id. I am probably missing something
very basic.

Thanks Again.

"Dave Peterson" wrote:

First, worksheet protection is pretty easy to break. In just moments, everyone
can see your list of id's/passwords.

And the protection for the VBA project is easy to break so that users can see
your code, too.

If that data is really private/confidential, don't share it in excel.

But this may get you closer if you still want to use it.

I put my id's in column A, the passwords in column B and the worksheets to go to
in column C of the worksheet named Hidden:

Option Explicit
Private Sub CommandButton1_Click()

Dim HidWks As Worksheet
Dim res As Variant

Set HidWks = Worksheets("hidden")

With HidWks
res = Application.Match(Me.TextBox1.Value, .Range("a:a"), 0)
If IsError(res) Then
MsgBox "Invalid User Id"
Exit Sub
Else
If CStr(.Range("a:a")(res, 2).Value) < Me.TextBox2.Value Then
MsgBox "Invalid password for ID"
Exit Sub
End If
End If

With .Parent.Worksheets(.Range("a:a")(res, 3).Value)
.Visible = xlSheetVisible
.Select
.Range("a1").Select
End With

Unload Me

End With

End Sub



cwwolfdog wrote:

I am using a userform to get employees id number and passwords. I have the
id numbers and passwords on a hidden sheet. I need to code in the submit
button on the form a way to search the hidden sheet for there id number and
password match and then if it does match take them to a specific sheet.
Should I be going about this in a different manner or is this possible?

Thanks

--

Dave Peterson


--

Dave Peterson

Here is my code. I tried your example and still got the same error.

Private Sub cmdSubmit_Click()

Dim HidWks As Worksheet
Dim res As Variant

Set HidWks = Worksheets("Sheet3")

With HidWks
res = Application.Match(Me.txtUswNumber.Value, .Range("a:a"), 0)
If IsError(res) Then
MsgBox "Invalid User Id"
Exit Sub
Else
If CStr(.Range("a:a")(res, 2).Value) < Me.txtPassword.Value Then
MsgBox "Invalid password for ID"
Exit Sub
End If
End If

With .Parent.Worksheets(.Range("a:a")(res, 3).Value)
.Visible = xlSheetVisible
.Select
.Range("a1").Select
End With

Unload Me

End With

Unload Me

End Sub

Worksheet 3 has:
616 caleb616 Sheet4
520 Alex520 Sheet5
628 Brandi628 Sheet6


Thanks Again.

"Dave Peterson" wrote:

Do you have extra spaces in column A (or in the textbox)?

If you take the value in the textbox and paste it into D1 (say) of the Hidden
worksheet, what does:
=match(d1,a:a,0)
show?





cwwolfdog wrote:

Thanks for your help,

i am having a problem I believe with the .range ("a:a"), 0) Everytime I run
the form it comes back with invalid user id. I am probably missing something
very basic.

Thanks Again.

"Dave Peterson" wrote:

First, worksheet protection is pretty easy to break. In just moments, everyone
can see your list of id's/passwords.

And the protection for the VBA project is easy to break so that users can see
your code, too.

If that data is really private/confidential, don't share it in excel.

But this may get you closer if you still want to use it.

I put my id's in column A, the passwords in column B and the worksheets to go to
in column C of the worksheet named Hidden:

Option Explicit
Private Sub CommandButton1_Click()

Dim HidWks As Worksheet
Dim res As Variant

Set HidWks = Worksheets("hidden")

With HidWks
res = Application.Match(Me.TextBox1.Value, .Range("a:a"), 0)
If IsError(res) Then
MsgBox "Invalid User Id"
Exit Sub
Else
If CStr(.Range("a:a")(res, 2).Value) < Me.TextBox2.Value Then
MsgBox "Invalid password for ID"
Exit Sub
End If
End If

With .Parent.Worksheets(.Range("a:a")(res, 3).Value)
.Visible = xlSheetVisible
.Select
.Range("a1").Select
End With

Unload Me

End With

End Sub



cwwolfdog wrote:

I am using a userform to get employees id number and passwords. I have the
id numbers and passwords on a hidden sheet. I need to code in the submit
button on the form a way to search the hidden sheet for there id number and
password match and then if it does match take them to a specific sheet.
Should I be going about this in a different manner or is this possible?

Thanks

--

Dave Peterson


--

Dave Peterson

If your userids are numbers on sheet3, then you have to convert the Text values
that are entered into that txtUswNumber text box.

If those ID's are just integers (no decimals), then you could use:

res = Application.Match(clng(Me.txtUswNumber.Value), .Range("a:a"), 0)

Clng() will do that conversion.

cwwolfdog wrote:

Here is my code. I tried your example and still got the same error.

Private Sub cmdSubmit_Click()

Dim HidWks As Worksheet
Dim res As Variant

Set HidWks = Worksheets("Sheet3")

With HidWks
res = Application.Match(Me.txtUswNumber.Value, .Range("a:a"), 0)
If IsError(res) Then
MsgBox "Invalid User Id"
Exit Sub
Else
If CStr(.Range("a:a")(res, 2).Value) < Me.txtPassword.Value Then
MsgBox "Invalid password for ID"
Exit Sub
End If
End If

With .Parent.Worksheets(.Range("a:a")(res, 3).Value)
.Visible = xlSheetVisible
.Select
.Range("a1").Select
End With

Unload Me

End With

Unload Me

End Sub

Worksheet 3 has:
616 caleb616 Sheet4
520 Alex520 Sheet5
628 Brandi628 Sheet6

Thanks Again.

"Dave Peterson" wrote:

Do you have extra spaces in column A (or in the textbox)?

If you take the value in the textbox and paste it into D1 (say) of the Hidden
worksheet, what does:
=match(d1,a:a,0)
show?





cwwolfdog wrote:

Thanks for your help,

i am having a problem I believe with the .range ("a:a"), 0) Everytime I run
the form it comes back with invalid user id. I am probably missing something
very basic.

Thanks Again.

"Dave Peterson" wrote:

First, worksheet protection is pretty easy to break. In just moments, everyone
can see your list of id's/passwords.

And the protection for the VBA project is easy to break so that users can see
your code, too.

If that data is really private/confidential, don't share it in excel.

But this may get you closer if you still want to use it.

I put my id's in column A, the passwords in column B and the worksheets to go to
in column C of the worksheet named Hidden:

Option Explicit
Private Sub CommandButton1_Click()

Dim HidWks As Worksheet
Dim res As Variant

Set HidWks = Worksheets("hidden")

With HidWks
res = Application.Match(Me.TextBox1.Value, .Range("a:a"), 0)
If IsError(res) Then
MsgBox "Invalid User Id"
Exit Sub
Else
If CStr(.Range("a:a")(res, 2).Value) < Me.TextBox2.Value Then
MsgBox "Invalid password for ID"
Exit Sub
End If
End If

With .Parent.Worksheets(.Range("a:a")(res, 3).Value)
.Visible = xlSheetVisible
.Select
.Range("a1").Select
End With

Unload Me

End With

End Sub



cwwolfdog wrote:

I am using a userform to get employees id number and passwords. I have the
id numbers and passwords on a hidden sheet. I need to code in the submit
button on the form a way to search the hidden sheet for there id number and
password match and then if it does match take them to a specific sheet.
Should I be going about this in a different manner or is this possible?

Thanks

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

You could also do the opposite.

You could put your data in column A as text.

Either format the cell as text or precede your data entry with an apostrophe
('616).

Formatting the cells as text won't have any effect until you reenter the data.

You could use a formula like: =a1&""
in a temporary column
then copy|paste special|values (over column A)
and destroy that column of helper formulas.

Dave Peterson wrote:

If your userids are numbers on sheet3, then you have to convert the Text values
that are entered into that txtUswNumber text box.

If those ID's are just integers (no decimals), then you could use:

res = Application.Match(clng(Me.txtUswNumber.Value), .Range("a:a"), 0)

Clng() will do that conversion.

cwwolfdog wrote:

Here is my code. I tried your example and still got the same error.

Private Sub cmdSubmit_Click()

Dim HidWks As Worksheet
Dim res As Variant

Set HidWks = Worksheets("Sheet3")

With HidWks
res = Application.Match(Me.txtUswNumber.Value, .Range("a:a"), 0)
If IsError(res) Then
MsgBox "Invalid User Id"
Exit Sub
Else
If CStr(.Range("a:a")(res, 2).Value) < Me.txtPassword.Value Then
MsgBox "Invalid password for ID"
Exit Sub
End If
End If

With .Parent.Worksheets(.Range("a:a")(res, 3).Value)
.Visible = xlSheetVisible
.Select
.Range("a1").Select
End With

Unload Me

End With

Unload Me

End Sub

Worksheet 3 has:
616 caleb616 Sheet4
520 Alex520 Sheet5
628 Brandi628 Sheet6

Thanks Again.

"Dave Peterson" wrote:

Do you have extra spaces in column A (or in the textbox)?

If you take the value in the textbox and paste it into D1 (say) of the Hidden
worksheet, what does:
=match(d1,a:a,0)
show?





cwwolfdog wrote:

Thanks for your help,

i am having a problem I believe with the .range ("a:a"), 0) Everytime I run
the form it comes back with invalid user id. I am probably missing something
very basic.

Thanks Again.

"Dave Peterson" wrote:

First, worksheet protection is pretty easy to break. In just moments, everyone
can see your list of id's/passwords.

And the protection for the VBA project is easy to break so that users can see
your code, too.

If that data is really private/confidential, don't share it in excel.

But this may get you closer if you still want to use it.

I put my id's in column A, the passwords in column B and the worksheets to go to
in column C of the worksheet named Hidden:

Option Explicit
Private Sub CommandButton1_Click()

Dim HidWks As Worksheet
Dim res As Variant

Set HidWks = Worksheets("hidden")

With HidWks
res = Application.Match(Me.TextBox1.Value, .Range("a:a"), 0)
If IsError(res) Then
MsgBox "Invalid User Id"
Exit Sub
Else
If CStr(.Range("a:a")(res, 2).Value) < Me.TextBox2.Value Then
MsgBox "Invalid password for ID"
Exit Sub
End If
End If

With .Parent.Worksheets(.Range("a:a")(res, 3).Value)
.Visible = xlSheetVisible
.Select
.Range("a1").Select
End With

Unload Me

End With

End Sub



cwwolfdog wrote:

I am using a userform to get employees id number and passwords. I have the
id numbers and passwords on a hidden sheet. I need to code in the submit
button on the form a way to search the hidden sheet for there id number and
password match and then if it does match take them to a specific sheet.
Should I be going about this in a different manner or is this possible?

Thanks

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson

That did the trick, thanks a bunch Dave.

"Dave Peterson" wrote:

You could also do the opposite.

You could put your data in column A as text.

Either format the cell as text or precede your data entry with an apostrophe
('616).

Formatting the cells as text won't have any effect until you reenter the data.

You could use a formula like: =a1&""
in a temporary column
then copy|paste special|values (over column A)
and destroy that column of helper formulas.

Dave Peterson wrote:

If your userids are numbers on sheet3, then you have to convert the Text values
that are entered into that txtUswNumber text box.

If those ID's are just integers (no decimals), then you could use:

res = Application.Match(clng(Me.txtUswNumber.Value), .Range("a:a"), 0)

Clng() will do that conversion.

cwwolfdog wrote:

Here is my code. I tried your example and still got the same error.

Private Sub cmdSubmit_Click()

Dim HidWks As Worksheet
Dim res As Variant

Set HidWks = Worksheets("Sheet3")

With HidWks
res = Application.Match(Me.txtUswNumber.Value, .Range("a:a"), 0)
If IsError(res) Then
MsgBox "Invalid User Id"
Exit Sub
Else
If CStr(.Range("a:a")(res, 2).Value) < Me.txtPassword.Value Then
MsgBox "Invalid password for ID"
Exit Sub
End If
End If

With .Parent.Worksheets(.Range("a:a")(res, 3).Value)
.Visible = xlSheetVisible
.Select
.Range("a1").Select
End With

Unload Me

End With

Unload Me

End Sub

Worksheet 3 has:
616 caleb616 Sheet4
520 Alex520 Sheet5
628 Brandi628 Sheet6

Thanks Again.

"Dave Peterson" wrote:

Do you have extra spaces in column A (or in the textbox)?

If you take the value in the textbox and paste it into D1 (say) of the Hidden
worksheet, what does:
=match(d1,a:a,0)
show?





cwwolfdog wrote:

Thanks for your help,

i am having a problem I believe with the .range ("a:a"), 0) Everytime I run
the form it comes back with invalid user id. I am probably missing something
very basic.

Thanks Again.

"Dave Peterson" wrote:

First, worksheet protection is pretty easy to break. In just moments, everyone
can see your list of id's/passwords.

And the protection for the VBA project is easy to break so that users can see
your code, too.

If that data is really private/confidential, don't share it in excel.

But this may get you closer if you still want to use it.

I put my id's in column A, the passwords in column B and the worksheets to go to
in column C of the worksheet named Hidden:

Option Explicit
Private Sub CommandButton1_Click()

Dim HidWks As Worksheet
Dim res As Variant

Set HidWks = Worksheets("hidden")

With HidWks
res = Application.Match(Me.TextBox1.Value, .Range("a:a"), 0)
If IsError(res) Then
MsgBox "Invalid User Id"
Exit Sub
Else
If CStr(.Range("a:a")(res, 2).Value) < Me.TextBox2.Value Then
MsgBox "Invalid password for ID"
Exit Sub
End If
End If

With .Parent.Worksheets(.Range("a:a")(res, 3).Value)
.Visible = xlSheetVisible
.Select
.Range("a1").Select
End With

Unload Me

End With

End Sub



cwwolfdog wrote:

I am using a userform to get employees id number and passwords. I have the
id numbers and passwords on a hidden sheet. I need to code in the submit
button on the form a way to search the hidden sheet for there id number and
password match and then if it does match take them to a specific sheet.
Should I be going about this in a different manner or is this possible?

Thanks

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson

Sorry to bother you again Dave, but I had one more question.

The excel file is being shared by 50 employees, so does this mean that once
a sheet is made visible that everyone will be able to see their sheet. Is
there a way to make it visible to only that person, or is that stretching it.

Thanks

"cwwolfdog" wrote:

That did the trick, thanks a bunch Dave.

"Dave Peterson" wrote:

You could also do the opposite.

You could put your data in column A as text.

Either format the cell as text or precede your data entry with an apostrophe
('616).

Formatting the cells as text won't have any effect until you reenter the data.

You could use a formula like: =a1&""
in a temporary column
then copy|paste special|values (over column A)
and destroy that column of helper formulas.

Dave Peterson wrote:

If your userids are numbers on sheet3, then you have to convert the Text values
that are entered into that txtUswNumber text box.

If those ID's are just integers (no decimals), then you could use:

res = Application.Match(clng(Me.txtUswNumber.Value), .Range("a:a"), 0)

Clng() will do that conversion.

cwwolfdog wrote:

Here is my code. I tried your example and still got the same error.

Private Sub cmdSubmit_Click()

Dim HidWks As Worksheet
Dim res As Variant

Set HidWks = Worksheets("Sheet3")

With HidWks
res = Application.Match(Me.txtUswNumber.Value, .Range("a:a"), 0)
If IsError(res) Then
MsgBox "Invalid User Id"
Exit Sub
Else
If CStr(.Range("a:a")(res, 2).Value) < Me.txtPassword.Value Then
MsgBox "Invalid password for ID"
Exit Sub
End If
End If

With .Parent.Worksheets(.Range("a:a")(res, 3).Value)
.Visible = xlSheetVisible
.Select
.Range("a1").Select
End With

Unload Me

End With

Unload Me

End Sub

Worksheet 3 has:
616 caleb616 Sheet4
520 Alex520 Sheet5
628 Brandi628 Sheet6

Thanks Again.

"Dave Peterson" wrote:

Do you have extra spaces in column A (or in the textbox)?

If you take the value in the textbox and paste it into D1 (say) of the Hidden
worksheet, what does:
=match(d1,a:a,0)
show?





cwwolfdog wrote:

Thanks for your help,

i am having a problem I believe with the .range ("a:a"), 0) Everytime I run
the form it comes back with invalid user id. I am probably missing something
very basic.

Thanks Again.

"Dave Peterson" wrote:

First, worksheet protection is pretty easy to break. In just moments, everyone
can see your list of id's/passwords.

And the protection for the VBA project is easy to break so that users can see
your code, too.

If that data is really private/confidential, don't share it in excel.

But this may get you closer if you still want to use it.

I put my id's in column A, the passwords in column B and the worksheets to go to
in column C of the worksheet named Hidden:

Option Explicit
Private Sub CommandButton1_Click()

Dim HidWks As Worksheet
Dim res As Variant

Set HidWks = Worksheets("hidden")

With HidWks
res = Application.Match(Me.TextBox1.Value, .Range("a:a"), 0)
If IsError(res) Then
MsgBox "Invalid User Id"
Exit Sub
Else
If CStr(.Range("a:a")(res, 2).Value) < Me.TextBox2.Value Then
MsgBox "Invalid password for ID"
Exit Sub
End If
End If

With .Parent.Worksheets(.Range("a:a")(res, 3).Value)
.Visible = xlSheetVisible
.Select
.Range("a1").Select
End With

Unload Me

End With

End Sub



cwwolfdog wrote:

I am using a userform to get employees id number and passwords. I have the
id numbers and passwords on a hidden sheet. I need to code in the submit
button on the form a way to search the hidden sheet for there id number and
password match and then if it does match take them to a specific sheet.
Should I be going about this in a different manner or is this possible?

Thanks

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson

You can hide each sheet except for the one that should be seen. But this
becomes kind of intricate (and easily screwed up).

My suggestion is this:

Save the workbook with the real data with a password (under the
File|SaveAs|Tools menu). Don't share that password with anyone.

Now create another workbook that validates the user (move the userform out of
the workbook you just put it in (sorry)).

Then if the user disables macros, they won't get to open the other workbook.

If they enable macros, you can validate the user. If they're valid, you can
open the real workbook (you supply the password).

After you open the workbook, you can hide/show the worksheets that you want to
hide/show.

Kind of like...

Option Explicit
Private Sub CommandButton1_Click()

Dim HidWks As Worksheet
Dim res As Variant
Dim OkToContinue As Boolean
Dim RealWkbk As Workbook
Dim RealWkbkName As String
Dim RealWkbkPwd As String
Dim testStr As String
Dim wks As Worksheet
Dim wksToSee As String
Dim myID As Variant

RealWkbkName = "C:\my documents\excel\book2.xls"
RealWkbkPwd = "a"

testStr = ""
On Error Resume Next
testStr = Dir(RealWkbkName)
On Error GoTo 0

If testStr = "" Then
MsgBox "Design error--workbook not found!"
Else
Set HidWks = Worksheets("hidden")
OkToContinue = True
With HidWks
If IsNumeric(Me.TextBox1.Value) Then
myID = CLng(Me.TextBox1.Value)
End If
res = Application.Match(myID, .Range("a:a"), 0)
If IsError(res) Then
MsgBox "Invalid User Id"
OkToContinue = False
Else
If CStr(.Range("a:a")(res, 2).Value) < Me.TextBox2.Value Then
MsgBox "Invalid password for ID"
OkToContinue = False
End If
End If

If OkToContinue = True Then
wksToSee = .Range("a:a")(res, 3).Value
Set RealWkbk = Workbooks.Open(Filename:=RealWkbkName, _
Password:=RealWkbkPwd)
'show the one you need to show
If WorksheetExists(wksToSee, RealWkbk) = False Then
MsgBox "Design error--sheet doesn't exist"
Else
RealWkbk.Worksheets(wksToSee).Visible = True
For Each wks In RealWkbk.Worksheets
If LCase(wks.Name) = wksToSee Then
'already shown, do nothing
Else
wks.Visible = xlSheetVeryHidden
End If
Next wks
End If
End If
End With
End If
Unload Me
End Sub


Then in a general module:

Option Explicit
Sub auto_open()
Application.EnableCancelKey = xlDisabled
UserForm1.Show
Application.EnableCancelKey = xlInterrupt
'ThisWorkbook.Close savechanges:=True
End Sub
Function WorksheetExists(SheetName As String, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) 0)
End Function


When you're done testing and after you've saved, you can uncomment that .close
line at the end.

That second procedure (worksheetexists was stolen from Chip Pearson.)

The .enablecancelkey stops the user from breaking out of your code while it's
executing.



cwwolfdog wrote:

Sorry to bother you again Dave, but I had one more question.

The excel file is being shared by 50 employees, so does this mean that once
a sheet is made visible that everyone will be able to see their sheet. Is
there a way to make it visible to only that person, or is that stretching it.

Thanks

"cwwolfdog" wrote:

That did the trick, thanks a bunch Dave.

"Dave Peterson" wrote:

You could also do the opposite.

You could put your data in column A as text.

Either format the cell as text or precede your data entry with an apostrophe
('616).

Formatting the cells as text won't have any effect until you reenter the data.

You could use a formula like: =a1&""
in a temporary column
then copy|paste special|values (over column A)
and destroy that column of helper formulas.

Dave Peterson wrote:

If your userids are numbers on sheet3, then you have to convert the Text values
that are entered into that txtUswNumber text box.

If those ID's are just integers (no decimals), then you could use:

res = Application.Match(clng(Me.txtUswNumber.Value), .Range("a:a"), 0)

Clng() will do that conversion.

cwwolfdog wrote:

Here is my code. I tried your example and still got the same error.

Private Sub cmdSubmit_Click()

Dim HidWks As Worksheet
Dim res As Variant

Set HidWks = Worksheets("Sheet3")

With HidWks
res = Application.Match(Me.txtUswNumber.Value, .Range("a:a"), 0)
If IsError(res) Then
MsgBox "Invalid User Id"
Exit Sub
Else
If CStr(.Range("a:a")(res, 2).Value) < Me.txtPassword.Value Then
MsgBox "Invalid password for ID"
Exit Sub
End If
End If

With .Parent.Worksheets(.Range("a:a")(res, 3).Value)
.Visible = xlSheetVisible
.Select
.Range("a1").Select
End With

Unload Me

End With

Unload Me

End Sub

Worksheet 3 has:
616 caleb616 Sheet4
520 Alex520 Sheet5
628 Brandi628 Sheet6

Thanks Again.

"Dave Peterson" wrote:

Do you have extra spaces in column A (or in the textbox)?

If you take the value in the textbox and paste it into D1 (say) of the Hidden
worksheet, what does:
=match(d1,a:a,0)
show?





cwwolfdog wrote:

Thanks for your help,

i am having a problem I believe with the .range ("a:a"), 0) Everytime I run
the form it comes back with invalid user id. I am probably missing something
very basic.

Thanks Again.

"Dave Peterson" wrote:

First, worksheet protection is pretty easy to break. In just moments, everyone
can see your list of id's/passwords.

And the protection for the VBA project is easy to break so that users can see
your code, too.

If that data is really private/confidential, don't share it in excel.

But this may get you closer if you still want to use it.

I put my id's in column A, the passwords in column B and the worksheets to go to
in column C of the worksheet named Hidden:

Option Explicit
Private Sub CommandButton1_Click()

Dim HidWks As Worksheet
Dim res As Variant

Set HidWks = Worksheets("hidden")

With HidWks
res = Application.Match(Me.TextBox1.Value, .Range("a:a"), 0)
If IsError(res) Then
MsgBox "Invalid User Id"
Exit Sub
Else
If CStr(.Range("a:a")(res, 2).Value) < Me.TextBox2.Value Then
MsgBox "Invalid password for ID"
Exit Sub
End If
End If

With .Parent.Worksheets(.Range("a:a")(res, 3).Value)
.Visible = xlSheetVisible
.Select
.Range("a1").Select
End With

Unload Me

End With

End Sub



cwwolfdog wrote:

I am using a userform to get employees id number and passwords. I have the
id numbers and passwords on a hidden sheet. I need to code in the submit
button on the form a way to search the hidden sheet for there id number and
password match and then if it does match take them to a specific sheet.
Should I be going about this in a different manner or is this possible?

Thanks

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson


--

Dave Peterson

dave,

I don't know if I was clear enought in my previous response, or maybe I am
not understanding the code. The workbook is shared with 50 employees at the
same time, meaning that more than one person will be editing and saving the
workbook regulary. Probably around 20 - 30 people at a given time. Wouldn't
the employees be able to see the worksheets of everyone who was currently
working on the workbook? Should I try a merging workbook and give everyone
there own document then merge into a master, or is there still a way to do it.

thanks

"Dave Peterson" wrote:

You can hide each sheet except for the one that should be seen. But this
becomes kind of intricate (and easily screwed up).

My suggestion is this:

Save the workbook with the real data with a password (under the
File|SaveAs|Tools menu). Don't share that password with anyone.

Now create another workbook that validates the user (move the userform out of
the workbook you just put it in (sorry)).

Then if the user disables macros, they won't get to open the other workbook.

If they enable macros, you can validate the user. If they're valid, you can
open the real workbook (you supply the password).

After you open the workbook, you can hide/show the worksheets that you want to
hide/show.

Kind of like...

Option Explicit
Private Sub CommandButton1_Click()

Dim HidWks As Worksheet
Dim res As Variant
Dim OkToContinue As Boolean
Dim RealWkbk As Workbook
Dim RealWkbkName As String
Dim RealWkbkPwd As String
Dim testStr As String
Dim wks As Worksheet
Dim wksToSee As String
Dim myID As Variant

RealWkbkName = "C:\my documents\excel\book2.xls"
RealWkbkPwd = "a"

testStr = ""
On Error Resume Next
testStr = Dir(RealWkbkName)
On Error GoTo 0

If testStr = "" Then
MsgBox "Design error--workbook not found!"
Else
Set HidWks = Worksheets("hidden")
OkToContinue = True
With HidWks
If IsNumeric(Me.TextBox1.Value) Then
myID = CLng(Me.TextBox1.Value)
End If
res = Application.Match(myID, .Range("a:a"), 0)
If IsError(res) Then
MsgBox "Invalid User Id"
OkToContinue = False
Else
If CStr(.Range("a:a")(res, 2).Value) < Me.TextBox2.Value Then
MsgBox "Invalid password for ID"
OkToContinue = False
End If
End If

If OkToContinue = True Then
wksToSee = .Range("a:a")(res, 3).Value
Set RealWkbk = Workbooks.Open(Filename:=RealWkbkName, _
Password:=RealWkbkPwd)
'show the one you need to show
If WorksheetExists(wksToSee, RealWkbk) = False Then
MsgBox "Design error--sheet doesn't exist"
Else
RealWkbk.Worksheets(wksToSee).Visible = True
For Each wks In RealWkbk.Worksheets
If LCase(wks.Name) = wksToSee Then
'already shown, do nothing
Else
wks.Visible = xlSheetVeryHidden
End If
Next wks
End If
End If
End With
End If
Unload Me
End Sub


Then in a general module:

Option Explicit
Sub auto_open()
Application.EnableCancelKey = xlDisabled
UserForm1.Show
Application.EnableCancelKey = xlInterrupt
'ThisWorkbook.Close savechanges:=True
End Sub
Function WorksheetExists(SheetName As String, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) 0)
End Function


When you're done testing and after you've saved, you can uncomment that .close
line at the end.

That second procedure (worksheetexists was stolen from Chip Pearson.)

The .enablecancelkey stops the user from breaking out of your code while it's
executing.



cwwolfdog wrote:

Sorry to bother you again Dave, but I had one more question.

The excel file is being shared by 50 employees, so does this mean that once
a sheet is made visible that everyone will be able to see their sheet. Is
there a way to make it visible to only that person, or is that stretching it.

Thanks

"cwwolfdog" wrote:

That did the trick, thanks a bunch Dave.

"Dave Peterson" wrote:

You could also do the opposite.

You could put your data in column A as text.

Either format the cell as text or precede your data entry with an apostrophe
('616).

Formatting the cells as text won't have any effect until you reenter the data.

You could use a formula like: =a1&""
in a temporary column
then copy|paste special|values (over column A)
and destroy that column of helper formulas.

Dave Peterson wrote:

If your userids are numbers on sheet3, then you have to convert the Text values
that are entered into that txtUswNumber text box.

If those ID's are just integers (no decimals), then you could use:

res = Application.Match(clng(Me.txtUswNumber.Value), .Range("a:a"), 0)

Clng() will do that conversion.

cwwolfdog wrote:

Here is my code. I tried your example and still got the same error.

Private Sub cmdSubmit_Click()

Dim HidWks As Worksheet
Dim res As Variant

Set HidWks = Worksheets("Sheet3")

With HidWks
res = Application.Match(Me.txtUswNumber.Value, .Range("a:a"), 0)
If IsError(res) Then
MsgBox "Invalid User Id"
Exit Sub
Else
If CStr(.Range("a:a")(res, 2).Value) < Me.txtPassword.Value Then
MsgBox "Invalid password for ID"
Exit Sub
End If
End If

With .Parent.Worksheets(.Range("a:a")(res, 3).Value)
.Visible = xlSheetVisible
.Select
.Range("a1").Select
End With

Unload Me

End With

Unload Me

End Sub

Worksheet 3 has:
616 caleb616 Sheet4
520 Alex520 Sheet5
628 Brandi628 Sheet6

Thanks Again.

"Dave Peterson" wrote:

Do you have extra spaces in column A (or in the textbox)?

If you take the value in the textbox and paste it into D1 (say) of the Hidden
worksheet, what does:
=match(d1,a:a,0)
show?





cwwolfdog wrote:

Thanks for your help,

i am having a problem I believe with the .range ("a:a"), 0) Everytime I run
the form it comes back with invalid user id. I am probably missing something
very basic.

Thanks Again.

"Dave Peterson" wrote:

First, worksheet protection is pretty easy to break. In just moments, everyone
can see your list of id's/passwords.

And the protection for the VBA project is easy to break so that users can see
your code, too.

If that data is really private/confidential, don't share it in excel.

But this may get you closer if you still want to use it.

I put my id's in column A, the passwords in column B and the worksheets to go to
in column C of the worksheet named Hidden:

Option Explicit
Private Sub CommandButton1_Click()

Dim HidWks As Worksheet
Dim res As Variant

Set HidWks = Worksheets("hidden")

With HidWks
res = Application.Match(Me.TextBox1.Value, .Range("a:a"), 0)
If IsError(res) Then
MsgBox "Invalid User Id"
Exit Sub
Else
If CStr(.Range("a:a")(res, 2).Value) < Me.TextBox2.Value Then
MsgBox "Invalid password for ID"
Exit Sub
End If
End If

With .Parent.Worksheets(.Range("a:a")(res, 3).Value)
.Visible = xlSheetVisible
.Select
.Range("a1").Select
End With

Unload Me

End With

End Sub



cwwolfdog wrote:

I am using a userform to get employees id number and passwords. I have the
id numbers and passwords on a hidden sheet. I need to code in the submit
button on the form a way to search the hidden sheet for there id number and
password match and then if it does match take them to a specific sheet.
Should I be going about this in a different manner or is this possible?

Thanks

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson


--

Dave Peterson

Do some small experiments.

I don't work with shared workbooks a lot, but I would imagine that the macro
will hide/unhide each sheet independently. (But that's a guess.)

If you really want confidentiality, then don't share the data using excel. Keep
each set of data in separate workbooks.

cwwolfdog wrote:

dave,

I don't know if I was clear enought in my previous response, or maybe I am
not understanding the code. The workbook is shared with 50 employees at the
same time, meaning that more than one person will be editing and saving the
workbook regulary. Probably around 20 - 30 people at a given time. Wouldn't
the employees be able to see the worksheets of everyone who was currently
working on the workbook? Should I try a merging workbook and give everyone
there own document then merge into a master, or is there still a way to do it.

thanks

"Dave Peterson" wrote:

You can hide each sheet except for the one that should be seen. But this
becomes kind of intricate (and easily screwed up).

My suggestion is this:

Save the workbook with the real data with a password (under the
File|SaveAs|Tools menu). Don't share that password with anyone.

Now create another workbook that validates the user (move the userform out of
the workbook you just put it in (sorry)).

Then if the user disables macros, they won't get to open the other workbook.

If they enable macros, you can validate the user. If they're valid, you can
open the real workbook (you supply the password).

After you open the workbook, you can hide/show the worksheets that you want to
hide/show.

Kind of like...

Option Explicit
Private Sub CommandButton1_Click()

Dim HidWks As Worksheet
Dim res As Variant
Dim OkToContinue As Boolean
Dim RealWkbk As Workbook
Dim RealWkbkName As String
Dim RealWkbkPwd As String
Dim testStr As String
Dim wks As Worksheet
Dim wksToSee As String
Dim myID As Variant

RealWkbkName = "C:\my documents\excel\book2.xls"
RealWkbkPwd = "a"

testStr = ""
On Error Resume Next
testStr = Dir(RealWkbkName)
On Error GoTo 0

If testStr = "" Then
MsgBox "Design error--workbook not found!"
Else
Set HidWks = Worksheets("hidden")
OkToContinue = True
With HidWks
If IsNumeric(Me.TextBox1.Value) Then
myID = CLng(Me.TextBox1.Value)
End If
res = Application.Match(myID, .Range("a:a"), 0)
If IsError(res) Then
MsgBox "Invalid User Id"
OkToContinue = False
Else
If CStr(.Range("a:a")(res, 2).Value) < Me.TextBox2.Value Then
MsgBox "Invalid password for ID"
OkToContinue = False
End If
End If

If OkToContinue = True Then
wksToSee = .Range("a:a")(res, 3).Value
Set RealWkbk = Workbooks.Open(Filename:=RealWkbkName, _
Password:=RealWkbkPwd)
'show the one you need to show
If WorksheetExists(wksToSee, RealWkbk) = False Then
MsgBox "Design error--sheet doesn't exist"
Else
RealWkbk.Worksheets(wksToSee).Visible = True
For Each wks In RealWkbk.Worksheets
If LCase(wks.Name) = wksToSee Then
'already shown, do nothing
Else
wks.Visible = xlSheetVeryHidden
End If
Next wks
End If
End If
End With
End If
Unload Me
End Sub


Then in a general module:

Option Explicit
Sub auto_open()
Application.EnableCancelKey = xlDisabled
UserForm1.Show
Application.EnableCancelKey = xlInterrupt
'ThisWorkbook.Close savechanges:=True
End Sub
Function WorksheetExists(SheetName As String, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) 0)
End Function


When you're done testing and after you've saved, you can uncomment that .close
line at the end.

That second procedure (worksheetexists was stolen from Chip Pearson.)

The .enablecancelkey stops the user from breaking out of your code while it's
executing.



cwwolfdog wrote:

Sorry to bother you again Dave, but I had one more question.

The excel file is being shared by 50 employees, so does this mean that once
a sheet is made visible that everyone will be able to see their sheet. Is
there a way to make it visible to only that person, or is that stretching it.

Thanks

"cwwolfdog" wrote:

That did the trick, thanks a bunch Dave.

"Dave Peterson" wrote:

You could also do the opposite.

You could put your data in column A as text.

Either format the cell as text or precede your data entry with an apostrophe
('616).

Formatting the cells as text won't have any effect until you reenter the data.

You could use a formula like: =a1&""
in a temporary column
then copy|paste special|values (over column A)
and destroy that column of helper formulas.

Dave Peterson wrote:

If your userids are numbers on sheet3, then you have to convert the Text values
that are entered into that txtUswNumber text box.

If those ID's are just integers (no decimals), then you could use:

res = Application.Match(clng(Me.txtUswNumber.Value), .Range("a:a"), 0)

Clng() will do that conversion.

cwwolfdog wrote:

Here is my code. I tried your example and still got the same error.

Private Sub cmdSubmit_Click()

Dim HidWks As Worksheet
Dim res As Variant

Set HidWks = Worksheets("Sheet3")

With HidWks
res = Application.Match(Me.txtUswNumber.Value, .Range("a:a"), 0)
If IsError(res) Then
MsgBox "Invalid User Id"
Exit Sub
Else
If CStr(.Range("a:a")(res, 2).Value) < Me.txtPassword.Value Then
MsgBox "Invalid password for ID"
Exit Sub
End If
End If

With .Parent.Worksheets(.Range("a:a")(res, 3).Value)
.Visible = xlSheetVisible
.Select
.Range("a1").Select
End With

Unload Me

End With

Unload Me

End Sub

Worksheet 3 has:
616 caleb616 Sheet4
520 Alex520 Sheet5
628 Brandi628 Sheet6

Thanks Again.

"Dave Peterson" wrote:

Do you have extra spaces in column A (or in the textbox)?

If you take the value in the textbox and paste it into D1 (say) of the Hidden
worksheet, what does:
=match(d1,a:a,0)
show?





cwwolfdog wrote:

Thanks for your help,

i am having a problem I believe with the .range ("a:a"), 0) Everytime I run
the form it comes back with invalid user id. I am probably missing something
very basic.

Thanks Again.

"Dave Peterson" wrote:

First, worksheet protection is pretty easy to break. In just moments, everyone
can see your list of id's/passwords.

And the protection for the VBA project is easy to break so that users can see
your code, too.

If that data is really private/confidential, don't share it in excel.

But this may get you closer if you still want to use it.

I put my id's in column A, the passwords in column B and the worksheets to go to
in column C of the worksheet named Hidden:

Option Explicit
Private Sub CommandButton1_Click()

Dim HidWks As Worksheet
Dim res As Variant

Set HidWks = Worksheets("hidden")

With HidWks
res = Application.Match(Me.TextBox1.Value, .Range("a:a"), 0)
If IsError(res) Then
MsgBox "Invalid User Id"
Exit Sub
Else
If CStr(.Range("a:a")(res, 2).Value) < Me.TextBox2.Value Then
MsgBox "Invalid password for ID"
Exit Sub
End If
End If

With .Parent.Worksheets(.Range("a:a")(res, 3).Value)
.Visible = xlSheetVisible
.Select
.Range("a1").Select
End With

Unload Me

End With

End Sub



cwwolfdog wrote:

I am using a userform to get employees id number and passwords. I have the
id numbers and passwords on a hidden sheet. I need to code in the submit
button on the form a way to search the hidden sheet for there id number and
password match and then if it does match take them to a specific sheet.
Should I be going about this in a different manner or is this possible?

Thanks

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson