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]

Just change this line of code :

Private Sub Worksheet_Calculate()

to this

Sub Hide-UnhideRows

. Put the macro in a standard module and run it manually from there.