add password when try show sheet otherwise should be hidden

[Ali M]

Hi

I want when I select sheet from combobox on userform show me inpubox to write password if it is right then will show sheet if it's wrong password then should keep hidden .

note: if I select another sheet then should hide the previous sheet , just show the selected sheet if it's right .

Private Sub CommandButton2_Click()
  On Error GoTo ErrHandler
If ComboBox1.Text = "" Then
        MsgBox "select sheet first"
    Else
        For ii = 1 To Sheets.Count
            Sheets(ii).Visible = xlSheetVisible
        Next
            For i = 1 To Sheets.Count
                If Not ComboBox1.Text = Sheets(i).Name Then
                    Sheets(i).Visible = xlSheetVeryHidden
                End If

            Next
            i = i + 1
    End If
Exit Sub
ErrHandler:
   MsgBox "name is wrong"
    Resume Next
End Sub


Private Sub UserForm_Activate()
    For Each i In Worksheets
        ComboBox1.AddItem (i.Name)
    Next
    i = i + 1
    For i = 1 To Sheets.Count
        Sheets(i).Visible = xlSheetVisible
    Next
End Sub

thanks

 

[breynolds0431]

Hello. See if this will work for you.

Private Sub CommandButton2_Click()

If ComboBox1.Text = "" Then
        MsgBox "select sheet first"
        Exit Sub
End If

'Requires password to process code's loop
Dim resp As String
resp = InputBox("Enter password.", "Verification Needed")

If Not resp = "password" Then '<change this to actual password
    MsgBox "That is not correct.", vbCritical, "Invalid Password"
    Exit Sub
End If

'code will continue if password was accepted
On Error GoTo ErrHandler

For ii = 1 To Sheets.Count
    Sheets(ii).Visible = xlSheetVisible
Next
    For i = 1 To Sheets.Count
        If Not ComboBox1.Text = Sheets(i).Name Then
            Sheets(i).Visible = xlSheetVeryHidden
        End If

    Next
    i = i + 1

Exit Sub
ErrHandler:
   MsgBox "name is wrong"
    Resume Next
End Sub

 

[Ali M]

wow ! this is awesome.
just curosity if you don't mind can I select ALL from combobox then should be differnt password and when the password is right show all of sheets and if it's wrong shouldn't happen .
thanks again

 

[breynolds0431]

wow ! this is awesome.
just curosity if you don't mind can I select ALL from combobox then should be differnt password and when the password is right show all of sheets and if it's wrong shouldn't happen .
thanks again

There's no multiselect option on a combobox. You can use a listbox instead with a checkbox that will automatically select all the listbox items.

It would be easy enough to add a new listbox to your userform and then use find and replace in your code to replace all occurances of ComboBox1 with ListBox1. Then add the "Select All Sheets" checkbox above that new ListBox.

Private Sub UserForm_Activate()

    For Each i In Worksheets
        ListBox1.AddItem (i.Name)
    Next
    i = i + 1
    For i = 1 To Sheets.Count
        Sheets(i).Visible = xlSheetVisible
    Next
End Sub

Private Sub UserForm_Initialize()
    With Me.ListBox1
        .MultiSelect = fmMultiSelectSingle
        .Locked = False
    End With
End Sub

Private Sub CheckBox1_Click()

'If checked, all items in listbox will be selected

Dim N As Single
If CheckBox1.Value = True Then
    ListBox1.MultiSelect = fmMultiSelectMulti
    For N = 0 To ListBox1.ListCount - 1
        ListBox1.Selected(N) = True
    Next N
    ListBox1.Locked = True
Else
    ListBox1.MultiSelect = fmMultiSelectSingle
    For N = 0 To ListBox1.ListCount - 1
        ListBox1.Selected(N) = False
    Next N
    ListBox1.Locked = False
End If

End Sub

Private Sub CommandButton2_Click()

Dim resp As String
Dim ws As Worksheet

'Will ensure a sheet is selected in listbox1

For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) = True Then
        shtCount = shtCount + 1
    End If
Next i

If shtCount = 0 Then
        MsgBox "select sheet first"
        Exit Sub
End If

'Checks if checkbox1 is true, which will mean all sheets are selected
If Me.CheckBox1.Value = True Then
'all sheets are selected
    resp = InputBox("Enter password to show all sheets", "Verification Needed")
    If Not resp = "password" Then '<change password to actual password
        MsgBox "That is not correct.", vbCritical, "Invalid Password"
    Else
        For Each ws In ThisWorkbook.Worksheets
            ws.Visible = True
        Next ws
    End If
Else
'single sheet is selected

    'Requires password to process code's loop
    resp = InputBox("Enter password.", "Verification Needed")
    
    If Not resp = "password" Then '<change this to actual password
        MsgBox "That is not correct.", vbCritical, "Invalid Password"
        Exit Sub
    End If
    
    'code will continue if password was accepted
    On Error GoTo ErrHandler
    
    For ii = 1 To Sheets.Count
        Sheets(ii).Visible = xlSheetVisible
    Next
        For i = 1 To Sheets.Count
            If Not ListBox1.Text = Sheets(i).Name Then
                Sheets(i).Visible = xlSheetVeryHidden
            End If
    
        Next
        i = i + 1
End If

Exit Sub
ErrHandler:
   MsgBox "name is wrong"
    Resume Next
End Sub

 

[Ali M]

why should use multiselect option ?
is not possible select all from combobox and loop through all of sheets and show?

 

[breynolds0431]

Without multi-select, you would not be able to select more than one item from a combobox.

You could avoid replacing the combobox with a listbox. With this option, you'd still need to add a checkbox for Select All Sheets. When the checkbox = True, then the combobox will be disabled and comandbutton2 will identify if combobox1 is True and ask for the show all sheets password.

Private Sub UserForm_Activate()

    For Each i In Worksheets
        ComboBox1.AddItem (i.Name)
    Next
    i = i + 1
    For i = 1 To Sheets.Count
        Sheets(i).Visible = xlSheetVisible
    Next
End Sub

Private Sub CheckBox1_Click()

'If checked, all items in listbox will be selected

Dim N As Single
If CheckBox1.Value = True Then
    ComboBox1.Enabled = False
Else
    ComboBox1.Enabled = True
End If

End Sub

Private Sub CommandButton2_Click()

Dim resp As String
Dim ws As Worksheet

'Will ensure a sheet is selected in ComboBox1
If ComboBox1.Value = "" Then
        MsgBox "select sheet first"
        Exit Sub
End If

'Checks if checkbox1 is true, which will mean all sheets are selected
If Me.CheckBox1.Value = True Then
'all sheets are selected
    resp = InputBox("Enter password to show all sheets", "Verification Needed")
    If Not resp = "password" Then '<change password to actual password
        MsgBox "That is not correct.", vbCritical, "Invalid Password"
    Else
        For Each ws In ThisWorkbook.Worksheets
            ws.Visible = True
        Next ws
    End If
Else
'single sheet is selected

    'Requires password to process code's loop
    resp = InputBox("Enter password.", "Verification Needed")
    
    If Not resp = "password" Then '<change this to actual password
        MsgBox "That is not correct.", vbCritical, "Invalid Password"
        Exit Sub
    End If
    
    'code will continue if password was accepted
    On Error GoTo ErrHandler
    
    For ii = 1 To Sheets.Count
        Sheets(ii).Visible = xlSheetVisible
    Next
        For i = 1 To Sheets.Count
            If Not ComboBox1.Text = Sheets(i).Name Then
                Sheets(i).Visible = xlSheetVeryHidden
            End If
    
        Next
        i = i + 1
End If

Exit Sub
ErrHandler:
   MsgBox "name is wrong"
    Resume Next
End Sub

 

[Ali M]

thanks again !

Without multi-select, you would not be able to select more than one item from a combobox

I'm not sure if I understand clearly, but when select all from combobox ,then just show . I don't need select more than item , based on OP when select sheet and password is right just show the select sheet , if select another will hide previous and if I select all then should show all of sheets, that's it .