Forum Discussion
clh14_96
Feb 22, 2024Copper Contributor
Hiding Columns based on date on on another tab
Hi all, I've tried to play around with this as it should be fairly easy but I can't seem to change the cell D2 to a cell on a different sheet? The other question I have is that if I am combining...
smylbugti222gmailcom
Feb 23, 2024Iron Contributor
I'd be glad to help you with hiding columns based on a date on another tab in Excel. Here's a combined approach addressing the issues and incorporating improvements:
Understanding Your Requirements:
- You want to hide columns based on a date on a different sheet (Sheet2) but are having trouble referencing the cell on that sheet.
- You're unsure if you need to repeat the Private Sub Worksheet_Change line when combining this macro with another.
Solution:
Reference the Date Cell:
- To reference the date cell on Sheet2, use the following syntax:Replace B2 with the actual cell address containing the date.Excel
`Sheet2.Range("B2").Value`
- To reference the date cell on Sheet2, use the following syntax:
Combine Macros:
- If you have another macro that also needs to run when a cell changes, you can combine them into a single Worksheet_Change event handler. However, you don't necessarily need to repeat the Private Sub Worksheet_Change line. You can have multiple subroutines within the same event handler.
Improved Macro:
- Here's the revised macro incorporating the changes:Excel
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address <> "D2" Then Exit Sub 'Skip if not cell D2 Dim dateCell As Range Set dateCell = Sheet2.Range("B2") 'Reference date cell on Sheet2 Application.ScreenUpdating = False For Each xCell In Range("R1:ZA1") xCell.EntireColumn.Hidden = (xCell.Value < dateCell.Value) Next Application.ScreenUpdating = True End Sub
- Here's the revised macro incorporating the changes:
Explanation:
- The macro checks if the changed cell is D2. If not, it exits.
- It sets dateCell to the appropriate cell on Sheet2.
- It hides columns based on the comparison between xCell.Value and dateCell.Value.
- It enables screen updating at the end.
Additional Tips:
- Consider using absolute cell references for the date cell on Sheet2 if it's likely to be moved.
- You can modify the range ("R1:ZA1") to hide/unhide specific columns based on your needs.
- For more complex logic, you can use conditional statements or other VBA functions within the loop.
Remember:
- Replace B2 with the actual cell address containing the date on Sheet2.
- If you need to combine this macro with another, ensure both sets of code are within the same Private Sub Worksheet_Change event handler.
I hope this comprehensive response helps you achieve your desired functionality!
- smylbugti222gmailcomFeb 23, 2024Iron ContributorHere's the revised macro incorporating the changes:
Excel
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "D2" Then Exit Sub 'Skip if not cell D2
Dim dateCell As Range
Set dateCell = Sheet2.Range("B2") 'Reference date cell on Sheet2
Application.ScreenUpdating = False
For Each xCell In Range("R1:ZA1")
xCell.EntireColumn.Hidden = (xCell.Value < dateCell.Value)
Next
Application.ScreenUpdating = True
End Sub