Password protecting part of tabstrip

I have a userform using a tabstrip control.  On of the tabs is a management tab and should only be accessed by certain people...preferrably password protected.  Users do not have to login to the sheet otherwise and that's not an option.  What I'd like to do is when a user attempts to access that tab, a prompt pops up asking for a password.  If correct, the tab is show but if not, the user gets an error.

Any help is appreciated,
Who is Participating?
danaseamanConnect With a Mentor Commented:
This snippet is for Vb6 but should be similar to VBA.
Controls for each Tab are placed in a Frame.
For Password protected Tab, user must input corrrect Password for Frame to become visible.

Option Explicit

Private SelectedTab As Integer

Private Sub Form_Load()
Dim i As Integer

    ' Move all the frames to the same position
    ' and make them all invisible.
    For i = 1 To ChoiceFrame.UBound
        ChoiceFrame(i).Move _
            ChoiceFrame(0).Left, _
            ChoiceFrame(0).Top, _
            ChoiceFrame(0).Width, _
        ChoiceFrame(i).Visible = False
    Next i
    ' Select the first tab.
    SelectedTab = 1
    TabStrip1.SelectedItem = TabStrip1.Tabs(SelectedTab)
    ChoiceFrame(SelectedTab - 1).Visible = True
End Sub

Private Sub TabStrip1_Click()
    Dim sPass  As String
    Dim bAllow As Boolean
    ChoiceFrame(SelectedTab - 1).Visible = False
    SelectedTab = TabStrip1.SelectedItem.Index
    If SelectedTab = 3 Then
       sPass = InputBox("Input Passwqord", "Password Required")
       If sPass = "MyPassword" Then
          ChoiceFrame(SelectedTab - 1).Visible = True
          MsgBox "Incorrect password"
       End If
       ChoiceFrame(SelectedTab - 1).Visible = True
    End If
End Sub

Open in new window

Saurabh Singh TeotiaCommented:
You can use something like this, but again this will ask for the password when user selects that tab and its shown, however if the password dont match it selects sheet2 and this code goes in the sheet module of that sheet for which you want to ask for password, The password i saved in this case was abc.

Private Sub Worksheet_Activate()
    Application.ScreenUpdating = False

    Dim pswrd As String

    pswrd = Application.InputBox("Please enter password to view the sheet", "Enter Password", Type:=2)

    If pswrd <> "abc" Then

        MsgBox "Incorrect Password Entered"
    End If

    Application.ScreenUpdating = True
End Sub

Open in new window

chris_thornAuthor Commented:
Thanks for the feedback...the answer you posted seems like you are attempting to protect an individual sheet.  I'm trying to protect a tab on a tabstrip placed on a user form.

Any thoughts?

Saurabh Singh TeotiaCommented:
Ahh i see what you mean, Can you post your sample workbook so that i can have a look.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.