[VBA] Userform to show upon opening excel sheet, then use a password to log in.

[LittleJon]

Please could someone give me the code for opening up a userform upon opening an excel sheet. Userform is called MainUF1. Then i need to put in a username and password.

Any help would be appreciated.

 

[mumps]

By " opening an excel sheet" do you mean activating a particular sheet in your workbook or opening the Excel file? What do you want the input of a username and password to do? Do you want to open the userform only if the correct username and password have been entered? What do you want to happen if the username and password are not correct?

 

[LittleJon]

Sorry, the userform needs to pop up when the workbook is opened. The username and password would give access to the workbook. I can add in the MsgBox for incorrect/correct entries.

Thanks

 

[mumps]

Place this macro in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up. Close the window to return to your sheet. Save the workbook as a macro-enabled file and close it. When you re-open it, you will be prompted for a username and password. If you enter incorrect data, the workbook will close. I have used 'username' for the user name and 'password' for the password. Change the information in the macro to suit your needs.

Private Sub Workbook_Open()
    Dim UN As String, PW As String, response1 As String, response2 As String
    UN = "[COLOR="#FF0000"]username[/COLOR]"
    PW = "[COLOR="#FF0000"]password[/COLOR]"
    response1 = InputBox("Please enter your username.")
    If response1 = "" Then
        MsgBox ("You have not entered a username.")
        ActiveWorkbook.Close False
    ElseIf response1 <> UN Then
        MsgBox ("Invalid user name.")
        ActiveWorkbook.Close False
    Else
        response2 = InputBox("Please enter your password.")
        If response2 = "" Then
            MsgBox ("You have not entered a password.")
            ActiveWorkbook.Close False
        ElseIf response2 <> PW Then
            MsgBox ("Invalid password.")
            ActiveWorkbook.Close False
        Else
            MainUF1.Show
        End If
    End If
End Sub

 

[LittleJon]

Thanks, the userform doesn't show up. Shouldn't this part of the code (MainUF1.show )be before the password?

When i run the userform, and click log in, the userform doesn't close. Should i add a bit of code to unload?

Thanks

 

[mumps]

Do you enter the username and password on the userform?

 

[LittleJon]

i tried replacing your red text with my own and also used your red text when i had to manually run the form. The form isn't showing up itself and the form isn't closing.

 

[mumps]

I need to know if you want the macro to prompt you to add the username and password or if you enter the username and password on the userform.

 

[LittleJon]

i don't need a msgbox prompt, i just need the userform to open up when someone tries to opens up the workbook.

Thanks again.

 

[mumps]

If you just need the userform to open, then this will do it:

Private Sub Workbook_Open()
    MainUF1.Show
End Sub

In Post #3 you said:

The username and password would give access to the workbook.

How do you plan to do this?