Results 1 to 3 of 3

Thread: VBA to hide sheets based on dynamic cell values
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to hide sheets based on dynamic cell values

    Hi - relatively new to VBA and needassistance!
    I have a list of around 50 cell values that sum up columns in correspondingsheets in the same workbook, I would like to hide the particular sheet based onthe value of the cell.
    In the example below I would like to hide sheet 1 only, but should the valuedrop to below 0 in sheet 2 I would like to hide sheet 2 as well.

    Sheet name Sum of column A in sheet
    Sheet1 -1000
    Sheet2 500
    Sheet3 300
    Sheet4 600

    I hope this makes sense! Tks













  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,890
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA to hide sheets based on dynamic cell values

    Try this
    - code below loop all sheets in workbook, finds sheet name in column A and uses value in column C to determine if visible \ hidden
    - On Error Resume Next prevents code failing if sheet name is not in list etc

    Sheet Name = "Index"
    Column A : List of Sheet Names
    Column B : values associated with sheet
    Column C : Different formula in each cell returning True\False to tell VBA if sheet should be visible\hidden

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    1
    Sheet Name Value Visible ? Formula in column C
    2
    Sheet1
    1000
    FALSE
    =B2>1000
    3
    Sheet2
    500
    TRUE
    =B3>=0
    4
    Sheet3
    300
    FALSE
    =B4<200
    5
    Sheet4
    600
    TRUE
    =B5<=1000
    6
    Sheet: Index

    Amend to match your requirements
    Code:
    Sub HideSheetsBasedOnRules()
        Dim ws As Worksheet, rng As Range
        With Sheets("Index")
            Set rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
        End With
            
        On Error Resume Next
        For Each ws In ThisWorkbook.Sheets
            ws.Visible = rng.Find(ws.Name).Offset(, 2).Value
        Next
    End Sub

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,208
    Post Thanks / Like
    Mentioned
    68 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA to hide sheets based on dynamic cell values

    I guess the names start in cell A2.
    Run this macro on the sheet where you have the sheet-sum relationship


    Code:
    Sub test()
        Dim c As Range
        On Error Resume Next
        For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
            If c.Offset(0, 1) < 0 Then Sheets(c.Value).Visible = 0
        Next
    End Sub
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •