VBA: Use Cell Value as Sheet reference to Hide/Unhide Sheets based on Cell Value

[rickytb]

Hi there,

Hopefully the title is correctly describing what I need, but here I will elaborate.
I will for example have the following worksheets:
- Overview
- Adam
- John
- Nick
- Tess

On the Overview sheet you will find the total of expenses per employee. The name in column A and the value in column B. Example

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Adam[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]John[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Nick[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Tess[/TD]
[TD]75[/TD]
[/TR]
</tbody>[/TABLE]

What I want to do is the following. If the value in column B is 0, I want to hide the sheet with the name in column A. Example: If B3 is 0, hide sheet John. However, in the VBA I want to have A3 instead of John since the value can change.

The following would work, but the names change often so I would need a reference instead of hard copying the names.

------- VBA Example -------

Private Sub Worksheet_Change(ByVal Target As Range)

If [B2] = "0" Then
Sheets("Adam").Visible = False
Else
Sheets("Adam").Visible = True
End If


If [B3] = "0" Then
Sheets("John").Visible = False
Else
Sheets("John").Visible = True
End If

If [B4] = "0" Then
Sheets("Nick").Visible = False
Else
Sheets("Nick").Visible = True
End If




If [B5] = "0" Then
Sheets("Tess").Visible = False
Else
Sheets("Tess").Visible = True
End If



End Sub

------- End VBA Example -------

How can I replace Adam with A2, John with A3, etc.?

Thanks in advance for your support and let me know if you need further clarification.

Regards,
Ricky

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Change a name in column A and exit the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    If Target.Offset(0, 1) = "0" Then
        Sheets(Target.Value).Visible = False
    Else
        Sheets(Target.Value).Visible = True
    End If
    Application.ScreenUpdating = True
End Sub

 

[mumps]

You could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data. By the way, I tried sending you a private message but I don't think it went through. Possibly, your Inbox is full and you'll have to clean it out to allow you to receive PM's.

 

[rickytb]

I indeed received a message that my inbox is full. A bit strange since I just registered, but okay. I have uploaded the document in the following folder, but removed most content leaving just the required parts: https://www.dropbox.com/sh/fv8n32zj6lr5v7g/AAAe13f1NUPsLrCSMzbz_cdNa?dl=0

The logic is to be built on the sheet Overview: It should start from row 9 downwards and will be many rows in the real file. Per person, there will be 2 tabs to be hidden, which might complicate things. It needs to be something like this.

If F9 = 0, then hide worksheet with name A9 and A9&" 2" (meaning sheet "Adam" and sheet "Adam 2" in this example).
If F9 is not 0, then show worksheet with name A9 and A9&" 2".

If F10 = 0, then hide worksheet with name A10 and A10&" 2".
If F10 is not 0, then show worksheet with name A10 and A10&" 2".

Etc. Etc.

Thanks a lot for your support.

Regards,
Ricky

 

[mumps]

Try this macro. The sheets will be automatically hidden every time the formula in column F returns a zero and visible when the formula returns a value greater than zero.

Private Sub Worksheet_Calculate()
    Application.ScreenUpdating = False
    Dim bottomF As Long
    bottomF = Range("F" & Rows.Count).End(xlUp).Row
    Dim TI As Range
    For Each TI In Range("F9:F" & bottomF)
        If Not IsEmpty(TI) And TI = "0" Then
            Sheets(Range("A" & TI.Row).Value).Visible = False
            Sheets(Range("A" & TI.Row).Value & " 2").Visible = False
        ElseIf Not IsEmpty(TI) And TI > "0" Then
            Sheets(Range("A" & TI.Row).Value).Visible = True
            Sheets(Range("A" & TI.Row).Value & " 2").Visible = True
        End If
    Next TI
    Application.ScreenUpdating = True
End Sub

 

[rickytb]

Thanks again for your support.

It is working in the sense that the tabs get hidden if I change the value to 0, but I get the following error.
Run-time error '9': Subscript out of range

Any clue how I can get rid of this error?

Cheers,
Ricky

 

[mumps]

The macro is Worksheet_Calculate event which means that the macro is triggered any time there is a change in a cell as the result of a formula so you don't have to manually change any value in column F. When I tried the macro on the file you uploaded, it ran without any errors. Which line of code is highlighted when you get the error and click "Debug"?

 

[rickytb]

Can it be because the actual file has around 150 worksheets? The following line of code was highlighted (see in red).
Please note that after restarting the file, I do not get the error. Not sure if it will show up again later. Would it be better to manually trigger the update or can I leave it automatically like it is now?

Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
Dim bottomF As Long
bottomF = Range("F" & Rows.Count).End(xlUp).Row
Dim TI As Range
For Each TI In Range("F9:F" & bottomF)
If Not IsEmpty(TI) And TI = "0" Then
Sheets(Range("A" & TI.Row).Value).Visible = False
Sheets(Range("A" & TI.Row).Value & " 2").Visible = False
ElseIf Not IsEmpty(TI) And TI > "0" Then
Sheets(Range("A" & TI.Row).Value).Visible = True
Sheets(Range("A" & TI.Row).Value & " 2").Visible = True
End If
Next TI
Application.ScreenUpdating = True
End Sub

 

[mumps]

As long as it's working, just leave it as it is. The number of sheets is not an issue. By the way, if the sheet that the macro is trying to make visible or not visible doesn't exist, it will generate the error. Also, please use code tags when you post code.

 

[rickytb]

Hi Mumps,

Me again... After working with the file for some time, I was wondering if it is still possible to trigger this manually rather than automatically after every change that I do.
How would this work?

Thanks!

Regards,
Ricky