Date Validation/Open Workbook VBA

Question

I have a workbook that I am trying to set up e-mail notifications for whenever the workbook is opened. One of the ways that I had intended to trigger the notifications is based upon date validation. I am trying to figure out a VBA solution for refreshing the date values of specific cells depending upon the days of the week. I have one line of 7 cells (each representing Sunday-Saturday) and am trying to figure out a sub that would populate the dates in those cells as they correspond with today's date. For example, if today = Thursday, the sub would know that TODAY()=05/21/2015 and that is the starting date which would go in cell 5, it could then figure out what the other dates are depending on their distance from 5... Is something like this doable? I had originally worked in a large amount of conditional formulas but it was getting messy trying to handle so many columns with formulas that would check and balance each other that this seemed like a good avenue to pursue...

I am trying to sketch out how the sub would need to look, but I have very little experience in how to reference dates in VBA. Any thoughts?

KKowalczyk KKowalczyk · Accepted Answer · 2015-05-21T16:04:30

Get this into Thisworkbook module

private sub workbook_open
Dim dayOfWeek As String
Dim i As Integer

dayOfWeek = Weekday(Date)

Select Case dayOfWeek

Case "1"
For i = 1 To 7
Cells(1, i).Value = Date + i - 1
Next i

Case "2"
For i = 1 To 7

Cells(1, i).Value = Date + i - 2
Next i
Case "3"

For i = 1 To 7
Cells(1, i).Value = Date + i - 3
Next i

Case "4"
For i = 1 To 7
Cells(1, i).Value = Date + i - 4
Next i

Case "5"
For i = 1 To 7
Cells(1, i).Value = Date + i - 5
Next i

Case "6"

For i = 1 To 7
Cells(1, i).Value = Date + i - 6
Next i

Case "7"

For i = 1 To 7
Cells(1, i).Value = Date + i - 7
Next i

End Select
End Sub

Date Validation/Open Workbook VBA

1 Answers