Hello everyone:
The code below opens all of the sheets listed. However. I would like to add to this code that if in sheet "Dashboard" cell B2 does not equal "oasis23", then sheets "status report", "byemployee", "byposition" must remain hidden.
The code below opens all of the sheets listed. However. I would like to add to this code that if in sheet "Dashboard" cell B2 does not equal "oasis23", then sheets "status report", "byemployee", "byposition" must remain hidden.
Code:
Private Sub cmdOK_Click()
Dim a, u, p, w(), i As Long, db As Worksheet, Flg As Boolean
Dim j As Long, x, y, c As Long, rSource As String
Set db = Sheets("DashBoard"): Flg = False: c = 0: x = 0
With db
a = .Range("a1").CurrentRegion
End With
u = UCase(Me.tbUN): p = Me.tbPW: Flg = False
With Application
x = .Match(u, .Index(a, 0, 1), 0)
End With
If Not IsError(x) Then
If Application.Index(a, x, 2) = p Then Flg = True
If Flg Then
ReDim w(1 To UBound(a, 2) - 2)
For j = 3 To UBound(a, 2)
If UCase(a(x, j)) = "A" Then c = c + 1: w(c) = a(1, j)
Next
Else
MsgBox "Incorrect Password", vbCritical + vbOKOnly
Exit Sub
End If
Else
MsgBox "Incorrect User Name", vbCritical + vbOKOnly
Exit Sub
End If
For i = 1 To Sheets.Count
If Sheets(i).Name <> "Login" Then
If IsError(Application.Match(Sheets(i).Name, w, 0)) Then
On Error Resume Next
Sheets(i).Visible = xlVeryHidden
Else
Sheets(i).Visible = xlSheetVisible
End If
End If
Next
Sheets("Summary Worksheet").Visible = xlSheetVisible
Sheets("All Employee Annualized").Visible = xlSheetVisible
Sheets("All Employee Salary").Visible = xlSheetVisible
Sheets("All Employee Hourly").Visible = xlSheetVisible
Sheets("All Position Annualized").Visible = xlSheetVisible
Sheets("All Position Salary").Visible = xlSheetVisible
Sheets("All Position Hourly").Visible = xlSheetVisible
Sheets("Status Report").Visible = xlSheetVisible
Sheets("byposition").Visible = xlSheetVisible
Sheets("byemployee").Visible = xlSheetVisible
Sheets("DashBoard").Visible = xlSheetVeryHidden
Sheets("Login").Visible = xlSheetVisible
On Error GoTo 0
With db.Range("aa1")
.Resize(100).Clear
.Value = "Sheet Names"
.Offset(1).Resize(c) = Application.Transpose(w)
End With
Unload Me
Sheets("Summary Worksheet").Activate
End Sub