Hide Columns in Excel based on a Cell's Value

Is there a function or VB Macro language that would update a date as soon as the file is activated and from the date hide and unhide columns in excel.  Basically I need to set a report on a single tab in excel so that only the current day of the week should be active/unhidden to have information entered into it.
SheetExample.xlsx
NYANBCNY32Asked:
Who is Participating?
 
Martin LissOlder than dirtCommented:
Here you go. Please don't forget to accept my answer if it's correct.
Q-28585055--1-.xlsm
0
 
Michael FowlerSolutions ConsultantCommented:
This can be done in VBA but we will need a few more details

Is cell B2 to be updated with Todays date on open
Your example has dates in joined cells, are these the columns you are looking to hide
Do new headers need to be created manually or by the application

Another option here could also be to have a data entry sheet which OnSave inputs the data into a hidden worksheet.
0
 
NYANBCNY32Author Commented:
Yes, cell B2 will be the space where the date will be updated, so that the cells that are already on the sheet itself will then read and the day of the week that matches the date to unhide and the rest remain hidden.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Martin LissOlder than dirtCommented:
I added this code to the workbook. Since the workbook originally only had dates up to 12/21 I added a few days after that.

Private Sub Workbook_Open()
Dim lngCol As Long

With Sheets("Sheet1")
    For lngCol = 3 To ActiveSheet.UsedRange.Columns.Count Step 3
        If .Cells(1, lngCol) <> .Range("B2") Then
            Cells(1, lngCol).EntireColumn.Hidden = True
            Cells(1, lngCol + 1).EntireColumn.Hidden = True
            Cells(1, lngCol + 2).EntireColumn.Hidden = True
        End If
    Next
End With

End Sub

Open in new window

Q-28585055.xlsm
0
 
NYANBCNY32Author Commented:
Thank you Martin Liss for this, just one adjustment and it's my fault on how the sample was posted, the dates start at column E and are 6 columns combined, can the formula change to start at column E and keep all six columns, right now it only holds 3.  Thank you.
Q-28585055--1-.xlsm
0
 
NYANBCNY32Author Commented:
PERFECT! Thank you again for the help on this.
0
 
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.