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?

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!

Hi

Paste the code onto sheet module
right click on the sheet tab -> View Code then paste the code on the right side then go back to the Excel screen and change A2

Please Login or Register  to view this content.

hope this helps

jindon

How do I use this code??

"Claud Balls" <[email protected]> wrote in message
news:%[email protected]...
> 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!

Open the worksheet you want to use, go to Tools>Macros>Visual 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!

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" <[email protected]> wrote in message
news:[email protected]...
> Open the worksheet you want to use, go to Tools>Macros>Visual 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!

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!

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!