Conditional Code or Function that will go to a worksheet

[NICMARTY]

Is there a code or function that will direct you to the first cell of the worksheet desired? For instance, I have three columns, the first with the username, the second with the password and the third with the sheet name. if I have a seperate worksheet with where a user will enter the user name and password, a formula will look up the info and direct them to the correct worksheet. Is this possible?

 

[brian.wethington]

Not sure exactly what you asking. Do have several sheets with the same type of data set up and you want to find the correct sheet to go to in the code and go to that sheet? Where will they put the username and password. We need a little more information, but I am quite sure it can be done.

 

[NICMARTY]

I have several sheets with personal data. I want these all to be hidden. There will be one sheet With the lookup info (username, password, sheet name) and another sheet (the only one visible when opening the file) that they will enter their username and password. Upon entering the correct info, the formula or code would then unhide the worksheet assigned to that person.

 

[brian.wethington]

I do this sort of thing like this. Remember that Excel is not a secure environment so if security is of utmost importance you will need to try something else.

this code would go in the workbook module

Option Explicit
Private Sub Workbook_Open()

Dim MyPass As String, x As Long

MyPass = InputBox("Please provide password", "Password protected workbook", "")
Application.ScreenUpdating = False
Select Case MyPass
    Case "xxxxxx"
        For x = 7 To 2 Step -1
            Sheets(x).Visible = True
        Next x
        Sheets("Error").Visible = False
    Case Else
        MsgBox "You must supply the correct password to proceed"
        Application.ScreenUpdating = True
        ActiveWorkbook.Close
End Select
Application.ScreenUpdating = True

End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim x As Long

Application.ScreenUpdating = False
Sheets("Error").Visible = True
For x = 7 To 2 Step -1
    Sheets(x).Visible = xlVeryHidden
Next x
ActiveWorkbook.Save
Application.ScreenUpdating = True

End Sub

This code simply asks for a password, however, you can set it to take in a username Then you can use a select case to unhide whichever sheets you wish. This code specifically simply hides the first sheet and unhides all others but you can set that up however you need. One last thing the code will save no matter what as closing at this point. This was a quick sure fire way for me to ensure that if they saved it it would still get saved with the first page unhidden and the others all hidden.

Shooting for something like this?

Hope this gives you some ideas at least!

 

[brian.wethington]

BTW, you can password protect your work to create a little more security.... if only a bit more.

Right click the project, Select VBAProject Properties, choose the protection tab, check the lock project for viewing, put in a password and confirm.