User Name & Password

[sahm24]

Hi all,

New to the thread and a VBA rookie. I am trying to create a budget workbook with 13 worksheet (one for each department). The plan is to have a welcome page when someone's opens the workbook with a message box to asking the user to enter user name and password. Based on the information entered the relevant worksheet will open keeping the rest 12 hidden. I have a table as follow:

User Name Password Worksheet
Sahm24 1 Finance

Can i link a VBA coding to this table?

To make things complicated, there will be two users (CFO & Treasurer) who will be able to view all 13 worksheet if their password is correct. Any help is appreciated.

 

[Michael M]

You'll need a Workbook_Open event AND a Workbook_close event
Have a look at these, and if it's going to be suitable, repost with more detail
Make sure you have a sheet called "Welcome"

Private Sub Workbook_Open()
Dim PW As String
PW = InputBox("please Insert Your User Password", "Password", "")
    Select Case PW
        Case "A"
            Sheets("Sheet2").Visible = True
        Case "B"
            Sheets("Sheet3").Visible = True
        Case "C"
            Sheets("Sheet4").Visible = True
        Case "D" 'this could be the CFO & Treasurer
            For Each ws In ThisWorkbook.Worksheets
            ws.Visible = xlSheetVisible
            Next ws
        Case Else
            'All sheets remain hidden
    End Select
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim ws As Worksheet
      For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Welcome" Then ws.Visible = xlSheetVeryHidden
    Next ws
End Sub

 

[jbeaucaire]

I cracked this nut for a friend a couple of years ago. The test workbook can viewed on my site here:

Search results - Jerry Beaucaire's - Excel Assistant - "password"

It's the Loooong named one.

You don't need usernames, just passwords. The passwords are linked to as many sheets as you'd like on the Passwords sheet. Using the password "Admin" will reveal that sheet and it's very simple controlling structure.

In your real environment, you would obviously remove all the text from the FRONT sheet and simply let the user click the OPEN SHEET(s) button. The code is not protected, so tweak to your heart's content.


Be sure to look around the site, there's tons of base code examples for common needs like this one, you may find more useful tools.

 

[Danerida]

@Sahm24, you need to re-read the forum rules regarding cross-posting of topics in other forums.


Cross-Posted here:
User Name & Password

 

[Michael M]

Thanks Danerida

@Sahm24

Please read this link as well.

Excelguru Help Site - A message to forum cross posters

 

[sahm24]

@Sahm24, you need to re-read the forum rules regarding cross-posting of topics in other forums.


Cross-Posted here:
User Name & Password

Apologies for not complying with the forum rules. honest mistake.

 

[sahm24]

Hi Jerry massive help. thank you

One problem i am facing is the message box that pops up with "Update Link".

I have 13 different users who will have access to only their respective budget to the welcome page. The problem I am facing is, once a user has updated his budget, saves and close. When a different user open the workbook, instead of automatically opening the welcome page its give the update message box with the last opened worksheet in the backgroud (massive security issue). Once you click on Update Link then it takes you to the welcome page.

The only way I can think of solving the error is my disabling start-up prompt but i think that is based on user settings. other option is putting the following code

Application.AskToUpdateLinks = False

Not sure if this will work either.

Can anyone recommend anything?