Hide Columns On Condition
 

I think I might have posed this question once before, but now i'm asking it
a bit differently. I have a table with column headers in D3:P3. A2 of this
sheet references the contents of a cell in another sheet that contains a
data validation list that references these column headers. What I would
like to do is a little difficult. I need a macro that will look in D3:P3
and hide those columns that dont match in A2. So would would happen is that
anytime A2 changes, the corresponding column will be the only one (or two)
visible.

Any ideas?

Hide Columns On Condition
 

How about this?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
For Each c In Sheets("sheet1").Range("D3:P3")
If c < Sheets("sheet1").Range("A2") Then
c.Columns.ColumnWidth = 0
Else
c.Columns.AutoFit
End If
Next
End Sub


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hide Columns On Condition
 

How do I use this code??

"Claud Balls" wrote in message
...
How about this?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
For Each c In Sheets("sheet1").Range("D3:P3")
If c < Sheets("sheet1").Range("A2") Then
c.Columns.ColumnWidth = 0
Else
c.Columns.AutoFit
End If
Next
End Sub


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hide Columns On Condition
 

Open the worksheet you want to use, go to ToolsMacrosVisual Basic
Editor
Double click ThisWorkbook, and paste the code in. The macro will run
when ever a cell is changed. Also, delete the extra space on the second
line so the end of the first line looks like this:
(ByVal Sh As Object, ByVal Target As Range)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hide Columns On Condition
 

Thanks Claud. One other question. Can I have this code work over a number
of pages? I was tinkering with the code a bit and I got it to work on the
activesheet, but it doesnt do it automatically. So what would happen is
that if on the "main" page B3 changed, the code would recalculate over
sheets January through December.

"Claud Balls" wrote in message
...
Open the worksheet you want to use, go to ToolsMacrosVisual Basic
Editor
Double click ThisWorkbook, and paste the code in. The macro will run
when ever a cell is changed. Also, delete the extra space on the second
line so the end of the first line looks like this:
(ByVal Sh As Object, ByVal Target As Range)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hide Columns On Condition
 

Try this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range) 'This should be one line

If ActiveSheet.Name = "Main" Then
If Not Intersect(Target, Range("B3")) Is Nothing Then
For Each c In Sheets("Sheet1").Range("D3:P3")
If c < Sheets("sheet1").Range("A2") Then
c.Columns.ColumnWidth = 0
Else
c.Columns.AutoFit
End If
Next
End If
End If
End Sub



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hide Columns On Condition
 

Sorry, forgot to put in the part about looping through months

If ActiveSheet.Name = "Main" Then
If Not Intersect(Target, Range("B3")) Is Nothing Then
For Each wsh In Sheets(Array _("Jan", "Feb", "Mar", "Apr", "May", _
"Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"))
For Each c In wsh.Range("D3:P3")
If c < Sheets("sheet1").Range("A2") Then
c.Columns.ColumnWidth = 0
Else
c.Columns.AutoFit
End If
Next
Next
End If
End If
End Sub

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!