VBA Idiot here - need to hide sheet based on cell within same sheet.

[jbtucke2]

Long story short I am always reading about VBA and trying to implement pasted code I find on this site...all with no success. Can someone make an idiot proof set of instructions for how to do the following?

I have a workbook:

Sheet 1+2 are for user data input
Sheet 3 is user notes
Sheet 4 shows users which sheets by name have a value greater than 0 in Cell E33 and therefore an invoice should be sent
Sheets 5-69 are Invoices that are generated using formulas from Sheet 1 and 2 data.

All sheet names are unique I am referring to them in sequential order.

In Cell "E33" of each sheet 5-69 there is a total invoice amount...if this number is zero, I want the sheet to hide. If this number is more than zero I want sheet to be visible. I figure this can be done in 2 ways...1: If E33 >0 then hide (for EVERY sheet 5-69) or 2: If corresponding value on Sheet 4 shows 0 then hide.

I hope this makes sense.



Anyways I tried to use this:

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

But am a complete NOOB and have no idea why it is not working. I am such a NOOB that it is hard for me to even know how to paste this jazz. Here are the steps I took:

Open Visual Basic
Select Sheet 5 from left handed tree thing
Insert Module Effecting only this page
Paste Code
Save
Exit back out to sheet
...Notice that is has not worked as there is nothing in E33 of sheet 5 at the moment.
Tried to "RUN" it and just became more confused and still no winning.

HELP EXCEL GODS!?!?

 

[Jonmo1]

This can be done, but we need to clarify a couple things..

The sheet's your concerned with, are they literally named
5
6
7
etc?

Or are those the index numbers for the sheets, i.e. the 5th sheet in the book, the 6th sheet in the book ?


And do you want this to happen automatically as the values of E33 in those sheets change?
How do those values change, are they formulas? Or are the manually changed by the end user?

 

[jbtucke2]

Thanks for getting back to me, I am sorry I thought I was more clear in my original post. Let me know if there is any other info you need.

This can be done, but we need to clarify a couple things..

The sheet's your concerned with, are they literally named
5
6
7
etc?

Or are those the index numbers for the sheets, i.e. the 5th sheet in the book, the 6th sheet in the book ? They are the index numbers, the sheets all have unique names.


And do you want this to happen automatically as the values of E33 in those sheets change? YES
How do those values change, are they formulas? They are formulas (Sum) of data further up in the same column which are pulled from other sheets. Or are the manually changed by the end user? No

 

[Jonmo1]

Try this in the module named ThisWorkbook

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
If Sh.Index >= 5 And Sh.Index <= 69 Then
    If Sh.Range("E33").Value = 0 Then
        Sh.Visible = xlSheetHidden
    Else
        Sh.Visible = xlSheetVisible
    End If
End If
End Sub

 

[jbtucke2]

So I copied your code and create a module in "this workbook" pasted your code, saved it and then closed the Visual Basic window. Currently all the sheets have no value for E33 but all sheets are still showing.

Am I doing something wrong in this process?

 

[Jonmo1]

It won't actually hide/unhide any sheets until the values in E33 of those sheets actually change.

Also I'm unclear of exactly what you did here.

So I copied your code and create a module in "this workbook" pasted your code

What exactly did you do there?
Did you click Insert - New Module and Name it "this workbook" ?

That's not the way to do it.
There should already be a module built into the book called "ThisWorkbook"
That's where you put the code.

 

[Jonmo1]

If you're looking for a macro that you just run on your command, try

Sub HideSheets()
Dim i As Long
For i = 5 to 69
    With Sheets(i)
        If .Range("E33").Value = 0 Then
            .Visible = xlSheetHidden
        Else
            .Visible = xlSheetVisible
        End If
    End With
Next i
End Sub

 

[jbtucke2]

Ok I see what I did wrong the first time. This time I just click on "this workbook" and pasted code.

<a href="http://s1067.photobucket.com/user/jbtucke2/media/example_zpstggzoa64.jpg.html" target="_blank"><img src="http://i1067.photobucket.com/albums/u423/jbtucke2/example_zpstggzoa64.jpg" border="0" alt=" photo example_zpstggzoa64.jpg"/></a>

But it is still not hiding the sheets after saving.

 

[Jonmo1]

Put the code from Post #4 into the ThisWorkbook module.

And again, the sheets will only hide/unhide at the time the values in E33 of each respective sheet actually change.

 

[jbtucke2]

Put the code from Post #4 into the ThisWorkbook module.

And again, the sheets will only hide/unhide at the time the values in E33 of each respective sheet actually change.

Is there no way to make it so that they hide based on the already empty cells? I only want the sheets to show if there is a sum displayed in E33 of corresponding sheet. Most of the time this will not be the case so most sheets would be hidden which is what I want. I only want them to show if and when there is a value greater than zero calculated for E33 cells.