Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Columns Based on Condition
New to VBA and need help.
I am trying to hide columns based on a condition. My workbook has numerous worksheets. Each worksheet contains data for a specific "account" and each column within the "account" worksheet has month end data. Therefore row 1 of each worksheet contains month-end values and each row below has various data for that specific "account." Furthermore, the first worksheet, which basically serves as a "menu", has a cell with a specific date. What I would like to do is write a macro in each worksheet representing an "account" that hides all columns in the "account" worksheets that have not occurred yet. (i.e. If the date on the menu worksheet is 3/31/05, I would like all columns that have month-end values exceeding 3/31/05 to be hidden). Any suggestions? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Columns Based on Condition
Change sMenu to hold the name of the Menu sheet and rngDate to refer to the
cell containing the date. Sub Hidecolumns() Dim sh as worksheet, rng as Range, cell as Range Dim sMenu as String, rngDate as Range sMenu = lcase("menu") set rngDate = worksheets(sMenu").Range("B9") for each sh in Worksheets if lcase(sh.Name) < sMenu then sh.Columns.Hidden = False set rng = sh.Range(sh.Cells(1,1),sh.Cells(1,"IV").End(xltoLe ft)) for each cell in rng if isdate(cell).Value then if cell.Value rngDate.Value then cell.EntireColumn.Hidden = True end if end if next end if Next End Sub -- Regards, Tom Ogilvy "Skornia115" wrote: New to VBA and need help. I am trying to hide columns based on a condition. My workbook has numerous worksheets. Each worksheet contains data for a specific "account" and each column within the "account" worksheet has month end data. Therefore row 1 of each worksheet contains month-end values and each row below has various data for that specific "account." Furthermore, the first worksheet, which basically serves as a "menu", has a cell with a specific date. What I would like to do is write a macro in each worksheet representing an "account" that hides all columns in the "account" worksheets that have not occurred yet. (i.e. If the date on the menu worksheet is 3/31/05, I would like all columns that have month-end values exceeding 3/31/05 to be hidden). Any suggestions? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Columns Based on Condition
Thanks Tom. If I want this to occur automatically, is there anything
specific I need to do with the syntax? Tom Ogilvy wrote: Change sMenu to hold the name of the Menu sheet and rngDate to refer to the cell containing the date. Sub Hidecolumns() Dim sh as worksheet, rng as Range, cell as Range Dim sMenu as String, rngDate as Range sMenu = lcase("menu") set rngDate = worksheets(sMenu").Range("B9") for each sh in Worksheets if lcase(sh.Name) < sMenu then sh.Columns.Hidden = False set rng = sh.Range(sh.Cells(1,1),sh.Cells(1,"IV").End(xltoLe ft)) for each cell in rng if isdate(cell).Value then if cell.Value rngDate.Value then cell.EntireColumn.Hidden = True end if end if next end if Next End Sub -- Regards, Tom Ogilvy "Skornia115" wrote: New to VBA and need help. I am trying to hide columns based on a condition. My workbook has numerous worksheets. Each worksheet contains data for a specific "account" and each column within the "account" worksheet has month end data. Therefore row 1 of each worksheet contains month-end values and each row below has various data for that specific "account." Furthermore, the first worksheet, which basically serves as a "menu", has a cell with a specific date. What I would like to do is write a macro in each worksheet representing an "account" that hides all columns in the "account" worksheets that have not occurred yet. (i.e. If the date on the menu worksheet is 3/31/05, I would like all columns that have month-end values exceeding 3/31/05 to be hidden). Any suggestions? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Columns Based on Condition
Thanks Tom. If I want this to occur automatically, is there anything
specific I need to do with the syntax? Tom Ogilvy wrote: Change sMenu to hold the name of the Menu sheet and rngDate to refer to the cell containing the date. Sub Hidecolumns() Dim sh as worksheet, rng as Range, cell as Range Dim sMenu as String, rngDate as Range sMenu = lcase("menu") set rngDate = worksheets(sMenu").Range("B9") for each sh in Worksheets if lcase(sh.Name) < sMenu then sh.Columns.Hidden = False set rng = sh.Range(sh.Cells(1,1),sh.Cells(1,"IV").End(xltoLe ft)) for each cell in rng if isdate(cell).Value then if cell.Value rngDate.Value then cell.EntireColumn.Hidden = True end if end if next end if Next End Sub -- Regards, Tom Ogilvy "Skornia115" wrote: New to VBA and need help. I am trying to hide columns based on a condition. My workbook has numerous worksheets. Each worksheet contains data for a specific "account" and each column within the "account" worksheet has month end data. Therefore row 1 of each worksheet contains month-end values and each row below has various data for that specific "account." Furthermore, the first worksheet, which basically serves as a "menu", has a cell with a specific date. What I would like to do is write a macro in each worksheet representing an "account" that hides all columns in the "account" worksheets that have not occurred yet. (i.e. If the date on the menu worksheet is 3/31/05, I would like all columns that have month-end values exceeding 3/31/05 to be hidden). Any suggestions? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Columns Based on Condition
I see there were a couple of typos in the original, but I would see it firing
only when you change the date in the menu sheet - my interpretation of automatically. right click on the sheet tab of that sheet and select view code, then put in code like this in the resulting module: Private Sub Worksheet_Change(ByVal Target As Range) Dim sh As Worksheet, rng As Range, cell As Range Dim sMenu As String, rngDate As Range sMenu = Me.Name Set rngDate = Worksheets(sMenu).Range("B9") If Target.Address = rngDate.Address Then For Each sh In Worksheets If LCase(sh.Name) < sMenu Then sh.Columns.Hidden = False Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(1, "IV").End(xlToLeft)) For Each cell In rng If IsDate(cell.Value) Then If cell.Value rngDate.Value Then cell.EntireColumn.Hidden = True End If End If Next End If Next End If End Sub If you want something different, you would have to define automatically. -- Regards, Tom Ogilvy "Skornia115" wrote: Thanks Tom. If I want this to occur automatically, is there anything specific I need to do with the syntax? Tom Ogilvy wrote: Change sMenu to hold the name of the Menu sheet and rngDate to refer to the cell containing the date. Sub Hidecolumns() Dim sh as worksheet, rng as Range, cell as Range Dim sMenu as String, rngDate as Range sMenu = lcase("menu") set rngDate = worksheets(sMenu").Range("B9") for each sh in Worksheets if lcase(sh.Name) < sMenu then sh.Columns.Hidden = False set rng = sh.Range(sh.Cells(1,1),sh.Cells(1,"IV").End(xltoLe ft)) for each cell in rng if isdate(cell).Value then if cell.Value rngDate.Value then cell.EntireColumn.Hidden = True end if end if next end if Next End Sub -- Regards, Tom Ogilvy "Skornia115" wrote: New to VBA and need help. I am trying to hide columns based on a condition. My workbook has numerous worksheets. Each worksheet contains data for a specific "account" and each column within the "account" worksheet has month end data. Therefore row 1 of each worksheet contains month-end values and each row below has various data for that specific "account." Furthermore, the first worksheet, which basically serves as a "menu", has a cell with a specific date. What I would like to do is write a macro in each worksheet representing an "account" that hides all columns in the "account" worksheets that have not occurred yet. (i.e. If the date on the menu worksheet is 3/31/05, I would like all columns that have month-end values exceeding 3/31/05 to be hidden). Any suggestions? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Columns Based on Condition
I have tried pasting this in with no luck. I just want to make sure I
am doing everything correctly. The name of the menu worksheet is Menu and the cell within the Menu worksheet I want referenced is B9. This is what I have: Private Sub Worksheet_Change(ByVal Target As Range) Dim sh As Worksheet, rng As Range, cell As Range Dim Menu As String, rngDate As Range Menu = Me.Name Set rngDate = Worksheets(Menu).Range("B9") If Target.Address = rngDate.Address Then For Each sh In Worksheets If LCase(sh.Name) < Menu Then sh.Columns.Hidden = False Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(1, "IV").End(xlToLeft)) For Each cell In rng If IsDate(cell.Value) Then If cell.Value rngDate.Value Then cell.EntireColumn.Hidden = True End If End If Next End If Next End If End Sub Thanks for your patience. Tom Ogilvy wrote: I see there were a couple of typos in the original, but I would see it firing only when you change the date in the menu sheet - my interpretation of automatically. right click on the sheet tab of that sheet and select view code, then put in code like this in the resulting module: Private Sub Worksheet_Change(ByVal Target As Range) Dim sh As Worksheet, rng As Range, cell As Range Dim sMenu As String, rngDate As Range sMenu = Me.Name Set rngDate = Worksheets(sMenu).Range("B9") If Target.Address = rngDate.Address Then For Each sh In Worksheets If LCase(sh.Name) < sMenu Then sh.Columns.Hidden = False Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(1, "IV").End(xlToLeft)) For Each cell In rng If IsDate(cell.Value) Then If cell.Value rngDate.Value Then cell.EntireColumn.Hidden = True End If End If Next End If Next End If End Sub If you want something different, you would have to define automatically. -- Regards, Tom Ogilvy "Skornia115" wrote: Thanks Tom. If I want this to occur automatically, is there anything specific I need to do with the syntax? Tom Ogilvy wrote: Change sMenu to hold the name of the Menu sheet and rngDate to refer to the cell containing the date. Sub Hidecolumns() Dim sh as worksheet, rng as Range, cell as Range Dim sMenu as String, rngDate as Range sMenu = lcase("menu") set rngDate = worksheets(sMenu").Range("B9") for each sh in Worksheets if lcase(sh.Name) < sMenu then sh.Columns.Hidden = False set rng = sh.Range(sh.Cells(1,1),sh.Cells(1,"IV").End(xltoLe ft)) for each cell in rng if isdate(cell).Value then if cell.Value rngDate.Value then cell.EntireColumn.Hidden = True end if end if next end if Next End Sub -- Regards, Tom Ogilvy "Skornia115" wrote: New to VBA and need help. I am trying to hide columns based on a condition. My workbook has numerous worksheets. Each worksheet contains data for a specific "account" and each column within the "account" worksheet has month end data. Therefore row 1 of each worksheet contains month-end values and each row below has various data for that specific "account." Furthermore, the first worksheet, which basically serves as a "menu", has a cell with a specific date. What I would like to do is write a macro in each worksheet representing an "account" that hides all columns in the "account" worksheets that have not occurred yet. (i.e. If the date on the menu worksheet is 3/31/05, I would like all columns that have month-end values exceeding 3/31/05 to be hidden). Any suggestions? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Columns Based on Condition
I assumed you would not have dates going all the way to column IV. It worked
for me in that case. If you do have dates going all the way to column IV change the code: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim sh As Worksheet, rng As Range, cell As Range Dim sMenu As String, rngDate As Range sMenu = LCase(Me.Name) Set rngDate = Worksheets(sMenu).Range("B9") If Target.Address = rngDate.Address Then For Each sh In Worksheets If LCase(sh.Name) < sMenu Then sh.Columns.Hidden = False ' Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(1, "IV").End(xlToLeft)) Set rng = sh.Range("A1:IV1") For Each cell In rng If IsDate(cell.Value) Then If cell.Value rngDate.Value Then cell.EntireColumn.Hidden = True End If End If Next End If Next End If End Sub -- Regards, Tom Ogilvy |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Columns Based on Condition
Also, you have to edit the cell/change the date in cell B9 on sheet menu to
make it fire. -- Regards, Tom Ogilvy "Skornia115" wrote: I have tried pasting this in with no luck. I just want to make sure I am doing everything correctly. The name of the menu worksheet is Menu and the cell within the Menu worksheet I want referenced is B9. This is what I have: Private Sub Worksheet_Change(ByVal Target As Range) Dim sh As Worksheet, rng As Range, cell As Range Dim Menu As String, rngDate As Range Menu = Me.Name Set rngDate = Worksheets(Menu).Range("B9") If Target.Address = rngDate.Address Then For Each sh In Worksheets If LCase(sh.Name) < Menu Then sh.Columns.Hidden = False Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(1, "IV").End(xlToLeft)) For Each cell In rng If IsDate(cell.Value) Then If cell.Value rngDate.Value Then cell.EntireColumn.Hidden = True End If End If Next End If Next End If End Sub Thanks for your patience. Tom Ogilvy wrote: I see there were a couple of typos in the original, but I would see it firing only when you change the date in the menu sheet - my interpretation of automatically. right click on the sheet tab of that sheet and select view code, then put in code like this in the resulting module: Private Sub Worksheet_Change(ByVal Target As Range) Dim sh As Worksheet, rng As Range, cell As Range Dim sMenu As String, rngDate As Range sMenu = Me.Name Set rngDate = Worksheets(sMenu).Range("B9") If Target.Address = rngDate.Address Then For Each sh In Worksheets If LCase(sh.Name) < sMenu Then sh.Columns.Hidden = False Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(1, "IV").End(xlToLeft)) For Each cell In rng If IsDate(cell.Value) Then If cell.Value rngDate.Value Then cell.EntireColumn.Hidden = True End If End If Next End If Next End If End Sub If you want something different, you would have to define automatically. -- Regards, Tom Ogilvy "Skornia115" wrote: Thanks Tom. If I want this to occur automatically, is there anything specific I need to do with the syntax? Tom Ogilvy wrote: Change sMenu to hold the name of the Menu sheet and rngDate to refer to the cell containing the date. Sub Hidecolumns() Dim sh as worksheet, rng as Range, cell as Range Dim sMenu as String, rngDate as Range sMenu = lcase("menu") set rngDate = worksheets(sMenu").Range("B9") for each sh in Worksheets if lcase(sh.Name) < sMenu then sh.Columns.Hidden = False set rng = sh.Range(sh.Cells(1,1),sh.Cells(1,"IV").End(xltoLe ft)) for each cell in rng if isdate(cell).Value then if cell.Value rngDate.Value then cell.EntireColumn.Hidden = True end if end if next end if Next End Sub -- Regards, Tom Ogilvy "Skornia115" wrote: New to VBA and need help. I am trying to hide columns based on a condition. My workbook has numerous worksheets. Each worksheet contains data for a specific "account" and each column within the "account" worksheet has month end data. Therefore row 1 of each worksheet contains month-end values and each row below has various data for that specific "account." Furthermore, the first worksheet, which basically serves as a "menu", has a cell with a specific date. What I would like to do is write a macro in each worksheet representing an "account" that hides all columns in the "account" worksheets that have not occurred yet. (i.e. If the date on the menu worksheet is 3/31/05, I would like all columns that have month-end values exceeding 3/31/05 to be hidden). Any suggestions? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Columns Based on Condition
So all I need to do is paste the syntax exactly as it is into each
worksheet, or do I need to change how the Menu worksheet is referenced in the syntax. Also, which worksheets do I paste this in....the "menu" or each "account". Tom Ogilvy wrote: Also, you have to edit the cell/change the date in cell B9 on sheet menu to make it fire. -- Regards, Tom Ogilvy "Skornia115" wrote: I have tried pasting this in with no luck. I just want to make sure I am doing everything correctly. The name of the menu worksheet is Menu and the cell within the Menu worksheet I want referenced is B9. This is what I have: Private Sub Worksheet_Change(ByVal Target As Range) Dim sh As Worksheet, rng As Range, cell As Range Dim Menu As String, rngDate As Range Menu = Me.Name Set rngDate = Worksheets(Menu).Range("B9") If Target.Address = rngDate.Address Then For Each sh In Worksheets If LCase(sh.Name) < Menu Then sh.Columns.Hidden = False Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(1, "IV").End(xlToLeft)) For Each cell In rng If IsDate(cell.Value) Then If cell.Value rngDate.Value Then cell.EntireColumn.Hidden = True End If End If Next End If Next End If End Sub Thanks for your patience. Tom Ogilvy wrote: I see there were a couple of typos in the original, but I would see it firing only when you change the date in the menu sheet - my interpretation of automatically. right click on the sheet tab of that sheet and select view code, then put in code like this in the resulting module: Private Sub Worksheet_Change(ByVal Target As Range) Dim sh As Worksheet, rng As Range, cell As Range Dim sMenu As String, rngDate As Range sMenu = Me.Name Set rngDate = Worksheets(sMenu).Range("B9") If Target.Address = rngDate.Address Then For Each sh In Worksheets If LCase(sh.Name) < sMenu Then sh.Columns.Hidden = False Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(1, "IV").End(xlToLeft)) For Each cell In rng If IsDate(cell.Value) Then If cell.Value rngDate.Value Then cell.EntireColumn.Hidden = True End If End If Next End If Next End If End Sub If you want something different, you would have to define automatically. -- Regards, Tom Ogilvy "Skornia115" wrote: Thanks Tom. If I want this to occur automatically, is there anything specific I need to do with the syntax? Tom Ogilvy wrote: Change sMenu to hold the name of the Menu sheet and rngDate to refer to the cell containing the date. Sub Hidecolumns() Dim sh as worksheet, rng as Range, cell as Range Dim sMenu as String, rngDate as Range sMenu = lcase("menu") set rngDate = worksheets(sMenu").Range("B9") for each sh in Worksheets if lcase(sh.Name) < sMenu then sh.Columns.Hidden = False set rng = sh.Range(sh.Cells(1,1),sh.Cells(1,"IV").End(xltoLe ft)) for each cell in rng if isdate(cell).Value then if cell.Value rngDate.Value then cell.EntireColumn.Hidden = True end if end if next end if Next End Sub -- Regards, Tom Ogilvy "Skornia115" wrote: New to VBA and need help. I am trying to hide columns based on a condition. My workbook has numerous worksheets. Each worksheet contains data for a specific "account" and each column within the "account" worksheet has month end data. Therefore row 1 of each worksheet contains month-end values and each row below has various data for that specific "account." Furthermore, the first worksheet, which basically serves as a "menu", has a cell with a specific date. What I would like to do is write a macro in each worksheet representing an "account" that hides all columns in the "account" worksheets that have not occurred yet. (i.e. If the date on the menu worksheet is 3/31/05, I would like all columns that have month-end values exceeding 3/31/05 to be hidden). Any suggestions? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Columns Based on Condition
Based on my understanding of what you want to do and my assumption that it
would only need to run if you change the date in B9 on the sheet menu, Just place it in the menu sheet code module. -- Regards, Tom Ogilvy "Skornia115" wrote: So all I need to do is paste the syntax exactly as it is into each worksheet, or do I need to change how the Menu worksheet is referenced in the syntax. Also, which worksheets do I paste this in....the "menu" or each "account". Tom Ogilvy wrote: Also, you have to edit the cell/change the date in cell B9 on sheet menu to make it fire. -- Regards, Tom Ogilvy "Skornia115" wrote: I have tried pasting this in with no luck. I just want to make sure I am doing everything correctly. The name of the menu worksheet is Menu and the cell within the Menu worksheet I want referenced is B9. This is what I have: Private Sub Worksheet_Change(ByVal Target As Range) Dim sh As Worksheet, rng As Range, cell As Range Dim Menu As String, rngDate As Range Menu = Me.Name Set rngDate = Worksheets(Menu).Range("B9") If Target.Address = rngDate.Address Then For Each sh In Worksheets If LCase(sh.Name) < Menu Then sh.Columns.Hidden = False Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(1, "IV").End(xlToLeft)) For Each cell In rng If IsDate(cell.Value) Then If cell.Value rngDate.Value Then cell.EntireColumn.Hidden = True End If End If Next End If Next End If End Sub Thanks for your patience. Tom Ogilvy wrote: I see there were a couple of typos in the original, but I would see it firing only when you change the date in the menu sheet - my interpretation of automatically. right click on the sheet tab of that sheet and select view code, then put in code like this in the resulting module: Private Sub Worksheet_Change(ByVal Target As Range) Dim sh As Worksheet, rng As Range, cell As Range Dim sMenu As String, rngDate As Range sMenu = Me.Name Set rngDate = Worksheets(sMenu).Range("B9") If Target.Address = rngDate.Address Then For Each sh In Worksheets If LCase(sh.Name) < sMenu Then sh.Columns.Hidden = False Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(1, "IV").End(xlToLeft)) For Each cell In rng If IsDate(cell.Value) Then If cell.Value rngDate.Value Then cell.EntireColumn.Hidden = True End If End If Next End If Next End If End Sub If you want something different, you would have to define automatically. -- Regards, Tom Ogilvy "Skornia115" wrote: Thanks Tom. If I want this to occur automatically, is there anything specific I need to do with the syntax? Tom Ogilvy wrote: Change sMenu to hold the name of the Menu sheet and rngDate to refer to the cell containing the date. Sub Hidecolumns() Dim sh as worksheet, rng as Range, cell as Range Dim sMenu as String, rngDate as Range sMenu = lcase("menu") set rngDate = worksheets(sMenu").Range("B9") for each sh in Worksheets if lcase(sh.Name) < sMenu then sh.Columns.Hidden = False set rng = sh.Range(sh.Cells(1,1),sh.Cells(1,"IV").End(xltoLe ft)) for each cell in rng if isdate(cell).Value then if cell.Value rngDate.Value then cell.EntireColumn.Hidden = True end if end if next end if Next End Sub -- Regards, Tom Ogilvy "Skornia115" wrote: New to VBA and need help. I am trying to hide columns based on a condition. My workbook has numerous worksheets. Each worksheet contains data for a specific "account" and each column within the "account" worksheet has month end data. Therefore row 1 of each worksheet contains month-end values and each row below has various data for that specific "account." Furthermore, the first worksheet, which basically serves as a "menu", has a cell with a specific date. What I would like to do is write a macro in each worksheet representing an "account" that hides all columns in the "account" worksheets that have not occurred yet. (i.e. If the date on the menu worksheet is 3/31/05, I would like all columns that have month-end values exceeding 3/31/05 to be hidden). Any suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide Columns Based on Condition | Excel Programming | |||
How do I hide a command button based on a condition? | Excel Programming | |||
Hide rows based on a condition | Excel Programming | |||
Hide a row based on one cell's condition | Excel Worksheet Functions | |||
How do I hide a column based on a condition (option not available. | Excel Worksheet Functions |