hide & unhide sheets based on cell value

[Hasson]

Hi
I want to hide sheets based on sheets names in column B , if the cell C2 is zero then should hide sheets except MAIN sheet if if it's not then should show the all of sheets.
I have this code , but doesn't do anything !

Sub VV()
Dim ws As Worksheet
Dim C As Range
Set ws = Sheets("MAIN")

        Application.ScreenUpdating = False
        For Each C In ws.Range("B2", Range("B" & Rows.Count).End(xlUp))
        If ws.Range("C2").Value = 0 Then
            Sheets(C.Value).Visible = False
            Else
            Sheets(C.Value).Visible = True
            End If
        Next C
       
        Application.ScreenUpdating = True
   
End Sub

I hope finding assistance

 

[Peter_SSs]

Code seems to work for me (provided 'MAIN' is the active sheet when the code is run!). Are you sure that the sheet names in column B of 'MAIN' exactly match the actual sheet names?

Here is my sample workbook structure

and this is 'MAIN'

Hasson.xlsm
	B	C
1
2	Sheet2	0
3	Sheet3
4	Sheet4
5	Sheet5
6	Sheet6
7
MAIN

After I run your code:

Change C2 value in 'MAIN'

Hasson.xlsm
	B	C
1
2	Sheet2	3
3	Sheet3
4	Sheet4
5	Sheet5
6	Sheet6
7
MAIN

Run your code again:

Isn't that what you are trying to achieve?

 

[Hasson]

Hi,
first seem to I make mistake by I put zero in C1.
second the problem when C2 is empty , then the sheets keep hidden !

 

[Peter_SSs]

Give it a try this way

Sub VV_v2()
  Dim C As Range
  
  With Sheets("MAIN")
    For Each C In .Range("B2", .Range("B" & Rows.Count).End(xlUp))
      Sheets(C.Value).Visible = CStr(.Range("C2").Value) <> "0"
    Next C
  End With
End Sub

 

[Hasson]

Awesome !
can you do when write in C2 will directly run the code without click button, please?

 

[Peter_SSs]

can you do when write in C2 will directly run the code without click button, please?

Sure, code below would go in the 'MAIN' worksheet's module.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim C As Range
  
  If Not Intersect(Target, Range("C2")) Is Nothing Then
    For Each C In Range("B2", Range("B" & Rows.Count).End(xlUp))
      Sheets(C.Value).Visible = CStr(Range("C2").Value) <> "0"
    Next C
  End If
End Sub

 

[Hasson]

works perfectly !
much appreciated for your help.

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.