Worksheet = Visible Query

[SwiftM]

Hi all,

I have 3 different worksheets named "marswi", "andrei" and "ryawee".

These names are also used in a combo drop down box for a username and password login. These are all named as one group called "ZoneName". When the user types in their username and password then depending on which user is logging in, the relevant sheet is made visible. This part I have worked out.

What I am struggling with though is how to make another sheet visible at the same time..... I'll try to explain...

I have 3 other worksheets with similar names to the other sheets called "marswiLog", "andreiLog" and "ryaweeLog" - the only difference is having the word "Log" on the end. When the user types in their username and password to login, then I would like both the relevant sheets to be made visible.

E.g. marswi logs in > marswi AND marswiLog worksheets are made visible.

The problem I am having is that I have made the vba link to the Zone_List_ComboBox.Value to open up the relevant named sheet ("marswi" etc), but I do not have or require the value "marswiLog" in my combo drop down.

Really hope this makes sense, hard to explain!

Any help would be much appreciated!! Thank you so much!

This is my code so far:

Private Sub Login_CommandButton_Click()
If Zone_List_ComboBox.Value = "" Then
MsgBox "Zone Cannot be Blank!!!", vbInformation, "Zone Name"
Exit Sub
End If
If Password_TB.Value = "" Then
MsgBox "Password Cannot be Blank!!!", vbInformation, "Password"
Exit Sub
End If
If Zone_List_ComboBox.Value = "Admin" And Password_TB.Value = "Admin" Then
Unload Me
Dim Ws As Worksheet
For Each Ws In ActiveWorkbook.Worksheets
Ws.Visible = xlSheetVisible
Next Ws
Sheets("Admin").Select
Else
Dim ZoneName As String
Dim Password As Variant
ZoneName = Zone_List_ComboBox.Value
Password = Application.WorksheetFunction.VLookup(ZoneName, Sheets("Admin").Range("A:B"), 2, 0)
If Password <> Password_TB.Value Then
MsgBox "Password is not matching", vbInformation, "Wrong Password"
Exit Sub
End If

If Password = Password_TB.Value Then
Unload Me
Sheets(ZoneName).Visible = True
Sheets(ZoneName).Select
ActiveSheet.Range("A1").Select
End If
End If

End Sub

 

[HaHoBe]

Hi SwiftM,

maybe like this

Private Sub Login_CommandButton_Click()
'https://www.mrexcel.com/board/threads/worksheet-visible-query.1219448/
Dim Ws As Worksheet
Dim strZoneName As String
Dim strPW As Variant

If Zone_List_ComboBox.Value = "" Then
  MsgBox "Zone Cannot be Blank!!!", vbInformation, "Zone Name"
  Exit Sub
End If

If Password_TB.Value = "" Then
  MsgBox "Password Cannot be Blank!!!", vbInformation, "Password"
  Exit Sub
End If

If Zone_List_ComboBox.Value = "Admin" And Password_TB.Value = "Admin" Then
  Unload Me
  For Each Ws In ActiveWorkbook.Worksheets
    Ws.Visible = xlSheetVisible
  Next Ws
  Sheets("Admin").Select
Else
  strZoneName = Zone_List_ComboBox.Value
  strPW = Application.WorksheetFunction.VLookup(strZoneName, Sheets("Admin").Range("A:B"), 2, 0)
  If strPW <> Password_TB.Value Then
    MsgBox "Password is not matching", vbInformation, "Wrong Password"
    Exit Sub
  End If
  If strPW = Password_TB.Value Then
    Unload Me
    Sheets(strZoneName).Visible = True
    Sheets(strZoneName & "Log").Visible = True
    Application.Goto Sheets(strZoneName).Range("A1"), True
  End If
End If

End Sub

Ciao,
Holger

 

[SwiftM]

Hi SwiftM,

maybe like this

Private Sub Login_CommandButton_Click()
'https://www.mrexcel.com/board/threads/worksheet-visible-query.1219448/
Dim Ws As Worksheet
Dim strZoneName As String
Dim strPW As Variant

If Zone_List_ComboBox.Value = "" Then
  MsgBox "Zone Cannot be Blank!!!", vbInformation, "Zone Name"
  Exit Sub
End If

If Password_TB.Value = "" Then
  MsgBox "Password Cannot be Blank!!!", vbInformation, "Password"
  Exit Sub
End If

If Zone_List_ComboBox.Value = "Admin" And Password_TB.Value = "Admin" Then
  Unload Me
  For Each Ws In ActiveWorkbook.Worksheets
    Ws.Visible = xlSheetVisible
  Next Ws
  Sheets("Admin").Select
Else
  strZoneName = Zone_List_ComboBox.Value
  strPW = Application.WorksheetFunction.VLookup(strZoneName, Sheets("Admin").Range("A:B"), 2, 0)
  If strPW <> Password_TB.Value Then
    MsgBox "Password is not matching", vbInformation, "Wrong Password"
    Exit Sub
  End If
  If strPW = Password_TB.Value Then
    Unload Me
    Sheets(strZoneName).Visible = True
    Sheets(strZoneName & "Log").Visible = True
    Application.Goto Sheets(strZoneName).Range("A1"), True
  End If
End If

End Sub

Ciao,
Holger

Hi Holger,

This is brilliant! Just what I was after

Thank you for taking the time to help me.

Kindest regards,
M

 

[HaHoBe]

Hi,

glad we could help on this one. Thanks for the feedback.

Holger