See Worksheet Tab, but can't Activate without Password Inputted

[RMXByker]

I have a worksheet labeled ADMIN that I would like to be seen in the worksheet tabs at the bottom of the workbook. However, I would like a password input to be required if the user tries to activate this tab. Is this possible? I thought potentially have some code in the worksheet activate tab but unsure. Anyone do this before?

 

[Max1616]

Hello,

Put this code in the sheet module that you would like protected:

Private Sub Worksheet_Activate()
Dim pw As String
Dim check As String

pw = "My Password"

Me.Visible = False
check = Application.InputBox("Enter Password to access sheet " & Me.Name & ".")
If check = pw Then
    Me.Visible = True
    Application.EnableEvents = False
    Me.Activate
    Application.EnableEvents = True
End If

End Sub

Update the pw variable (line 4) to whatever password you would like.

Let me know if you have any questions.

 

[My Aswer Is This]

Since you said:

I have a worksheet labeled ADMIN that I would like to be seen in the worksheet tabs at the bottom of the workbook. However, I would like a password input to be required if the user tries to activate this tab. Is this possible?

Try this:

If the password is not correct the script will Activate sheet(1)

So we will assume sheet named "Admin" is not Sheet(1)

Or modify script to activate some other sheet

Right click on the Admin sheet tab and paste in this script.

Private Sub Worksheet_Activate()
Dim ans As String
ans = InputBox("Enter Password")
If ans = "Test" Then
Else
MsgBox "Wrong Password"
Sheets(1).Activate
End If
Exit Sub
End Sub

 

[RMXByker]

Thanks for your guys help. One issue with what is shown above is that the user could still see the worksheet even with the popup. They just couldn't do anything with it. So I ended up simply adding a "Shape" hiding everything and a form popup as well. Then once the password is correct, it unlocks the sheet and hides the shape. Once locked, it protects the sheet and sets the visibility of the shape.

Thanks everyone. Below is the code I used in case anyone wants to tinker with it later one.

Sheet Code

Private Sub Worksheet_Activate()

    Application.ScreenUpdating = False
    ADMINLock.Show


End Sub

Form Code

Private Sub ADMINLockButton_Click()


    Sheets("ADMIN").Select
    ActiveSheet.Shapes("Rectangle 3").Visible = True
    Worksheets("ADMIN").Protect Password:="PASSWORD 2"


    Worksheets("Sheet 1").Activate
    Range("A10").Select
    
    Me.Hide
    Application.ScreenUpdating = True
    
End Sub




Private Sub ADMINUnlockButton_Click()
    If ADMINUnlock.Value = "PASSWORD" Then
        
        Worksheets("ADMIN").Unprotect Password:="PASSWORD2"
        Me.Hide
        Sheets("ADMIN").Select
        ActiveSheet.Shapes("Rectangle 3").Visible = False
        Range("B2").Select
                
    Else
        MsgBox "You do not have Admin Unlock permission"
        Worksheets("Sheet 1").Activate
        Range("A10").Select


    End If
    
    Me.Hide
    Application.ScreenUpdating = True
        
End Sub


Private Sub UserForm_Initialize()
With Me
.StartUpPosition = 0
.Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
.Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
End With
End Sub