First, the easy portion: myStartDate = FirstDOWinMonth(DateSerial(Year(Date), 7, 1), vbWednesday). Dateserial() looks for a year, month, and a day. I just changed the month to 7 (July). Second, why bother changing the caption if the caption would change next year. But if you wanted to, then you're going to have to find out why the code suggested didn't work for you. It worked fine for me. If I wanted to change those captions, I'd use captions like Week01, Week02, ..., Week52 (or live with Commandbutton1 through Commandbutton52). I guess it could make work designing the form a little easier. There was a mistake in the first version of the code I gave you And sometimes years can have 53 "weeks" in it based on the starting date.
Code:
Option Explicit
Private Sub UserForm_Initialize()
Dim iCtr As Long
Dim myStartDate As Date
Dim myDate As Date
myStartDate = FirstDOWinMonth(DateSerial(Year(Date), 7, 1), vbWednesday)
For iCtr = 1 To 53 '52 ???????
myDate = myStartDate + (7 * (iCtr - 1))
!not sure if you'd use this!
If myDate > DateSerial(Year(myStartDate) + 1, _
Month(myStartDate), Day(myStartDate)) Then
Me.Controls("commandbutton" & iCtr).Visible = False
End If
Me.Controls("commandbutton" & iCtr).Caption _
= Format(myDate, "ddd-mm/dd/yyyy")
Next iCtr
End Sub
Function FirstDOWinMonth(TheDate As Date, DOW As Integer) As Date
FirstDOWinMonth = Int((DateSerial(Year(TheDate), _
Month(TheDate), 1) + 6 - DOW) / 7) * 7 + DOW
End Function
Bookmarks