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]

Ok, there's 2 seperate pieces of code.
Use them both, but they each do different things..

The code from Post #4 goes in the ThisWorkbook module.
It will hide/unhide the sheets on the fly in the future, as values in E33 of each sheet changes.

The 2nd code from Post #7 goes in a Standard module (click Insert - New Module).
This code you can run manually at any given time of your choice.
It will go through each sheet and check if E33 is 0 and hide/unhide the sheet accordingly.

 

[jbtucke2]

Ok, there's 2 seperate pieces of code.
Use them both, but they each do different things..

The code from Post #4 goes in the ThisWorkbook module.
It will hide/unhide the sheets on the fly in the future, as values in E33 of each sheet changes.

The 2nd code from Post #7 goes in a Standard module (click Insert - New Module).
This code you can run manually at any given time of your choice.
It will go through each sheet and check if E33 is 0 and hide/unhide the sheet accordingly.

Okay 7 sounds more like what I am trying to do...especially if I can attach it to a check box. I appreciate your assistance.

On another note...do you know how to use a cell reference to make another cell reference or if this is possible


For example I have:

="PAGES: "&'FLYER CO-OP'!B2&""
="WEEK OF: "&'OTHER CO-OP'!C2&""
="WEEK OF: "&'OTHER CO-OP'!E2&""

As you can see in each of these formulas I am using row 2

Is there a way to do something to the effect of ="WEEK OF: "&'OTHER CO-OP'!E2&"" and replace the 2 with another cell reference B58 of the sheet the formula is applied to (inside B58 I would put the "2").

The goal I am trying to achieve is to change the row number in all my formulas by editing only B58.

 

[Jonmo1]

Try
="WEEK OF: "&INDEX('OTHER CO-OP'!E:E,B58)&""

 

[jbtucke2]

Try
="WEEK OF: "&INDEX('OTHER CO-OP'!E:E,B58)&""

You are a GENIUS...thank you so much