VBA Conditional Statement That Functions Based on Today's Date

D

default_name

Board Regular
Joined
May 16, 2018
Messages
180
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
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
VBA Code: 
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.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
Not tested. Try:
VBA Code: 
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
 
Upvote 0
Cubist said:
Not tested. Try:
VBA Code: 
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
Click to expand...

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?
 
Upvote 0
How about this?
VBA Code: 
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
 
Upvote 0
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
 
Upvote 0
default_name said:
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
Click to expand...
Are the ranges actual dates? Can you post what they look like?
 
Upvote 0
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/20244/25/20244/26/20244/27/20244/28/20244/29/20244/30/20245/1/20245/2/20245/3/20245/4/20245/5/20245/6/20245/7/2024...[content][content]
2​
[content]...[content][content][content][content][content][content][content][content][content][content][content][content][content][content]...[content][content]
 
Upvote 0
This seems to work for me. Try a different approach.
VBA Code: 
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
 
Upvote 1
I get an error with that one
Run-time error '13':
Type mismatch

Debugger has this line of code highlighted:
VBA Code: 
If DateValue(cell.Value) = currentDate Then
 
Upvote 0
default_name said:
I get an error with that one
Run-time error '13':
Type mismatch

Debugger has this line of code highlighted:
VBA Code: 
If DateValue(cell.Value) = currentDate Then
Click to expand...
Are your date cells appear left- aligned or right-aligned?
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Help - Existing VBA routine to hide one column hides three?
Replies
4
Views
720
EssKayKay
E
H
  • Question
Hide Columns VBA - refusing to hide random columns
Replies
2
Views
279
hmltnangel
H
D
  • Solved
Open Certain Worksheet Based on Current Date
Replies
8
Views
330
NdNoviceHlp
NdNoviceHlp
F
  • Solved
Highlight a column if it is today's date
Replies
4
Views
187
falcios
F
Z
  • Solved
VBA Code to Hide columns in Specific Intervals
2
Replies
11
Views
436
jolivanes
jolivanes

Forum statistics

Threads
1,216,028
Messages
6,128,395
Members
449,446
Latest member
CodeCybear

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top