SOLVED
Home

Excel Hide Sheets Based on A Cell Value

%3CLINGO-SUB%20id%3D%22lingo-sub-751395%22%20slang%3D%22en-US%22%3EExcel%20Hide%20Sheets%20Based%20on%20A%20Cell%20Value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-751395%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20create%20a%20workbook%20which%20hides%20inactive%20sheets%20based%20on%20a%20cell%20value%20on%20the%20front%20sheet.%20i.e%20if%20the%20cell%20value%20us%20%22yes%22%20it%20is%20visible%2C%20if%20it%20is%20%22no%22%20it%20is%20hidden.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3CBR%20%2F%3EIf%20%5BA9%5D%20%3D%20%22Yes%22%20Then%3CBR%20%2F%3ESheets(%22VAR%20001%22).Visible%20%3D%20True%3CBR%20%2F%3EElse%3CBR%20%2F%3ESheets(%22VAR%20001%22).Visible%20%3D%20False%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20this%20to%20happen%20for%20multiple%20sheets.%20I%20have%20used%20this%20macro%20which%20works%20on%20a%20single%20sheet%20but%20I%20would%20like%20the%20other%20sheets%20named%20VAR%20002%20up%20to%20VAR%20250%20to%20operate%20the%20same%20way%20but%20with%20each%20sheet%20referencing%20a%20different%20cell%20for%20Yes%2FNo.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVAR%20002%20need%20to%20reference%20cell%20A10%2C%20VAR%20003%20needs%20to%20reference%20cell%20A11%20and%20so%20on%20up%20to%20VAR250%20referencing%20call%20A258.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20please%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-751395%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-751425%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Hide%20Sheets%20Based%20on%20A%20Cell%20Value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-751425%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F375132%22%20target%3D%22_blank%22%3E%40AB_2019%3C%2FA%3E%26nbsp%3B%20If%20the%20sheet%20names%20are%20tied%20to%20the%20row%20numbers%2C%20you%20can%20use%20the%20code%20below.%20It%20will%20only%20run%20when%20a%20cell%20in%20the%20range%20A9%20to%20A258%20is%20changed.%20It%20also%20works%20if%20you%20copy%20and%20paste%20%22Yes%22%20or%20another%20value%20into%20more%20than%20one%20cell%20at%20a%20time.%20(Note%20that%20this%20is%20case%20sensitive%2C%20so%20%22yes%22%20will%20hide%20the%20sheet%20and%20only%20%22Yes%22%20will%20show%20it)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet%20me%20know%20how%20you%20get%20on%20with%20this.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%0ADim%20myRow%20As%20Integer%0ADim%20mySheetName%20As%20String%0ADim%20cel%20As%20Range%0A%0A'%20watch%20only%20cells%20A9%20to%20A258%0AIf%20Not%20Intersect(Target%2C%20Range(%22A9%3AA258%22))%20Is%20Nothing%20Then%0A%0A'%20if%20more%20than%20one%20cell%20has%20been%20changed%2C%20we%20need%20to%20cath%20them%20all%0A%20%20%20%20For%20Each%20cel%20In%20Target%0A%0A'%20get%20the%20row%20of%20the%20changed%20cell%0A'%20subtract%208%20to%20arrive%20at%20the%20sheet%20number%0A%20%20%20%20%20%20%20%20myRow%20%3D%20cel.Row%20-%208%0A%0A'%20build%20the%20sheet%20name%0A%20%20%20%20%20%20%20%20mySheetName%20%3D%20%22VAR%20%22%20%26amp%3B%20Format(myRow%2C%20%22000%22)%0A%0A%0A%20%20%20%20%20%20%20%20If%20cel%20%3D%20%22Yes%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20ThisWorkbook.Worksheets(mySheetName).Visible%20%3D%20True%0A%20%20%20%20%20%20%20%20Else%0A%20%20%20%20%20%20%20%20%20%20%20%20ThisWorkbook.Worksheets(mySheetName).Visible%20%3D%20False%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%20cel%0A%0AEnd%20If%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-751492%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Hide%20Sheets%20Based%20on%20A%20Cell%20Value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-751492%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3BThis%20works%20perfectly!%20Thank%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20last%20one%20you%20may%20be%20able%20to%20help%20me%20with.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20hide%20rows%20on%20a%20sheet%20based%20on%20a%20number%20in%20a%20cell%20(A6).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStarting%20again%20with%20row%209%20being%20the%20first%20row%2C%20so%20if%20number%201%20is%20in%20the%20A6%20cell%20then%20row%209%20is%20shown%2C%3C%2FP%3E%3CP%3Eif%20number%202%20is%20in%20A6%20then%20rows%209%20and%2010%20are%20shown.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EContinuing%20up%20to%20number%20250%20in%20A6%20showing%20all%20rows%20up%20to%20row%20258.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20rows%201-8%20and%20all%20rows%20after%20258%20always%20visible%20and%20not%20effected%20but%20this%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20not%20completed%20a%20function%20like%20this%20before%20and%20am%20not%20having%20much%20luck%20on%20Google.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-752114%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Hide%20Sheets%20Based%20on%20A%20Cell%20Value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-752114%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F375132%22%20target%3D%22_blank%22%3E%40AB_2019%3C%2FA%3E%26nbsp%3B%20Glad%20this%20worked%20for%20you.%20It%20would%20be%20great%20if%20you%20could%20mark%20my%20answer%20as%20the%20solution.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHiding%20rows%20is%20a%20different%20question.%20I'll%20put%20that%20into%20a%20new%20question%20for%20you%2C%20so%20it%20gets%20more%20attention.%3C%2FP%3E%3C%2FLINGO-BODY%3E
AB_2019
Occasional Contributor

Hi,

 

I am trying to create a workbook which hides inactive sheets based on a cell value on the front sheet. i.e if the cell value us "yes" it is visible, if it is "no" it is hidden.

 

Private Sub Worksheet_Change(ByVal Target As Range)
If [A9] = "Yes" Then
Sheets("VAR 001").Visible = True
Else
Sheets("VAR 001").Visible = False
End If
End Sub

 

I need this to happen for multiple sheets. I have used this macro which works on a single sheet but I would like the other sheets named VAR 002 up to VAR 250 to operate the same way but with each sheet referencing a different cell for Yes/No.

 

VAR 002 need to reference cell A10, VAR 003 needs to reference cell A11 and so on up to VAR250 referencing call A258.

 

Can you please help.

 

Thank you!

 

2 Replies
Solution

@AB_2019  If the sheet names are tied to the row numbers, you can use the code below. It will only run when a cell in the range A9 to A258 is changed. It also works if you copy and paste "Yes" or another value into more than one cell at a time. (Note that this is case sensitive, so "yes" will hide the sheet and only "Yes" will show it)

 

Let me know how you get on with this.

 

 

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRow As Integer
Dim mySheetName As String
Dim cel As Range

' watch only cells A9 to A258
If Not Intersect(Target, Range("A9:A258")) Is Nothing Then

' if more than one cell has been changed, we need to cath them all
    For Each cel In Target

' get the row of the changed cell
' subtract 8 to arrive at the sheet number
        myRow = cel.Row - 8

' build the sheet name
        mySheetName = "VAR " & Format(myRow, "000")


        If cel = "Yes" Then
            ThisWorkbook.Worksheets(mySheetName).Visible = True
        Else
            ThisWorkbook.Worksheets(mySheetName).Visible = False
        End If
    Next cel

End If

 

 

@AB_2019  Glad this worked for you. It would be great if you could mark my answer as the solution.

 

Hiding rows is a different question. I'll put that into a new question for you, so it gets more attention.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies