Home

How to hide a number of worksheets using VBA

%3CLINGO-SUB%20id%3D%22lingo-sub-730031%22%20slang%3D%22en-US%22%3EHow%20to%20hide%20a%20number%20of%20worksheets%20using%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-730031%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20workbook%20containing%20a%20large%20number%20of%20worksheets.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20worksheet%20is%20a%20list%20of%20all%20employees%20(Past%20and%20Present)%20this%20worksheet%20is%20defined%20under%20Name%20Manager%20as%20ActiveWorkers%20(%3D'Labour%20Total'!%24A%3A%24J).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20button%20on%20this%20worksheet%20that%20should%20hide%20all%20worksheets%20of%20employees%20that%20are%20no%20longer%20working.%20This%20is%20to%20minimise%20the%20number%20of%20tabs%20at%20the%20bottom%20of%20the%20active%20workbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20VBA%20I%20have%20used%20is%20detailed%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20HideNonWorkers()%3C%2FP%3E%3CP%3EDim%20c%20As%20Range%3C%2FP%3E%3CP%3EFor%20Each%20c%20In%20Range(%22ActiveWorkers%22)%3CBR%20%2F%3EIf%20(c.Offset(0%2C%208)%20%3D%20%22NO%22)%20Then%3CBR%20%2F%3EActiveWorkbook.Sheets(c.Offset(0%2C%209)).Visible%20%3D%20False%3CBR%20%2F%3EElseIf%20(c.Offset(0%2C%208)%20%3D%20%22YES%22)%20Then%3CBR%20%2F%3EActiveWorkbook.Sheets(c.Offset(0%2C%209)).Visible%20%3D%20True%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ENext%3CBR%20%2F%3EOn%20Error%20GoTo%200%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20True%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20get%20an%20error%20message%20when%20th%20code%20runs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20diagnostics%20it%20tells%20me%20the%20the%20return%20values%20of%20the%20code%2C%20which%20appears%20correct.%20I%20do%20not%20know%20where%20the%20error%20is.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20code%20looks%20at%20the%20active%20worksheet%20and%20evaluates%20the%20value%20of%20column%20I%20of%20the%20worksheet.%20If%20the%20value%20is%20%22NO%22%20then%20the%20code%20gets%20the%20worksheet%20name%20from%20column%20J%20and%20is%20supposed%20to%20set%20the%20named%20worksheet%20to%20not%20visible.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20help%20please.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20Regards%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVonryan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-730031%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-750266%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20hide%20a%20number%20of%20worksheets%20using%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-750266%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F323150%22%20target%3D%22_blank%22%3E%40vonryan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20main%20problem%20was%20not%20using%20the%20.Value%20property%20of%20c.Offset(0%2C9)%20when%20trying%20to%20set%20the%20visibility%20property%20of%20the%20worksheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBesides%20fixing%20that%2C%20I%20also%20revised%20the%20code%20so%20it%20would%20be%20case%20insensitive%20when%20testing%20for%20YES%20and%20NO%2C%20and%20to%20use%20the%20recommended%20Enum%20values%20xlSheetVisible%20and%20xlSheetHidden%20rather%20than%20True%20and%20False.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20HideNonWorkers()%3C%2FP%3E%3CP%3EDim%20c%20As%20Range%3C%2FP%3E%3CP%3EFor%20Each%20c%20In%20Range(%22ActiveWorkers%22)%3CBR%20%2F%3EIf%20UCase(c.Offset(0%2C%208).Value)%20%3D%20%22NO%22%20Then%3CBR%20%2F%3EActiveWorkbook.Sheets(c.Offset(0%2C%209).Value).Visible%20%3D%20xlSheetHidden%3CBR%20%2F%3EElseIf%20UCase(c.Offset(0%2C%208).Value)%20%3D%20%22YES%22%20Then%3CBR%20%2F%3EActiveWorkbook.Sheets(c.Offset(0%2C%209).Value).Visible%20%3D%20xlSheetVisible%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ENext%3CBR%20%2F%3EOn%20Error%20GoTo%200%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20True%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E
vonryan
Contributor

Dear All,

 

I have a workbook containing a large number of worksheets.

 

One worksheet is a list of all employees (Past and Present) this worksheet is defined under Name Manager as ActiveWorkers (='Labour Total'!$A:$J).

 

I have a button on this worksheet that should hide all worksheets of employees that are no longer working. This is to minimise the number of tabs at the bottom of the active workbook.

 

The VBA I have used is detailed below.

 

Sub HideNonWorkers()

Dim c As Range

For Each c In Range("ActiveWorkers")
If (c.Offset(0, 8) = "NO") Then
ActiveWorkbook.Sheets(c.Offset(0, 9)).Visible = False
ElseIf (c.Offset(0, 8) = "YES") Then
ActiveWorkbook.Sheets(c.Offset(0, 9)).Visible = True
End If
Next
On Error GoTo 0
Application.EnableEvents = True

End Sub

 

I get an error message when th code runs.

 

In diagnostics it tells me the the return values of the code, which appears correct. I do not know where the error is.

 

The code looks at the active worksheet and evaluates the value of column I of the worksheet. If the value is "NO" then the code gets the worksheet name from column J and is supposed to set the named worksheet to not visible.

 

Can anyone help please.

 

Best Regards

 

Vonryan

1 Reply

@vonryan 

Your main problem was not using the .Value property of c.Offset(0,9) when trying to set the visibility property of the worksheet.

 

Besides fixing that, I also revised the code so it would be case insensitive when testing for YES and NO, and to use the recommended Enum values xlSheetVisible and xlSheetHidden rather than True and False.

 

Sub HideNonWorkers()

Dim c As Range

For Each c In Range("ActiveWorkers")
If UCase(c.Offset(0, 8).Value) = "NO" Then
ActiveWorkbook.Sheets(c.Offset(0, 9).Value).Visible = xlSheetHidden
ElseIf UCase(c.Offset(0, 8).Value) = "YES" Then
ActiveWorkbook.Sheets(c.Offset(0, 9).Value).Visible = xlSheetVisible
End If
Next
On Error GoTo 0
Application.EnableEvents = True

End Sub

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies