VBA to shows sheets if condition met

jmazorra

Well-known Member
Joined
Mar 19, 2011
Messages
715
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.

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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Try this code, just add in other sheets if needed,
Code:
If Sheets("Dashboard").Range("B2") <> "oasis23" Then
Sheets("status report").Visible = False
Sheets("byposition").Visible = false
Sheets("byemployee").Visible = false
Else
Sheets("Status Report").Visible = True
Sheets("byposition").Visible = True
Sheets("byemployee").Visible = True
End If

case of the sheet names must match.
 
Upvote 0

Forum statistics

Threads
1,226,390
Messages
6,190,740
Members
453,615
Latest member
robbieb29

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top