Hide sheet when changing a cell

B

blommerse

Hello, can anybody tell me how to hide a tab when I'm changing a cell.

Sample:

I have sheet A, B, C, D, E, F.

When I put an E in cell B2 from sheet A. then I want to appear sheet E
and hide sheet B, C, D and F.

Can anybody tell me how to do this? Or which code I have to use?

Thanx!
 
A

Ardus Petus

"A" Worksheet code:

'-------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rWatch As Range
Dim sSheet As Object
Set rWatch = Range("B2")
If Intersect(Target, rWatch) Is Nothing Then Exit Sub
For Each sSheet In Sheets
If sSheet.Name <> "A" Then
sSheet.Visible = sSheet.Name = rWatch.Value
End If
Next sSheet
End Sub
'--------------------------------------------------------------
 
B

blommerse

That didn't work. Also I have more sheets than 6 which I want to show.
So if I put in cel B2 an "B" I want to show sheet A, B, G and H.
If I put a "C" in cel B2 than I want to show sheet A, C, G and H.
If I put a "D" in cel B2 than I want to show sheet A, D, G and H.
If I put a "E" in cel B2 than I want to show sheet A, E, G and H.

Please help!

Greets
 
A

Ardus Petus

'--------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rWatch As Range
Dim sSheet As Object
Set rWatch = Range("B2")
If Intersect(Target, rWatch) Is Nothing Then Exit Sub
For Each sSheet In Sheets(Array("B", "C", "D", "E"))
sSheet.Visible = sSheet.Name = rWatch.Value
Next sSheet
End Sub
'--------------------------------------------------------------

HTH
 
B

blommerse

This is what I fill in:

'--------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rWatch As Range
Dim sSheet As Object
Set rWatch = Range("B9")
If Intersect(Target, rWatch) Is Nothing Then Exit Sub
For Each sSheet In Sheets(Array("TPG Post via CH", "Deutsche Post",
"Spring Royal Mail", "Select Mail", "TPG Post via Lettershop",
"Sandd"))
sSheet.Visible = sSheet.Name = rWatch.Value
Next sSheet
End Sub
'--------------------------------------------------------------

If I fill "Spring Royal Mail" in cell B2 in sheet A it used to be that
only sheet Spring Royal Mail apairs. And sheet "H" stay visible.
It's not working YET.
Greetings
 
Top