Password and view of spreadsheet
 

Hello -

I want to create a user form that asks for a password, and according to that
password, allow the user to view only certain rows of data on certain sheets.
How would I go about doing this?

Any help will be greatly appreciated!

--
Sandy

Password and view of spreadsheet
 

add a sheet and in the IDE set its visible property to VERY HIDDEN ...thsi
will hold your data and being very hidded, can't be un-hid and read by the
user. you will need to protect your code to prevent your users openeing the
ide and changing the sheet's visible setting
on the hidden sheet create a table of user names in A and passwords in B
for each name add a third item in C, a comma separated list of sheets for
example that you want to be visible to this user. eg "sheet2,sheet3,sheet5"
You need to have one visible sheet, the set the visible property of all the
others to VERY HIDDEN


your userform should allow the user to enter their name and password, and is
succesful, unhide the relevent sheets.... you need to decide what to do if
they fail too of course....




Heres some code behind a userform with two text boxes and a command button.
a sheet is named passwords set as above

Option Explicit
Private counter As Long
Private Sub check_Click()
Dim sheets As Variant, index As Long
counter = counter + 1

If CheckPassword Then
sheets = Split(WorksheetFunction.VLookup(txtUserName,
Worksheets("passwords").Range("A:C"), 3, False), ",")
For index = LBound(sheets, 1) To UBound(sheets, 1)

Worksheets(sheets(index)).Visible = xlSheetVisible
Next
Unload Me
ElseIf counter = 3 Then
ThisWorkbook.Close False
Else
MsgBox "Try again"

End If

End Sub
Private Function CheckPassword() As Boolean
Dim pwd As String
With Worksheets("passwords")
On Error Resume Next
pwd = WorksheetFunction.VLookup(txtUserName, .Range("A:B"), 2, False)
On Error GoTo 0
If pwd = "" Then Exit Function
CheckPassword = (pwd = txtPassword)
End With
End Function






"Sandy" wrote:

Hello -

I want to create a user form that asks for a password, and according to that
password, allow the user to view only certain rows of data on certain sheets.
How would I go about doing this?

Any help will be greatly appreciated!

--
Sandy

Password and view of spreadsheet
 

Thank you very much, Patrick!
--
Sandy


"Patrick Molloy" wrote:

add a sheet and in the IDE set its visible property to VERY HIDDEN ...thsi
will hold your data and being very hidded, can't be un-hid and read by the
user. you will need to protect your code to prevent your users openeing the
ide and changing the sheet's visible setting
on the hidden sheet create a table of user names in A and passwords in B
for each name add a third item in C, a comma separated list of sheets for
example that you want to be visible to this user. eg "sheet2,sheet3,sheet5"
You need to have one visible sheet, the set the visible property of all the
others to VERY HIDDEN


your userform should allow the user to enter their name and password, and is
succesful, unhide the relevent sheets.... you need to decide what to do if
they fail too of course....




Heres some code behind a userform with two text boxes and a command button.
a sheet is named passwords set as above

Option Explicit
Private counter As Long
Private Sub check_Click()
Dim sheets As Variant, index As Long
counter = counter + 1

If CheckPassword Then
sheets = Split(WorksheetFunction.VLookup(txtUserName,
Worksheets("passwords").Range("A:C"), 3, False), ",")
For index = LBound(sheets, 1) To UBound(sheets, 1)

Worksheets(sheets(index)).Visible = xlSheetVisible
Next
Unload Me
ElseIf counter = 3 Then
ThisWorkbook.Close False
Else
MsgBox "Try again"

End If

End Sub
Private Function CheckPassword() As Boolean
Dim pwd As String
With Worksheets("passwords")
On Error Resume Next
pwd = WorksheetFunction.VLookup(txtUserName, .Range("A:B"), 2, False)
On Error GoTo 0
If pwd = "" Then Exit Function
CheckPassword = (pwd = txtPassword)
End With
End Function






"Sandy" wrote:

Hello -

I want to create a user form that asks for a password, and according to that
password, allow the user to view only certain rows of data on certain sheets.
How would I go about doing this?

Any help will be greatly appreciated!

--
Sandy