Need help with VBA code for hiding across multiple ranges

[nbooth]

Hi -

I'm working on a project and would like to make the following code active with a trigger cell - like an "if" statement in Excel: if($D$3=0, activate code, don't activate code).

I've run a macro recorder and tweaked the code a bit to get the macro to do most of what I want. All I need to do now is make the code key off D3 on the Assumptions tab...And to turn off, or revert back to normal, when D3=1. Here's the recorder code:

Sub Macro3()
'
' Macro3 Macro
'
' Keyboard Shortcut: Ctrl+n
'
ActiveWindow.Zoom = 100
Columns("AD:BC").Select
Range("AD4").Activate
Selection.EntireColumn.Hidden = True
Sheets("Utility Comparison").Select
ActiveWindow.Zoom = 100
Rows("1:9").Select
Selection.EntireRow.Hidden = True
Sheets("Income Statement & Tax").Select
ActiveWindow.Zoom = 100
Rows("1:13").Select
Selection.EntireRow.Hidden = True
Sheets("Tax Rate Chart").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Depreciation").Select
ActiveWindow.Zoom = 100
Rows("2:16").Select
Selection.EntireRow.Hidden = True
Sheets("Construction").Select
ActiveWindow.Zoom = 100
Rows("2:9").Select
Selection.EntireRow.Hidden = True
Sheets("Debt").Select
ActiveWindow.Zoom = 100
Columns("U:AB").Select
Columns("U:AH").Select
Selection.EntireColumn.Hidden = True
ActiveWindow.Zoom = 100
Rows("1:13").Select
Selection.EntireRow.Hidden = True
Sheets("Cap&IRRs").Select
ActiveWindow.SelectedSheets.Visible = False
ActiveWindow.Zoom = 100

End Sub

Any help would be greatly appreciated.

Thanks!

nbooth

[p45cal]

D3 on the Assumptions tab

…which has a formula in?, or you enter these values in manually?

ps. you macro could probably be shortened to:
[vba]Sub blah()
Columns("AD:BC").EntireColumn.Hidden = True
Sheets("Utility Comparison").Rows("1:9").EntireRow.Hidden = True
Sheets("Income Statement & Tax").Rows("1:13").EntireRow.Hidden = True
Sheets("Tax Rate Chart").Visible = False
Sheets("Depreciation").Rows("2:16").EntireRow.Hidden = True
Sheets("Construction").Rows("2:9").EntireRow.Hidden = True
With Sheets("Debt")
.Columns("U:AH").EntireColumn.Hidden = True
.Rows("1:13").EntireRow.Hidden = True
End With
Sheets("Cap&IRRs").Visible = False
End Sub
[/vba]although the first line, to be robust, should be qualified with the sheet it works on (like all the other lines do) right now it acts on whichever sheet happens to be the active sheet.

[nbooth]

First, thanks for your quick response. I really do appreciate it.

And yes, that short-hand certainly does look much more efficient.

For D3 on the Assumptions tab, it will be a manually entered value; 0 for "on" and 1 for "off." I'll format the 0 and 1 to look like something else though...

Thanks again.

nbooth

[p45cal]

Put the Blah macro into a standard code module.
Put the Blah2 macro into the same standard code module.

Blah2 is a copy of Blah, except change every instance of True to False and vice versa.
Put the following event code into the Assumption sheet's code module:
[vba]Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("D3"), Target) Is Nothing Then
With Range("D3")
If .Value = 1 Then Blah2
If .Value = 0 Then Blah
End With
End If
End Sub
[/vba]All untested.

Oh, and that first line of Blah should be qualified, change it to:
Sheets("Assumptions").Columns("AD:BC").EntireColumn.Hidden = True
and qualify it in Blah2 too.

[nbooth]

p45cal -

Just wanted to circle back and say thanks for your help.

Here's the code I eventually settled on:

(In a general module)

[VBA]Sub Client()

Sheets("Assumptions").Columns("AD:BC").EntireColumn.Hidden = True
Sheets("Utility Comparison").Rows("1:9").EntireRow.Hidden = True
Sheets("Income Statement & Tax").Rows("1:13").EntireRow.Hidden = True
Sheets("Tax Rate Chart").Visible = False
Sheets("Depreciation").Rows("2:16").EntireRow.Hidden = True
Sheets("Construction").Rows("2:9").EntireRow.Hidden = True

With Sheets("Debt")
.Columns("U:AH").EntireColumn.Hidden = True
.Rows("1:13").EntireRow.Hidden = True
End With

Sheets("Cap&IRRs").Visible = False
Sheets("TE Comparison").Visible = False

End Sub

Sub Underwriting()

Sheets("Assumptions").Columns("AD:BC").EntireColumn.Hidden = False
Sheets("Utility Comparison").Rows("1:9").EntireRow.Hidden = False
Sheets("Income Statement & Tax").Rows("1:13").EntireRow.Hidden = False
Sheets("Tax Rate Chart").Visible = True
Sheets("Depreciation").Rows("2:16").EntireRow.Hidden = False
Sheets("Construction").Rows("2:9").EntireRow.Hidden = False

With Sheets("Debt")
.Columns("U:AH").EntireColumn.Hidden = False
.Rows("1:13").EntireRow.Hidden = False
End With

Sheets("Cap&IRRs").Visible = True
Sheets("TE Comparison").Visible = True

End Sub[/vba]


And in Assumption's code module:


[vba]Private Sub Worksheet_Change(ByVal Target As Range)
If Range("I9").Value = 1 Then
Call Underwriting
End If
If Range("I9").Value = 0 Then
Call Client
End If
End Sub[/VBA]

Works like a charm. Thanks very much again for your time and engagement on this.

Best,

nbooth

[p45cal]

[vba]Private Sub Worksheet_Change(ByVal Target As Range)
If Range("I9").Value = 1 Then
Call Underwriting
End If
If Range("I9").Value = 0 Then
Call Client
End If
End Sub [/vba]is not efficient; any change, anywhere on that sheet is likely to cause the code (Underwriting or Client) to run,. even if I9 hasn't changed at all.

This is more (but not the most) efficient (assuming, that is, that I9 is manually changed):
[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("I9"), Target) Is Nothing Then
If Range("I9").Value = 1 Then Underwriting
If Range("I9").Value = 0 Then Client
End If
End Sub
[/vba]