Hide/Unhide sheet based on cell value

[rww]

I'm trying to find a way to hide or unhide one excel sheet based on the contents of a cell on another sheet. I would appreciate any and all help, but since I'm new to VBA, I need to know how to enter the results into my spreadsheet.

Thanks.

[Artik]

In worksheets module, eg. Sheet1:Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
If Me.Range("A1").Value = 0 Then
ThisWorkbook.Worksheets("Sheet2").Visible = xlSheetHidden
ElseIf Me.Range("A1").Value = 1 Then
ThisWorkbook.Worksheets("Sheet2").Visible = xlSheetVisible
Else
ThisWorkbook.Worksheets("Sheet2").Visible = xlSheetVeryHidden
End If
End If
End Sub Depending on the value in cell A1 of eg. Sheet1, Sheet2 to be shown, hidden or very hidden.

Artik

[Bob Phillips]

Why would you use Me.Range("A1") in the code when you acsertained that Me.Range("A1") is Target a step earlier?

[Artik]

Xld sorry, but my translator does not understand a word "acsertained".

My guess is only that you ask me, why I use the Range("A1"), but not Target? Is this what you meant? If yes, then - the Target returns the range of multicellular.
For example.
I change Range("A1:B2") by copy/paste. Parameter Target returns the range A1:B2, but not one cell.

Another version of the code:Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range

Set Rng = Intersect(Target, Me.Range("A1"))

If Not Rng Is Nothing Then

With ThisWorkbook.Worksheets("Sheet2")
If Rng.Value = 0 Then
.Visible = xlSheetHidden
ElseIf Rng.Value = 1 Then
.Visible = xlSheetVisible
Else
.Visible = xlSheetVeryHidden
End If
End With

Set Rng = Nothing
End If

End Sub
Is OK? :)

Artik

[Bob Phillips]

Agreed, but there are many ways to handle multi-cell selections.

First you have to decide if that is valid. If it is not, error out. If it is then process the whole range like so



Set Rng = Intersect(Target, Me.Range("A1:A10"))

If Not Rng Is Nothing Then

For Each cell In Rng

'do stuff
Next cell
End If


Either way, setting Rng the way you do, against a single cell, seems unnecessary, you already had Target.