Form for Password to switch sheet

[yvoNL]

Hi there,

I'm working on a project in which I want a certain sheet to only visible to a certain person with the admin password.
Before I used an inputbox as the admin user password form. Worked perfectly. I made a macro as followed:

Sub ShowPassForm()
    ' Show password input box
    ibo = InputBox(prompt:="FIll in your admin password to go to the material library", Title:="Admin password - Material library")
    
    ' Check password
    If ibo = Sheets("Admin").Range("PassMatLib").Value Then
        
        ' Make the material library sheet visible and activate it.
        Sheets("Material library").Visible = 1
        Sheets("Material library").Activate


    Else
        
        ' Show error message
        mbo = MsgBox("An invalid password has been entered", vbOKOnly, "Invalid password")


    End If
End Sub

To go with that I added the following code to sheet itself to make the sheet very hidden after leaving it:

Private Sub Worksheet_Deactivate()
    
    Sheets("Material library").Visible = 2
    
End Sub

Next step was to add *'s while typing the password. After searching on Google a bit I found out this was not possible with an input and that I had to use a self made user form. So I made a simple form with an inputfield, and 2 buttons: enter & exit. I modified the code above to:

Sub ShowMatLib()
    ' Show password form
    frmAdminMatLib.Show
    
    ' Check password
    If Sheets("Admin").Range("PassMatLib").Value = frmAdminMatLib.txbPassword.Text Then
        ' Hide password form
        'frmAdminMatLib.Hide
        
        ' Empty password field
        'frmAdminMatLib.txbPassword.Value = ""
        'frmAdminMatLib.txbPassword.SetFocus
        
        ' Make the material library sheet visible and activate it.
        Sheets("Material library").Visible = 1
        Sheets("Material library").Activate


    Else
        ' Hide password form
        'frmAdminMatLib.Hide
        
        ' Show error message
        mbo = MsgBox("An invalid password has been entered", vbOKOnly, "Invalid password")
        
        ' Empty password field
        frmAdminMatLib.txbPassword.Value = ""
        frmAdminMatLib.txbPassword.SetFocus
    End If
End Sub

This seemed to work perfectly. The characters were hiding with *'s (after changing the right proprties of the textbox) and it switched to sheet "Material library" after making it visible. However I couldn't seem to scroll with my mouse in the "Material library", but the scroll bar is going up and down when I do. When I go back to the sheet were the password form appeared, it seems like I had been scrolling there. So in fact the "Material library" was activated, but Excel was still stuck on the previous sheet (were the password inputbox appeared).

I tried to use Select instead of activate:

Sheets("Material library").Range("B15").Select

But then I got a run time error 1004: Method Select of class Range has failed (translated from Dutch to English, so I'm not sure if it's exactly the same ).

I tried 1 more thing to see what happens: Activate the sheet, then show the password form. If the passowrd is incorrect go back to the input sheet. This is not really what I want, because people will see the contents when looking at the password form. However this way I could see if the form is to blame. So I changed the code to:

Sub ShowMatLib()
    ' Activate the material library
    Sheets("Material library").Visible = 1
    Sheets("Material library").Activate


    ' Show password form
    frmAdminMatLib.Show
    
    ' Check password
    If Sheets("Admin").Range("PassMatLib").Value = frmAdminMatLib.txbPassword.Text Then
        ' Hide password form
        'frmAdminMatLib.Hide
        
        ' Empty password field
        'frmAdminMatLib.txbPassword.Value = ""
        'frmAdminMatLib.txbPassword.SetFocus
        
    Else
        ' Hide password form
        'frmAdminMatLib.Hide
        
        ' Show error message
        mbo = MsgBox("An invalid password has been entered", vbOKOnly, "Invalid password")
        
        ' Empty password field
        frmAdminMatLib.txbPassword.Value = ""
        frmAdminMatLib.txbPassword.SetFocus

        Sheets("Input").Activate
        Sheets("Material library").Visible = 2



    End If
End Sub

This seemed to do the job. I could now scroll on the sheet. So that shows me that the form is to blame.

Does anybody know how to prevent the form from messing with my changing to another sheet?

PS Sorry for the long explenation, but I wanted the situation to be clear.

 

[yvoNL]

Nobody?
If something is unclear in my explanation, let me know.