VBA Conditional Statement That Functions Based on Today's Date

[default_name]

Hey everyone!

I am currently trying to implement a function into a workbook that has many worksheets.
Each worksheet has individual dates stored in cells E1:R1.

• When the workbook is opened I want the code to immediately run (so I put it in the Workbook_Open sub).
• The code looks through each worksheet.
  • If today's date is found on one of the worksheets (in E1:R1), then I would like it to then check for the following conditions:
    • If today's date appears between E1:K1 of that sheet, then I would like to hide column U
    • If today's date appears between L1:R1 of that sheet, then I would like to hide column T
  • If today's date is not found in E1:R1 of a worksheet, then I would like to hide both columns T and U and move on to the next sheet.

I am thinking that this might be done using an If/ElseIf/Else statement, but I am sure that I am overthinking it.
I have tried to play around with it and I feel like I am getting nowhere. Coding really isn't a strong trait I possess (as you will see below).

My garbage attempt

Private Sub Workbook_Open()
Dim currentDate As Date
currentDate = Date

For Each ws In Worksheets
With ws

   If Date = ws.Range("E1:K1").Find(Date, , , xlWhole, , , , , False)
   Then
      Columns("T:T").Select
      Selection.EntireColumn.Hidden = False
      Columns("U:U").Select
      Selection.EntireColumn.Hidden = True
   ElseIf Date = ws.Range("L1:R1").Find(Date, , , xlWhole, , , , , False)
      Columns("T:T").Select
      Selection.EntireColumn.Hidden = True
      Columns("U:U").Select
      Selection.EntireColumn.Hidden = False
   Else
      Columns("T:T").Select
      Selection.EntireColumn.Hidden = True
      Columns("U:U").Select
      Selection.EntireColumn.Hidden = True
   End If

End With
Next ws
End Sub

I hope that my request makes sense.
If not, please let me know and I can try to clarify.

 

[Cubist]

Not tested. Try:

Private Sub Workbook_Open()
    Dim currentDate As Date
    Dim ws As Worksheet
    Dim dateRange As Range
    Dim hideT As Boolean
    Dim hideU As Boolean
    
    currentDate = Date

    For Each ws In Worksheets
        hideT = True
        hideU = True
        
        ' Look for today's date in range E1:R1 of the current worksheet
        Set dateRange = ws.Range("E1:R1").Find(currentDate, , xlValues, xlWhole)
        
        If Not dateRange Is Nothing Then ' Today's date found in E1:R1
            If Not Intersect(dateRange, ws.Range("E1:K1")) Is Nothing Then ' Today's date in E1:K1
                hideU = False
            ElseIf Not Intersect(dateRange, ws.Range("L1:R1")) Is Nothing Then ' Today's date in L1:R1
                hideT = False
            End If
        End If
        
        ' Hide/show columns based on conditions
        ws.Columns("T:T").EntireColumn.Hidden = hideT
        ws.Columns("U:U").EntireColumn.Hidden = hideU
    Next ws
End Sub

 

[default_name]

Not tested. Try:

Private Sub Workbook_Open()
    Dim currentDate As Date
    Dim ws As Worksheet
    Dim dateRange As Range
    Dim hideT As Boolean
    Dim hideU As Boolean
   
    currentDate = Date

    For Each ws In Worksheets
        hideT = True
        hideU = True
       
        ' Look for today's date in range E1:R1 of the current worksheet
        Set dateRange = ws.Range("E1:R1").Find(currentDate, , xlValues, xlWhole)
       
        If Not dateRange Is Nothing Then ' Today's date found in E1:R1
            If Not Intersect(dateRange, ws.Range("E1:K1")) Is Nothing Then ' Today's date in E1:K1
                hideU = False
            ElseIf Not Intersect(dateRange, ws.Range("L1:R1")) Is Nothing Then ' Today's date in L1:R1
                hideT = False
            End If
        End If
       
        ' Hide/show columns based on conditions
        ws.Columns("T:T").EntireColumn.Hidden = hideT
        ws.Columns("U:U").EntireColumn.Hidden = hideU
    Next ws
End Sub

Thanks for your help Cubist!
All of the columns T and U in the workbook are hidden, but the conditional portion doesnt seem to work/do anything.
No errors though, so on the right track...thoughts?

 

[Cubist]

How about this?

Private Sub Workbook_Open()
    Dim currentDate As Date
    Dim ws As Worksheet
    Dim dateRange As Range
    Dim hideT As Boolean
    Dim hideU As Boolean
   
    currentDate = Date

    For Each ws In Worksheets
        ws.Columns.Hidden = False
       
        hideT = True
        hideU = True
       
        Set dateRange = ws.Range("E1:R1").Find(currentDate, , xlValues, xlWhole)
       
        If Not dateRange Is Nothing Then
            If Not Intersect(dateRange, ws.Range("E1:K1")) Is Nothing Then
                hideU = False
            End If
           
            If Not Intersect(dateRange, ws.Range("L1:R1")) Is Nothing Then
                hideT = False
            End If
        End If
       
        ws.Columns("T:T").EntireColumn.Hidden = hideT
        ws.Columns("U:U").EntireColumn.Hidden = hideU
    Next ws
End Sub

 

[default_name]

That code will hide columns T:U in all worksheets.
It doesn't seem to be picking up the portion that works with today's date though

 

[Cubist]

That code will hide columns T:U in all worksheets.
It doesn't seem to be picking up the portion that works with today's date though

Are the ranges actual dates? Can you post what they look like?

 

[default_name]

I cant screenshot/post the exact contents of the workbook, but here is an example of the date setup:

Today's date is 5/2/2024, which falls between L1:R1, so column T should be hidden and column U should be visible

A​

...

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

R​

...

T​

U​

1​

[content]

...

4/24/2024

4/25/2024

4/26/2024

4/27/2024

4/28/2024

4/29/2024

4/30/2024

5/1/2024

5/2/2024

5/3/2024

5/4/2024

5/5/2024

5/6/2024

5/7/2024

...

[content]

[content]

2​

[content]

...

[content]

[content]

[content]

[content]

[content]

[content]

[content]

[content]

[content]

[content]

[content]

[content]

[content]

[content]

...

[content]

[content]

 

[Cubist]

This seems to work for me. Try a different approach.

Private Sub Workbook_Open()
    Dim currentDate As Date
    Dim ws As Worksheet
    Dim hideT As Boolean
    Dim hideU As Boolean
    Dim cell As Range
    Dim dateFoundInE As Boolean
    Dim dateFoundInL As Boolean

    currentDate = Date

    For Each ws In Worksheets
        hideT = True
        hideU = True
        dateFoundInE = False
        dateFoundInL = False

        For Each cell In ws.Range("E1:K1")
            If DateValue(cell.Value) = currentDate Then
                dateFoundInE = True
                Exit For
            End If
        Next cell
        For Each cell In ws.Range("L1:R1")
            If DateValue(cell.Value) = currentDate Then
                dateFoundInL = True
                Exit For
            End If
        Next cell

        hideT = Not dateFoundInL
        hideU = Not dateFoundInE
        ws.Columns("T:T").Hidden = hideT
        ws.Columns("U:U").Hidden = hideU
    Next ws
End Sub

 

[default_name]

I get an error with that one
Run-time error '13':
Type mismatch

Debugger has this line of code highlighted:

If DateValue(cell.Value) = currentDate Then

 

[Cubist]

I get an error with that one
Run-time error '13':
Type mismatch

Debugger has this line of code highlighted:

If DateValue(cell.Value) = currentDate Then

Are your date cells appear left- aligned or right-aligned?