Conditional Code or Function that will go to a worksheet

NICMARTY

New Member
Joined
Jun 14, 2007
Messages
15
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?
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

brian.wethington

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
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

New Member
Joined
Jun 14, 2007
Messages
15
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

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
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
Code:
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

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,868
Messages
5,598,551
Members
414,245
Latest member
allyciv

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top