Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
hide columns based on header macro
Hi, I need a macro that can loop through a workbook and based on the headers
hide columns. So I could use a dropbox list, select a heading and the headings on the list not selected (four possible headers for the list and 30 worksheets) would be hidden. I learning but am not good at VB and will appreciate any help. Thanks, Todd |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
hide columns based on header macro
Try something like the following, making adjustments to fit your layout
Assume your combobox is on Sheet(1) Attach the following code to your combobox Assign the combobox linkcell as "W1" Sub HideColumns() Dim sh as worksheet Dim c as range For each sh in Worksheets ' loop thru all worksheets For each c in Range("A1:D1") ' assuming this is where your headers are If c.value=Sheet(1).Range("W1") c.EntireColumn.Hidden=False Else c.EntireColumn.Hidden=True End If Next c Next sh End Sub "Todd L." wrote: Hi, I need a macro that can loop through a workbook and based on the headers hide columns. So I could use a dropbox list, select a heading and the headings on the list not selected (four possible headers for the list and 30 worksheets) would be hidden. I learning but am not good at VB and will appreciate any help. Thanks, Todd |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
hide columns based on header macro
Thanks so much,
I am having an error at "If c.Value = Sheet(1).Range("W1") " which says there is an expected then or go to. I put a then at the end of the statement and I get "sub or function not defined". What else do I need to do? Todd Sub HideColumns() Dim sh As Worksheet Dim c As Range For Each sh In Worksheets ' loop thru all worksheets For Each c In Range("A1:D1") ' assuming this is where your headers are If c.Value = Sheet(1).Range("W1") Then c.EntireColumn.Hidden = False Else c.EntireColumn.Hidden = True End If Next c Next sh End Sub "gocush" wrote: Try something like the following, making adjustments to fit your layout Assume your combobox is on Sheet(1) Attach the following code to your combobox Assign the combobox linkcell as "W1" Sub HideColumns() Dim sh as worksheet Dim c as range For each sh in Worksheets ' loop thru all worksheets For each c in Range("A1:D1") ' assuming this is where your headers are If c.value=Sheet(1).Range("W1") c.EntireColumn.Hidden=False Else c.EntireColumn.Hidden=True End If Next c Next sh End Sub "Todd L." wrote: Hi, I need a macro that can loop through a workbook and based on the headers hide columns. So I could use a dropbox list, select a heading and the headings on the list not selected (four possible headers for the list and 30 worksheets) would be hidden. I learning but am not good at VB and will appreciate any help. Thanks, Todd |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
hide columns based on header macro
sorry
change the following line: If c.Value = Sheet(1).Range("W1") Then to If c.Value = Sheets("YourSheetName").Range("W1") Then Change "YourSheetName" to the name of your sheet where you have range("W1") storing the value from the dropdown. Another note: If you are using a dropdown made from the FORMS toolbar, then the value stored in the linkcell ("W2") will not be a text value as in your header. Instead, it will be a number: If the user selects the third option in the dropbox which might be "Blue" if your headers are colors, then Range("W2") will have the value 3 rather than "Blue". The 3 would then have to be converted back to "Blue" in order to compare it to your headers. On the other hand, if your dropdown was created form the CONTROL TOOLBOX, it will store the actual text "Blue" in W3 If you have a FORMS dropdown, use the following Option Explicit Sub HideColumns() Dim sh As Worksheet Dim c As Range Dim Sel As String Sel = Application.Index(Sheets("Sheet1").Range("A1:D1"), _ Sheets("Sheet1").Range("W1")) For Each sh In Worksheets ' loop thru all worksheets For Each c In Range("A1:D1") ' assuming this is where your headers are If c.Value = Sel Then c.EntireColumn.Hidden = False Else c.EntireColumn.Hidden = True End If Next c Next sh End Sub "Todd" wrote: Thanks so much, I am having an error at "If c.Value = Sheet(1).Range("W1") " which says there is an expected then or go to. I put a then at the end of the statement and I get "sub or function not defined". What else do I need to do? Todd Sub HideColumns() Dim sh As Worksheet Dim c As Range For Each sh In Worksheets ' loop thru all worksheets For Each c In Range("A1:D1") ' assuming this is where your headers are If c.Value = Sheet(1).Range("W1") Then c.EntireColumn.Hidden = False Else c.EntireColumn.Hidden = True End If Next c Next sh End Sub "gocush" wrote: Try something like the following, making adjustments to fit your layout Assume your combobox is on Sheet(1) Attach the following code to your combobox Assign the combobox linkcell as "W1" Sub HideColumns() Dim sh as worksheet Dim c as range For each sh in Worksheets ' loop thru all worksheets For each c in Range("A1:D1") ' assuming this is where your headers are If c.value=Sheet(1).Range("W1") c.EntireColumn.Hidden=False Else c.EntireColumn.Hidden=True End If Next c Next sh End Sub "Todd L." wrote: Hi, I need a macro that can loop through a workbook and based on the headers hide columns. So I could use a dropbox list, select a heading and the headings on the list not selected (four possible headers for the list and 30 worksheets) would be hidden. I learning but am not good at VB and will appreciate any help. Thanks, Todd |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
hide columns based on header macro
Sub HideColumns()
Dim sh as worksheet Dim c as range For each sh in Worksheets ' loop thru all worksheets For each c in sh.Range("A1:D1") ' assuming this is where your headers are If c.value = Sheets(1).Range("W1").Value Then c.EntireColumn.Hidden=False Else c.EntireColumn.Hidden=True End If Next c Next sh End Sub -- Regards, Tom Ogilvy "Todd" wrote in message ... Thanks so much, I am having an error at "If c.Value = Sheet(1).Range("W1") " which says there is an expected then or go to. I put a then at the end of the statement and I get "sub or function not defined". What else do I need to do? Todd Sub HideColumns() Dim sh As Worksheet Dim c As Range For Each sh In Worksheets ' loop thru all worksheets For Each c In Range("A1:D1") ' assuming this is where your headers are If c.Value = Sheet(1).Range("W1") Then c.EntireColumn.Hidden = False Else c.EntireColumn.Hidden = True End If Next c Next sh End Sub "gocush" wrote: Try something like the following, making adjustments to fit your layout Assume your combobox is on Sheet(1) Attach the following code to your combobox Assign the combobox linkcell as "W1" Sub HideColumns() Dim sh as worksheet Dim c as range For each sh in Worksheets ' loop thru all worksheets For each c in Range("A1:D1") ' assuming this is where your headers are If c.value=Sheet(1).Range("W1") c.EntireColumn.Hidden=False Else c.EntireColumn.Hidden=True End If Next c Next sh End Sub "Todd L." wrote: Hi, I need a macro that can loop through a workbook and based on the headers hide columns. So I could use a dropbox list, select a heading and the headings on the list not selected (four possible headers for the list and 30 worksheets) would be hidden. I learning but am not good at VB and will appreciate any help. Thanks, Todd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide Columns based on a cell value | Excel Worksheet Functions | |||
Writing a macro to hide columns based on cell value | Excel Discussion (Misc queries) | |||
Automatic Hide group of a columns based on cel value | Excel Discussion (Misc queries) | |||
Macro to Hide/Show Columns based on control cell value | Excel Programming | |||
macro to hide column based on header | Excel Programming |