VBA for hiding columns based on inputted date

[AlexCHI]

I am using Excel 2007 to make an attendance sheet with cell "S9" starting as January 01, 2017 through "NS9" as December 31, 2017. I am hoping that if the user of the form inputs a date (1/03/2017 as an example) in a specified cell (say B3) then the columns with other dates would "temporarily" hide and showing just the column with the user-inputted date. In this example, column U with 1/03/2017 would show while the columns with other 364 dates would hide. And if the specified cell (i.e. B3) is left blank then all columns showing 365 dates would appear again.

I figured this would save time to scroll through many columns before getting to the date in question. Really appreciate if anyone would help me with the VBA code to accomplish this! Thank you in advance.

 

[AlexCHI]

The existing code expects to find a date (or nothing) in cell B3. If you want to be able to input multiple dates into that cell then that cell will contain nothing or a date or a text string representing multiple dates.
1. How do you envisage multiple dates being entered? eg With a space between each date, or a comma, or a comma space or Alt+Enter etc.
2. How accurate do you think the user will be with entering those dates with a strict format?

Hi Peter,

Thank you for your reply and bringing up these questions. You are right, I am hoping cell B3 can be a specific date, or a text string representing multiple dates, or containing nothing. In an ideal setting, I would hope the user(s) will stick to the format that was laid down when inputting multiple dates in cell B3 but I can't anticipate that would be the case all the time. As the result, is it possible to set up the code in a way that the value in cell B3 (in the case of a text string representing multiple dates) that it will accept multiple formats (i.e., with a comma and a comma space)?

 

[Peter_SSs]

For entering dates with a comma or a comma space then try this. You would need to incorporate it with the other code once you are happy that it works for you.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim OneDate As Variant
  Dim InvalidDates As String
  
  If Not Intersect(Target, Range("B3")) Is Nothing Then
    Application.ScreenUpdating = False
    If IsEmpty(Range("B3").Value) Then
      Columns("S:SN").Hidden = False
    Else
      Columns("S:SN").Hidden = True
      For Each OneDate In Split(Replace(Range("B3").Value, ", ", ","), ",")
        If IsDate(OneDate) Then
          Columns("S").Offset(, CDate(OneDate) - Range("S9").Value).Hidden = False
        Else
          InvalidDates = InvalidDates & vbLf & OneDate
        End If
      Next OneDate
    End If
    Application.ScreenUpdating = True
    If Len(InvalidDates) > 0 Then MsgBox "Invalid Dates Entered:" & InvalidDates
  End If
End Sub

 

[AlexCHI]

For entering dates with a comma or a comma space then try this. You would need to incorporate it with the other code once you are happy that it works for you.

Wow, works perfectly! Thank you so much Peter for all your time and help. Hope one day I can be more proficient with excel- so many amazing things it can achieve. Learned so much in this one week since I joined this forum, greatly appreciated.

 

[Peter_SSs]

Wow, works perfectly! Thank you so much Peter for all your time and help.

Glad to help again.

Hope one day I can be more proficient with excel- so many amazing things it can achieve. Learned so much in this one week since I joined this forum, greatly appreciated.

Just keep loitering here and you will certainly pick up a lot.