excelvbadatepicker

Excel Date Picker Format

I need to correct the date format in a date picker based on this tutorial: https://www.youtube.com/watch?v=CkNRt8rDRiY

Sub buildCalendar()

    Dim iYear As Integer
    Dim iMonth As Integer
    
    Dim startOfMonth As Date
    Dim trackingDate As Date
    
    Dim iStartofMonthDay As Integer
    Dim cDay As Control
    
    If VBA.IsDate(ActiveCell.Value) Then
        
        trackingDate = Format(ActiveCell.Value, "mm-dd-yy")

I added format here and it seems to pick up the correct month in the month label in the userform.

    Else
    
        trackingDate = Now()
        
    End If
    
    iYear = VBA.Year(trackingDate)
    iMonth = VBA.Month(trackingDate)
    
    UserForm1.Controls("lblmonth").Caption = VBA.MonthName(iMonth, True)
    UserForm1.Controls("lblyear").Caption = iYear
    
    startOfMonth = VBA.DateSerial(iYear, iMonth, 1)
    iStartofMonthDay = VBA.Weekday(startOfMonth, vbSunday)
    
    trackingDate = DateAdd("d", -iStartofMonthDay + 1, startOfMonth)

    For i = 1 To 42
    
        Set cDay = UserForm1.Controls("day" & i)
        cDay.Caption = VBA.Day(trackingDate)
        cDay.Tag = trackingDate
        
        If VBA.Month(trackingDate) <> iMonth Then cDay.ForeColor = 8421504
        
        trackingDate = VBA.DateAdd("d", 1, trackingDate)
    
    Next i

The click command on the date labels triggers the following code to paste the selected date in the date picker to the active cell.

Sub dayClick(i As Integer)
    ActiveCell.Value = Me.Controls("day" & i).Tag
    Unload Me
End Sub

Private Sub day1_Click(): dayClick (1): End Sub
Private Sub day2_Click(): dayClick (2): End Sub
Private Sub day3_Click(): dayClick (3): End Sub
Private Sub day4_Click(): dayClick (4): End Sub
Private Sub day5_Click(): dayClick (5): End Sub
Private Sub day6_Click(): dayClick (6): End Sub
Private Sub day7_Click(): dayClick (7): End Sub
Private Sub day8_Click(): dayClick (8): End Sub
Private Sub day9_Click(): dayClick (9): End Sub
Private Sub day10_Click(): dayClick (10): End Sub

Dates with days between 1 to 12 (e.g. Sept. 1 to Sept. 12) appear as mm/dd/yy in format after clicking on the date on the date picker.

However, dates with days > 12 (13 to 31) appear as dd/mm/yy format.
I clicked on Sept 13 here, but format changed to dd/mm/yy (13/09/25).

Solution

  • Change this

    trackingDate = Format(ActiveCell.Value, "mm-dd-yy")

    to

    trackingDate = ActiveCell.Value 'see comment above

    and this

    cDay.Tag = trackingDate

    to

    cDay.Tag = Format(trackingDate, "yyyy-mm-dd")

    Using an unambiguous date format like yyyy-mm-dd for the Tag property helps you avoid unintentional swapping of month/day due to local date format differences.

    Also maybe do this ActiveCell.Value = CDate(Me.Controls("day" & i).Tag)

    FYI the approach in the example you're following is not too great, but the changes above should help.