Macro to Hide/Unhide worksheet based on a cell value

[stuckagain22]

I would like to create a macro/vb code that will unhide a worksheet if cell value is greater than zero, or hide the worksheet if the cell value is zero.

I'm not too familiar with VB code and was wondering if this is possible? I have my doubts, since it means that the code must constantly be running in an endless loop, checking to see if the value has changed?

 

[meldoc]

Hello

I tried changing the value of E21 manually, and posted that it worked.
Only after posting did I think to check if the change was the result of a formula.
You need a calculate event for that, try the below code, as a replacement.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Calculate()<br>    <SPAN style="color:#00007F">If</SPAN> Range("E21").Value = 0 And <SPAN style="color:#00007F">Not</SPAN> IsEmpty(Range("E21")) <SPAN style="color:#00007F">Then</SPAN><br>        Sheets("Animal").Visible = xlSheetHidden<br>    <SPAN style="color:#00007F">Else</SPAN><br>        Sheets("Animal").Visible = xlSheetVisible<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[stuckagain22]

I've been doing some testing and I believe I found the problem... I just don't know how to fix it.

JoeMo, your code works fine on a test sheet I tried, until I change the cell contents to a formula.

If A1 is just a number, then it works. If A1 displays a number, but contains a formula, then it doesn't work. I tested this by typing "2" in B1, and then putting "=B1" in A1, and then it stopped working.

Is there a way to fix this?

 

[stuckagain22]

Hello

I tried changing the value of E21 manually, and posted that it worked.
Only after posting did I think to check if the change was the result of a formula.
You need a calculate event for that, try the below code, as a replacement.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Calculate()<br>****<SPAN style="color:#00007F">If</SPAN> Range("E21").Value = 0 And <SPAN style="color:#00007F">Not</SPAN> IsEmpty(Range("E21")) <SPAN style="color:#00007F">Then</SPAN><br>********Sheets("Animal").Visible = xlSheetHidden<br>****<SPAN style="color:#00007F">Else</SPAN><br>********Sheets("Animal").Visible = xlSheetVisible<br>****<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Thanks JoeMo!!! This works great!!

Please ignore the post I made before this... I hadn't noticed that there was a reply on page 2.

Thanks so much!

 

[patsdavixen]

Hi Everyone,

I have a similar question however, the listed code in this thread does not seem to work in my situation.
I have a sheet named "Input" in my workbook. In Cell B4 of the "Input" sheet, the user can either select "B" or "V".
I have a sheet named "B" and another sheet named "V" in the same workbook. I also have 20 other sheets that should always remain hidden.
If the user selects "B" only sheet "B" should be visible and all other sheets should be hidden. If the user changes B4 to "V", only the sheet "V" should be visible and all other sheets including "B" should become hidden. Here is the code I have so far:

Sub Unhide()

Sheets("Input").Select
Range("B4").Select
If Worksheets("Input").Range("B4") = "V" Then
Sheets("V").Visible = True
Else
Sheets("B").Visible = False
If Worksheets("Input").Range("B4") = "B" Then
Sheets("B").Visible = True
Else
Sheets("V").Visible = False
End If
End If
End Sub

Also Private Sub is not working in my excel files.

Any help would be appreciated.

Thank you - Pat

 

[stuckagain22]

Firstly let me start off by saying I am by no means an Excel expert, but if I understood correctly what you want to happen then I think you code should be:

Sub Unhide()

Sheets("Input").Select
Range("B4").Select
If Worksheets("Input").Range("B4") = "V" Then
  Sheets("V").Visible = True
  Sheets("B").Visible = False
Else
If Worksheets("Input").Range("B4") = "B" Then
  Sheets("B").Visible = True
  Sheets("V").Visible = False
End If
End Sub

 

[stuckagain22]

Also Private Sub is not working in my excel files.

Just to confirm...

Private Sub Worksheet_Unhide()

... is not working when you use the above code?

 

[patsdavixen]

Just to confirm...

Private Sub Worksheet_Unhide()

... is not working when you use the above code?

Thank you very much. That code worked fine. The only thing is do you know how to ensure that any other unhidden sheet irrespective of the sheet name is also hidden?

And yes, Private Sub doesn't seem to work and I do not understand why. I have used Private Function in the past and that worked but I do not know why private Sub does not work