Hiding Columns based on value from another worksheet

J

jaredmrowe

I'm building a pro-forma financial statement that will evaluate an
investment for a given number of years. I need to be able to put in a
number specifying how long I will hold the investment in cell I-6 on a
worksheet called "Data Entry", and then add or subtract columns on a
worksheet called "operating Statement" based on the number from the
data entry worksheet.

The maximum holding period is 10 years, so I figure the easiest way to
do this is to just build my worksheet to have columns for years 0-10,
and then use the macro to hide columns if the holding period is less
than 10. Example, if the holding period is 5, I want the macro to hide
columns 6-10.

How do I do this?

Thanks.
 
J

jaredmrowe

I've inserted the following code, but there are problems. the code
works fine, unless the value of the target cell is 1. If I set it to
one, then nothing happens when I change the value unless I set it to
10, and then it's like the thing resets and it works fine and adjusts
to whatever number is in the target cell. Why does the value 1 screw
this up? How can I fix it. Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)
Const HOLD_P As String = "I6"


On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Address(False, False) = HOLD_P Then
Select Case .Value
Case "1"
Sheets("Operating Statement").Columns("D:L").Hidden
= True
Case "2"
Sheets("Operating Statement").Columns("E:L").Hidden
= True
Case "3"
Sheets("Operating Statement").Columns("F:L").Hidden
= True
Case "4"
Sheets("Operating Statement").Columns("G:L").Hidden
= True
Case "5"
Sheets("Operating Statement").Columns("H:L").Hidden
= True
Case "6"
Sheets("Operating Statement").Columns("I:L").Hidden
= True
Case "7"
Sheets("Operating Statement").Columns("J:L").Hidden
= True
Case "8"
Sheets("Operating Statement").Columns("K:L").Hidden
= True
Case "9"
Sheets("Operating Statement").Columns("L:L").Hidden
= True
Case "10"
Sheets("Operating Statement").Columns("C:L").Hidden
= False
End Select
End If
End With


ws_exit:
Application.EnableEvents = True
End Sub
 
J

jaredmrowe

I solved it by modifying it as below. i totally rock. please let me
know if there's an easier way to do this. Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)
Const HOLD_P As String = "I6"


On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Address(False, False) = HOLD_P Then
Select Case .Value
Case "1"
Sheets("Operating Statement").Columns("C:L").Hidden
= False
Sheets("Operating Statement").Columns("D:L").Hidden
= True
Case "2"
Sheets("Operating Statement").Columns("C:L").Hidden
= False
Sheets("Operating Statement").Columns("E:L").Hidden
= True
Case "3"
Sheets("Operating Statement").Columns("C:L").Hidden
= False
Sheets("Operating Statement").Columns("F:L").Hidden
= True
Case "4"
Sheets("Operating Statement").Columns("C:L").Hidden
= False
Sheets("Operating Statement").Columns("G:L").Hidden
= True
Case "5"
Sheets("Operating Statement").Columns("C:L").Hidden
= False
Sheets("Operating Statement").Columns("H:L").Hidden
= True
Case "6"
Sheets("Operating Statement").Columns("C:L").Hidden
= False
Sheets("Operating Statement").Columns("I:L").Hidden
= True
Case "7"
Sheets("Operating Statement").Columns("C:L").Hidden
= False
Sheets("Operating Statement").Columns("J:L").Hidden
= True
Case "8"
Sheets("Operating Statement").Columns("C:L").Hidden
= False
Sheets("Operating Statement").Columns("K:L").Hidden
= True
Case "9"
Sheets("Operating Statement").Columns("C:L").Hidden
= False
Sheets("Operating Statement").Columns("L:L").Hidden
= True
Case "10"
Sheets("Operating Statement").Columns("C:L").Hidden
= False
End Select
End If
End With
 
G

Guest

Using a helper row/range (say row 5) will be better and have two macros - one
to hide and another to unhide. Obviously you will be having your YEAR
headings on a row. Say they are from C4:L4. Name C5:L5 "HICOLS". Name a cell
to identify current year as "CurYr". On C5 enter =IF(C5>="CurYR",1,0). Copy
through D5:L5.
You can hide row 5 or format the font colour as white so the numbers become
invisible. Enter the number for the current year in "CurYr" and run the
following macro. For subsequent months in will not be necessary to run the
Unhide macro unless you want to see the previous years numbers.
Note "CurYr" must follow your YEAR headings and can be 2005,2006,2007, etc.
or 1,2,3, whichever you choose.
Sub HideColums()
For Each cell_in_loop In Range("HICOLS")
If cell_in_loop.Value = 0 Then
With cell_in_loop
.EntireColumn.Hidden = True
End With
End If
Next
End Sub

Sub UnHideColums()
For Each cell_in_loop In Range("HICOLS")
If cell_in_loop.Value = 0 Then
With cell_in_loop
.EntireColumn.Hidden = False
End With
End If
Next
End Sub

All macros from this ng.

ps. I do not know VBA,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Hide Columns Based On Date Value 1
Hiding columns based on cell contents 3
Hiding Columns based on cell value 3
Makro from value in one cell, hiding columns in another sheet 4
Hiding cells in another worksheet 2
Excel Columns moving up and down on their own in a worksheet. What Goes? 2
Hiding columns conditionally 0
Total columns Based on another cells value 4

Top