Open workbook using username and password

[ABabeNChrist]

I got this idea from viewing a previous thread about using a userform with a username and password entry that would unhide different columns. I made a couple of modification so that I could use the same basic approach but instead of unhide a column it would now insert the password on to sheet1 cell A1, which is a hidden sheet, then from there some more code UserForm1.Show. UserForm1 is used as a main panel to access different sheets. I then added

Code: Select all

Private Sub workbook_open()
    If Sheets("Sheet1").Range("A1") = "ababenchrist" Then
        UserForm1.Show
        Exit Sub
    End If
    
    UserForm2.Show
End Sub

So that once the correct password was used and the workbook was opened, the password would not be needed again.
I’m hoping I did this correct…..
Below I have attached a sample of this workbook
Username and password are both ababenchrist

Sample username and password.xlsm

[HansV]

Yes, it works.

I would make the sheet with the password a "very hidden" sheet. You can do this in the Visual Basic Editor:

x300.png

or using code:

Worksheets("Sheet1").Visible = xlSheetVeryHidden

A "very hidden" sheet cannot be unhidden from the Excel interface - it is not listed if you look at Format | Hide and Unhide | Unhide Sheet. It can only be unhidden in the Visual Basic Editor or using code.

[ABabeNChrist]

OOOOOOOh very cool, that’s a great idea. I knew about very hidden but not the part about it can only be unhidden in the Visual Basic Editor or using code.
Thank you Hans
I like playing around and trying different things, I'm still very new to this, but I found it very interesting.

[ABabeNChrist]

Hi Hans
I was thinking of possibly adding a piece of code that will open a web page, something like this.

Code: Select all

        ActiveWorkbook.FollowHyperlink _
                Address:="http://www.MyWebSite.html", _
                NewWindow:=True

I wasn’t sure of the correct location, I thought maybe after End Select and also adding On Error Resume Next, in case the internet service was unavailable.

Code: Select all

Private Sub PassWord_Click()
    Dim bError As Boolean
    Inspect = False
    bError = True
    If Len(txtUser.Text) > 0 And Len(txtPass.Text) > 0 Then
        Select Case txtUser.Text
        Case "ababenchrist"
            If txtPass.Text = "ababenchrist" Then
                bError = False
                Sheets("Sheet1").Unprotect
                Sheets("Sheet1").Range("A1") = "ababenchrist"
                Sheets("Sheet1").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                                         PassWord:="", UserInterfaceOnly:=True

            End If
        End Select
        On Error Resume Next
        'Enter desired file path with a URL
        ActiveWorkbook.FollowHyperlink _
                Address:="http://www.MyWebSite.html", _
                NewWindow:=True

    End If
    If bError Then
        MsgBox "Invalid User Name or Password"
    Else
        Inspect = True
        Unload Me
        UserForm1.Show
    End If
End Sub

The method of my madness for adding a web site address is so the user can register from my web site
I will then have the web site close once information has been entered, thus returning back to workbook.
Is this done correctly or do i need to make some changes
any help is always greatly appreciated.......

[HansV]

In this location, the code will display the web page if the user has entered a password, whether it was correct or not. If the user has left the password blank, the web page will not be displayed.
Is that what you intended?

[ABabeNChrist]

Hi Hans
I would like for the web page to be displayed only after the correct username and password has been entered. Now if there is no internet connection then skip code that will display web page and then continue with code. That’s why I thought of adding On Error Resume Next.
So would I add my code before End Select ?

[HansV]

See picture below:

x332.png

(And yes, you need the line On Error Resume Next.)

[ABabeNChrist]

Thank You Hans

[ABabeNChrist]

Here I go again, I was thinking of removing the website access code from this point and use a UserForm that is more flexible and can give me more options, JPG and beautiful colors.

The reason for change is that the website access was for registering and with the current approach the client has only one opportunity to register and if client has no internet connection they’ll be no opportunity.

So I thought by removing the code to access a website, I would just let code run its course as it is.
So what I’m trying to figure out is how to have this same UserForm , let’s call it UserForm2, open first upon opening of workbook before UserForm1, but once client has selected the register now button or cancel on UserForm2 then this userform will not reappear anymore and only UserForm1 will appear.
Here is the code I am using in My Workbook Module at the present moment; I figure that’s where the change must be made.

Code: Select all

Option Explicit
Private Sub workbook_open()
    If Sheets("Sheet1").Range("A1") = "Password" Then
        UserForm1.Show 'Is used as main opening page for navagation
        Dim wsh As Worksheet
        For Each wsh In Worksheets
            wsh.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                        PassWord:="", UserInterfaceOnly:=True
        Next wsh
        ActiveWorkbook.Protect PassWord:="", Structure:=True, Windows:=True
        Exit Sub
    End If
    'Activation UserForm used for username and password
    Activation.Show
End Sub

[HansV]

I don't see any UserForm2 in your code, and I don't really understand what you want to do.

[ABabeNChrist]

UserForm2 will be my new access to website for registering
I’m sorry I wasn’t sure where and how to put UserForm2 in this code.

[HansV]

Please specify exactly and precisely in which situation(s) you want to show UserForm2, and in which situation(s) you do NOT want to show it.

[ABabeNChrist]

Please specify exactly and precisely in which situation(s) you want to show UserForm2, and in which situation(s) you do NOT want to show it.

I would like UserForm2 to open when workbook is first opened. If the client selects command button to register later on UserForm2 then UserForm1 will open and UserForm2 will continue to open first when workbook is opened
Now once client has selected either register now or cancel command button from UserForm2
UserForm2 will now NOT open upon opening of workbook but instead UserForm1 will now open
I hope you understand

[HansV]

Change the Workbook_Open code as follows:

Code: Select all

Private Sub workbook_open()
  Dim v As Variant
  On Error Resume Next
  v = Me.CustomDocumentProperties("SkipUserForm2")
  If Err Then
    Me.CustomDocumentProperties.Add _
      Name:="SkipUserForm2", _
      LinkToContent:=False, _
      Type:=msoPropertyTypeBoolean, _
      Value:=False
  End If
  On Error GoTo 0
  If Me.CustomDocumentProperties("SkipUserForm2") = False Then
    UserForm2.Show
  End If
  If Sheets("Sheet1").Range("A1") = "Password" Then
    UserForm1.Show 'Is used as main opening page for navagation
    Dim wsh As Worksheet
    For Each wsh In Worksheets
      wsh.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
        Password:="", UserInterfaceOnly:=True
    Next wsh
    ActiveWorkbook.Protect Password:="", Structure:=True, Windows:=True
    Exit Sub
  End If
  'Activation UserForm used for username and password
  Activation.Show
End Sub

Use code like this for the command buttons on UserForm2:

Code: Select all

Private Sub cmdCancel_Click()
  ThisWorkbook.CustomDocumentProperties("SkipUserForm2") = True
  Unload Me
End Sub

Private Sub cmdRegisterLater_Click()
  ThisWorkbook.CustomDocumentProperties("SkipUserForm2") = False
  ' Your code goes here
  ' ...
  Unload Me
End Sub

Private Sub cmdregisterNow_Click()
  ThisWorkbook.CustomDocumentProperties("SkipUserForm2") = True
  ' Your code goes here
  ' ...
  Unload Me
End Sub

[ABabeNChrist]

Thank you Hans
I wouldn’t have been able to figure this out on my own, not even on my best day with a bottle of smart pills.

I will put into place as you mentioned and give her a try…