Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide Rows If Condition True | Excel Worksheet Functions | |||
How do I auto hide a row if a condition isnt met? | Excel Discussion (Misc queries) | |||
hide rows where cell condition is not met | Excel Worksheet Functions | |||
How do I hide a combo_box on a condition? | Excel Worksheet Functions | |||
Hide a row based on one cell's condition | Excel Worksheet Functions |