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

	A	B
1	Name	Value
2	Adam	100
3	John	0
4	Nick	50
5	Tess	75

<tbody>
</tbody>

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.