Hiding Columns based on date on on another tab

[clh14_96]

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 this macro with another, one do I need to include the bits in purple in the first line: Private Sub Worksheet_Change(ByVal Target As Range)

Private Sub Worksheet_Change(ByVal Target As Range)

Dim xCell As Range
If Target.Address = Range("D2").Address Then Exit Sub
Application.ScreenUpdating = False
For Each xCell In Range("R1:ZA1")
xCell.EntireColumn.Hidden = (xCell.Value < Target.Value)
Next
Application.ScreenUpdating = True

End Sub

Thanks

[#2]

@clh14_96 

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:

1. Reference the Date Cell:

   • To reference the date cell on Sheet2, use the following syntax:
     Excel

     `Sheet2.Range("B2").Value`

     Replace B2 with the actual cell address containing the date.

2. 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.

3. 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

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!

[#3]

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

[#4]

@clh14_96 

To ease our communications, give us the name of whatever this worksheet is and also the name of the "different sheet". In the absence of such info, I will call them Sheet1 and Sheet2, respectively.

It appears that you have a misunderstanding of certain things:

1. Worksheet_Change is not a macro; it is an event handler. (Both are procedures that contain VBA code.) An event handler is code that is executed automatically by Excel when its event type (e.g., cell content change) occurs for its object (e.g., a specific worksheet). In this case that is when cell content is changed for one or more cells on Sheet1. So you will not be "combining this macro with another". More on this below. (There can be zero or one worksheet event handler per worksheet per event type; each worksheet can have its own event handler for an event type, containing either identical or different code. When you make a copy of a worksheet, you get a copy of its event handlers.) 
2. Simply put, yes, the Worksheet_Change event handler definition must be followed by "(ByVal Target As Range)". Target represents which cell or cells have been modified, and that's the mechanism by which VBA code can take action specific to the changed cells. (OK, technically, you can change the parameter name Target to a different name, e.g., to CellsThatChanged, but that may be wasted effort, as there is no functional difference.)
3. The scope of the Worksheet_Change event handler can be changed from Private to Public, but any code you write to invoke it would have to provide the required parameter, and it would probably lead to insane coding.
4. Each Worksheet object (it is implicitly "Me" in worksheet event handlers, referring to the Worksheet whose code module contains the event handler) has a Range property that this code is referencing (so explicitly, it's "Me.Range(…"). So yes, "D2" is a cell on Sheet1. And Target represents only a cell (or set of cells) on Sheet1.

It does not make sense that you want the address of cell D2 on another worksheet. If you want the value from that cell, you can use a reference such as Sheets("Sheet2").Range("D2").Value. Performance note: If you are going to use that retrieved value repeatedly, capture it into a local variable (defined As Variant, if you don't have confidence the value will be a more-specific type), and use the variable in subsequent code.

OTOH, if you are trying to ignore changes to cell D2 on Sheet1, the syntax might be

If Target.Address = "$D$2" Then Exit Sub
'or
If Target.Address(False, False) = "D2" Then Exit Sub

The problem with that code is... (Remember from above: The event handler is executed when one or more cells are modified.) The user can (for example) modify content in both cells C2 and D2 with a single action, in which case Target.Address will be $C$2 (not $D$2). Code can be written to skip processing of changes to D2 regardless, but I won't go into that without clarification of what you intend your statement to accomplish.

Regarding "combining" code, you may instead need a custom, separate procedure of Public scope that can be called from each Worksheet_Change event handler. Such procedure would go into a (standard) code module, the same type of code module where macros are stored.  But again, I won't go into details without clarification of what you intend your code to accomplish.