Hide sheets depending on cell values

J

Jmoz092

Board Regular
Joined
Sep 8, 2017
Messages
184
Office Version
  1. 365
  2. 2011
Platform
  1. Windows
  2. MacOS
Hello,

I'm having problems getting a macro to run from a button in one of my workbooks.

Code: 
Sub HideAllSheets()
Application.ScreenUpdating = False


'hide all sheets with no data
Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Sheets
        If ws.Name Like "Data #########" And WorksheetFunction.CountA( _
        Range("c4")) < 1 Then
        ws.Visible = xlSheetHidden
        End If
    Next ws
    
    For Each ws In ActiveWorkbook.Sheets
     If ws.Name Like "Invoice #########" And Range("D45").Value = "$0.00" Then
      ws.Visible = xlSheetHidden
      End If
    Next ws
    
Application.ScreenUpdating = True


End Sub

The sheet names are dynamic, depending on which workbook the user is using within this project. Each Data sheet has a paired Invoice sheet. There are 20 or 30 of each, depending which type of workbook the user is using within this project.

On each "Data #########" worksheet, a person's name will appear in cell c4 if an event has occurred. Accordingly, costs incurred during the event will be calculated in cell D45 of the corresponding invoice sheet.

I'm trying to give the user a button to hide all Data and Invoice sheets in the workbook at the end of the month that have not been used. With the code as is, nothing happens when I run the macro, from the button or the macro window. Any help would be greatly appreciated. Thanks
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
So I got this to work by writing it out "longhand". My loop writing skills are still woefully inadequate. How could this code be expressed in a loop?

Code: 
[COLOR=#454545][FONT=&quot]Sub HideAllDataSheets() [/FONT][/COLOR][COLOR=#454545][FONT=&quot]
[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]Dim ws As Worksheet[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]
[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Application.ScreenUpdating = False[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]
[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    'hide all DataSheets with no data[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheet2.Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    If IsEmpty(Range("c4").Value) = True Then[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        Sheet2.Visible = xlSheetHidden[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    End If[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheet4.Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    If IsEmpty(Range("c4").Value) = True Then[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        Sheet4.Visible = xlSheetHidden[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    End If[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheet6.Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    If IsEmpty(Range("c4").Value) = True Then[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        Sheet6.Visible = xlSheetHidden[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    End If[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheet8.Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    If IsEmpty(Range("c4").Value) = True Then[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        Sheet8.Visible = xlSheetHidden[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    End If[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheet10.Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    If IsEmpty(Range("c4").Value) = True Then[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        Sheet10.Visible = xlSheetHidden[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    End If[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheet12.Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    If IsEmpty(Range("c4").Value) = True Then[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        Sheet12.Visible = xlSheetHidden[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    End If[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheet14.Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    If IsEmpty(Range("c4").Value) = True Then[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        Sheet14.Visible = xlSheetHidden[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    End If[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheet16.Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    If IsEmpty(Range("c4").Value) = True Then[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        Sheet16.Visible = xlSheetHidden[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    End If[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheet18.Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    If IsEmpty(Range("c4").Value) = True Then[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        Sheet18.Visible = xlSheetHidden[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    End If[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheet20.Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    If IsEmpty(Range("c4").Value) = True Then[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        Sheet20.Visible = xlSheetHidden[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    End If[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheet22.Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    If IsEmpty(Range("c4").Value) = True Then[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        Sheet22.Visible = xlSheetHidden[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    End If[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheet24.Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    If IsEmpty(Range("c4").Value) = True Then[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        Sheet24.Visible = xlSheetHidden[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    End If[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheet26.Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    If IsEmpty(Range("c4").Value) = True Then[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        Sheet26.Visible = xlSheetHidden[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    End If[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheet28.Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    If IsEmpty(Range("c4").Value) = True Then[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        Sheet28.Visible = xlSheetHidden[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    End If[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheet30.Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    If IsEmpty(Range("c4").Value) = True Then[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        Sheet30.Visible = xlSheetHidden[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    End If[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheet32.Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    If IsEmpty(Range("c4").Value) = True Then[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        Sheet32.Visible = xlSheetHidden[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    End If[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheet34.Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    If IsEmpty(Range("c4").Value) = True Then[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        Sheet34.Visible = xlSheetHidden[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    End If[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheet36.Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    If IsEmpty(Range("c4").Value) = True Then[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        Sheet36.Visible = xlSheetHidden[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    End If[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheet38.Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    If IsEmpty(Range("c4").Value) = True Then[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        Sheet38.Visible = xlSheetHidden[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    End If[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheet40.Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    If IsEmpty(Range("c4").Value) = True Then[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        Sheet40.Visible = xlSheetHidden[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    End If[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Application.ScreenUpdating = True[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]
[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]End Sub[/FONT][/COLOR]

I hid the Invoice ######### sheets at workbook open and unhid them with a worksheet change sub on each data ######### sheet, so that solved the hide Invoice ######### problem.

Thanks for the education.
 
Upvote 0
One option
Code: 
Sub HideSheets()
   Dim Ws As Worksheet
   
   For Each Ws In Sheets(Array(Sheet2.Name, Sheet4.Name, Sheet6.Name))
      Ws.Visible = IIf(IsEmpty(Ws.Range("C4").Value), 0, -1)
   Next Ws
End Sub
Just add the rest of the sheets to the array
 
Upvote 0
Another option if you want to confine the code to "even-numbered" Sheets (Sheet2, Sheet4,...) as in your post #2 :
Code: 
Sub HideAllDataSheets()
Dim ws As Worksheet, Num As Long
Application.ScreenUpdating = False
For Each ws In Worksheets
    If ws.Name Like "Sheet*" Then Num = Right(ws.Name, Len(ws.Name) - 5)
    If Num Mod 2 = 0 Then
        If IsEmpty(ws.Range("c4")) Then ws.Visible = xlSheetHidden
    End If
Next ws
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

KlausW
  • Solved
hides all sheets except those standing in
Replies
3
Views
232
KlausW
KlausW
A
  • Solved
For each sheet in workbook not doing anything in the last sheet
Replies
4
Views
119
jolivanes
jolivanes
A
  • Question Question
Rename sheets by cell value in date format
Replies
1
Views
149
Kaper
Kaper
E
  • Solved
Quick VBA code to Hide Rows based on Cell Value
Replies
3
Views
531
Joe4
Joe4
I
  • Question Question
Hide command button on worksheet depending on worksheet cell value
Replies
2
Views
274
ipbr21054
I

Forum statistics

Threads
1,223,981
Messages
6,175,771
Members
452,668
Latest member
mrider123

Share this page

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