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?

 

[JoeMo]

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?

Here's a macro you can install as a module for the worksheet you want to hide. In this case, I assumed the cell that controls the sheet's visibility is A1 - change to suit.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
    If Range("A1").Value = 0 And Not IsEmpty(Range("A1")) Then
        Me.Visible = xlSheetHidden
    Else
        Me.Visible = xlSheetVisible
    End If
End If
End Sub

Note: once hidden, you must use the Hide/Unhide menu command to unhide the sheet.

 

[stuckagain22]

Thanks for the code. I really am not that familiar with vb code, and I can't get this to work.

Could you please let me know what to change.

The worksheet that I want to hide/appear is "Animal".

The value that I want this sheet to trigger from is cell "E21" on worksheet "Animals_DATA".

(If I have to, I can move the value to the "Animal" sheet, but if its not too much of a problem, then I would prefer to keep it on the "Animals_DATA" sheet.

Thanks.

 

[stuckagain22]

Do I put this into a Module, or into the appropriate sheet listed under Microsoft Excel Objects?

Right now, this sheet is not showing up under the macro list.

When I select "run" to test it, I instead get a window appearing with a list of macro names, and "Worksheet_Change" is not one of the listed macros.

Please help.

 

[TheTao2]

You could also use this macro:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> AnimalZero()<br>Range("E21").Select<br><SPAN style="color:#00007F">Dim</SPAN> cell <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> Selection<br><SPAN style="color:#00007F">If</SPAN> InStr(1, cell, "0", 1) <SPAN style="color:#00007F">Then</SPAN> Sheets("Animal").Visible = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">Next</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[JoeMo]

Thanks for the code. I really am not that familiar with vb code, and I can't get this to work.

Could you please let me know what to change.

The worksheet that I want to hide/appear is "Animal".

The value that I want this sheet to trigger from is cell "E21" on worksheet "Animals_DATA".

(If I have to, I can move the value to the "Animal" sheet, but if its not too much of a problem, then I would prefer to keep it on the "Animals_DATA" sheet.

Thanks.

I've revised the code so it hides or unhides the sheet "Animal" depending on the value in cell E21 of the sheet Animals_Data. Place this in the code module for the sheet Animals_data. (Open the workbook/press Alt+F11/find the sheet Animals_Data in the tree on the left of the VB editor/double click the sheet Animals_data icon and paste the code into the empty space to the right of the tree/save the file.)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("E21")) Is Nothing Then
    If Range("E21").Value = 0 And Not IsEmpty(Range("E21")) Then
        Sheets("Animal").Visible = xlSheetHidden
    Else
        Sheets("Animal").Visible = xlSheetVisible
    End If
End If
End Sub

 

[stuckagain22]

Thanks for the detailed instructions JoeMo. I have now done all that, but it still isn't working. Also, when I select "run" to see if it is working, then it still pops up a window with the macro names, and "Worksheet_Change" is not listed there.

 

[stuckagain22]

This won't work either. This is odd, since I have had no trouble getting more complicated macros to run in the past. I'm going to post a link for download to see if someone can help shed some light on this.

 

[stuckagain22]

I have now uploaded the sheet to:

http://www.d20.ca/ogresheetA678.zip

Please let me know if you can figure why this is not working -- I would greatly appreciate it.

 

[meldoc]

never mind.