Hiding Columns based on a Date value being greater that todays date

[dwpfreak]

I have searched the forum and despite similar questions with similar VBA solutions all of them I have tried but none actually worked.

I have a line of dates in a Leave sheet from cell b3 to ni3 which I wish to hide automatically.
I would prefer not to have a cell with todays data in, but would like it to read todays date from within the code and then hide all columns with date in row 3 B - NI to be hidden if date is before todays date.

I tried conditional format approach to limited success, and the other similar questions on here and those answers did not work.

Bit of a VBA novice so a simple solution and a copy and paste exercise would be awesome :D

Thanks all

 

[Joe4]

How many sheets are in this workbook?
Is the sheet named "Leave"?
When would you like this to run? It can be run automatically upon selecting the sheet, opening the file, etc.

 

[dwpfreak]

hi, sheet name 2023 (terrible I know) 2 sheets, 2nd named data. Opening file please

 

[Joe4]

Ok, then place this VBA code, exactly "as-is", in the "ThisWorkbook" module in the VB Editor.

Private Sub Workbook_Open()

    Dim cell As Range
    
    Application.ScreenUpdating = False
    
'   Select "2023" sheeet
    Sheets("2023").Activate
        
'   Unhide all columns
    Columns("B:NI").Hidden = False
    
'   Loop through all columns in row 3
    For Each cell In Range("B3:NI3")
'       Hide column if date in cell is before current date
        If cell.Value < Date Then
            cell.EntireColumn.Hidden = True
        End If
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

Then, as long as you enable VBA code/macros, whenever you open this file, the code should run automatically.

 

[dwpfreak]

did that, didn't work copied it straight in saved and closed and reopened. still visible?

 

[Joe4]

Are your values in cells B3:N13 on your "2023" sheets valid dates?
Are you sure Macros/VBA have been enabled?

Add this line in red to the code and try again:

Private Sub Workbook_Open()

    Dim cell As Range

    MsgBox "Code is running!"
  
    Application.ScreenUpdating = False
  
'   Select "2023" sheeet
    Sheets("2023").Activate
      
'   Unhide all columns
    Columns("B:NI").Hidden = False
  
'   Loop through all columns in row 3
    For Each cell In Range("B3:NI3")
'       Hide column if date in cell is before current date
        If cell.Value < Date Then
            cell.EntireColumn.Hidden = True
        End If
    Next cell
  
    Application.ScreenUpdating = True
  
End Sub

Now, if you save and close, and then re-open, you should get a MsgBox pop-up saying "Code is running".

If you do NOT get that message box, then you probably either:
- have placed this code in the wrong VBA module (it MUST be in the "ThisWorkbook" module in this workbook)
or
- you have not enabled VBA/Macros

If you get the Message Box, and the code does not seem to do anything, then you probably:
- have your dates entered in as text and not valid dates
or
- have none of the records meeting the criteria (none of the dates in B3:NI3 are prior to the current date)
or
- have it pointing to the wrong sheet (you said it should be the "2023" sheet, right?)

 

[dwpfreak]

hi thank you, I get the code is running message but the columns are still visible

 

[dwpfreak]

i have checked the data, they are formatted as dates. the dates run in row 3 b3 - ni3

 

[Joe4]

hi thank you, I get the code is running message but the columns are still visible

OK, did you confirm all the things I listed?

If you get the Message Box, and the code does not seem to do anything, then you probably:
- have your dates entered in as text and not valid dates
or
- have none of the records meeting the criteria (none of the dates in B3:NI3 are prior to the current date)
or
- have it pointing to the wrong sheet (you said it should be the "2023" sheet, right?)

i have checked the data, they are formatted as dates. the dates run in row 3 b3 - ni3

Note that just because the cell if formatted as a date doesn't mean that the entry was entered as a date.
What exactly do the entries look like?
If you change the format of those cells to some other date format, do the values shown appear to change?
If not, then you have text entries and not date entries, as formatting only affects date/numeric entries.

Another way to check is by entering a formula that checks one of these date cells, i.e.

=ISNUMBER(B3)

If the formula returns FALSE, you have a text entry, not a valid date entry!