add a date picker to my text box

Question

ive looked through other posts and I can't quite figure out the day picker for my user form. Any ideas or help would be appreciated. The input date box is currently just a text box to write in the date in the format dd-mmm-yy. As ive been testing the form it's pretty hard to type the date in like this. Since I built the form for someone else I would like it to be a little easier. Thanks

Private Sub UserForm_Initialize()

'Empty TextBox
DateTextBox.Value = ""
CmbBox_ACFT.Value = ""
JCNTextBox.Value = ""
TEMSTextBox.Value = ""
DMGTextBox.Value = ""
MXNTextBox.Value = ""
CmbBox_POS.Clear
CmbBox_Shift.Clear

With CmbBox_Shift
CmbBox_Shift.AddItem "DAYS"
CmbBox_Shift.AddItem "SWINGS"
CmbBox_Shift.AddItem "MIDS"
End With

With CmbBox_POS
CmbBox_POS.AddItem "1"
CmbBox_POS.AddItem "2"
CmbBox_POS.AddItem "APU"
End With

With CmbBox_ACFT
CmbBox_ACFT.AddItem "123"
CmbBox_ACFT.AddItem "456"
CmbBox_ACFT.AddItem "789"
CmbBox_ACFT.AddItem "012"
CmbBox_ACFT.AddItem "782"
End With

Option_Yes.Value = False
DateTextBox.SetFocus

End Sub
Sub OKButton_Click()

Dim emptyRow As Long

'Make Sheet1 active
Sheet1.Activate

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Transfer information
Cells(emptyRow, 1).Value = DateTextBox.Value 'need a date picker to help minimize errors. In dd-mmm-yy format.  
Cells(emptyRow, 2).Value = CmbBox_ACFT.Value
Cells(emptyRow, 3).Value = CmbBox_Shift.Value
Cells(emptyRow, 4).Value = CmbBox_POS.Value
Cells(emptyRow, 5).Value = MXNTextBox.Value
Cells(emptyRow, 6).Value = JCNTextBox.Value
Cells(emptyRow, 7).Value = TEMSTextBox.Value
'Cells(emptyRow, 8).Value = DinnerComboBox.Value
Cells(emptyRow, 9).Value = DMGTextBox.Value

If Option_Yes.Value = True Then
Cells(emptyRow, 8).Value = "Yes"
Else
Cells(emptyRow, 8).Value = "No"
End If

 With ActiveSheet.UsedRange.Borders
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
End With

End Sub

1

That's pretty neat, Thanks! – Gilmer Feb 07 '18 at 21:01

score 0 · Answer 1 · answered Feb 07 '18 at 20:37

it is entirely possible the date picker control may not be installed. I did find a related post that explains how to ensure it is installed ("Monthview"). You have to install it.

First close Excel.
download MSCOMCT2.cab from http://support.microsoft.com/kb/297381 and extract the two files inside the zip file.
Right click the .inf file and click "Install".
Next, open Windows Explorer and find the directory C:\Windows\System32.
Locate the extracted .ocx file in C:\Windows\System32 and move it to C:\Windows\sysWOW64.
Click Start > Search > "cmd.exe", right-click the found item and click "Run as Administrator"
At the command prompt type "regsvr32 C:\Windows\sysWOW64\MSCOMCT2.ocx" and hit enter
Open Excel and fire up the VB Editor
Click Tools > References and choose "Microsoft Windows Common Controls 2-6.0 (sp6)
Select any userform in the VB project, click Tools > Additional Controls
Select "Microsoft MOnthview Control 6.0 (SP6)
Use 'DatePicker' control in the VBA user form.

From How to Add Date Picker To VBA UserForm

@Gilmer, can you please come back and update this question? If my answer helped, please mark it so it might help others in a similar situation. Thank you! — bbcompent1, Nov 21 '18 at 12:18

score 0 · Answer 2 · answered Oct 19 '22 at 17:45

For few versions MSCOMCT2.cab isn't working You can try out the following script

Option Explicit
Dim ThisDay As Date
Dim ThisYear, ThisMth As Date
Dim CreateCal As Boolean
Dim i As Integer



Private Sub D1_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D1.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D1.ControlTipText
End If
  
End Sub

Private Sub D10_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D10.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D10.ControlTipText
End If

End Sub

Private Sub D11_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D11.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D11.ControlTipText
End If

End Sub

Private Sub D12_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D12.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D12.ControlTipText
End If

End Sub

Private Sub D13_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D13.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D13.ControlTipText
End If

End Sub

Private Sub D14_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D14.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D14.ControlTipText
End If

End Sub

Private Sub D15_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D15.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D15.ControlTipText
End If

End Sub

Private Sub D16_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D16.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D16.ControlTipText
End If

End Sub

Private Sub D17_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D17.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D17.ControlTipText
End If

End Sub

Private Sub D18_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D18.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D18.ControlTipText
End If

End Sub

Private Sub D19_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D19.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D19.ControlTipText
End If

End Sub

Private Sub D2_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D2.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D2.ControlTipText
End If

End Sub

Private Sub D20_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D20.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D20.ControlTipText
End If

End Sub

Private Sub D21_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D21.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D21.ControlTipText
End If

End Sub

Private Sub D22_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D22.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D22.ControlTipText
End If

End Sub

Private Sub D23_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D23.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D23.ControlTipText
End If

End Sub

Private Sub D24_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D24.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D24.ControlTipText
End If

End Sub

Private Sub D25_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D25.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D25.ControlTipText
End If

End Sub

Private Sub D26_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D26.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D26.ControlTipText
End If

End Sub

Private Sub D27_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D27.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D27.ControlTipText
End If

End Sub

Private Sub D28_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D28.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D28.ControlTipText
End If

End Sub

Private Sub D29_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D29.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D29.ControlTipText
End If

End Sub

Private Sub D3_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D3.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D3.ControlTipText
End If

End Sub

Private Sub D30_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D30.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D30.ControlTipText
End If

End Sub

Private Sub D31_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D31.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D31.ControlTipText
End If

End Sub

Private Sub D32_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D32.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D32.ControlTipText
End If

End Sub

Private Sub D33_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D33.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D33.ControlTipText
End If

End Sub

Private Sub D34_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D34.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D34.ControlTipText
End If

End Sub

Private Sub D35_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D35.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D35.ControlTipText
End If

End Sub

Private Sub D36_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D36.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D36.ControlTipText
End If

End Sub

Private Sub D37_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D37.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D37.ControlTipText
End If

End Sub

Private Sub D38_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D38.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D38.ControlTipText
End If

End Sub

Private Sub D39_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D39.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D39.ControlTipText
End If

End Sub

Private Sub D4_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D4.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D4.ControlTipText
End If

End Sub

Private Sub D40_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D40.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D40.ControlTipText
End If

End Sub

Private Sub D41_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D41.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D41.ControlTipText
End If

End Sub

Private Sub D42_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D42.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D42.ControlTipText
End If

End Sub

Private Sub D5_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D5.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D5.ControlTipText
End If

End Sub

Private Sub D6_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D6.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D6.ControlTipText
End If

End Sub

Private Sub D7_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D7.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D7.ControlTipText
End If

End Sub

Private Sub D8_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D8.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D8.ControlTipText
End If

End Sub

Private Sub D9_Click()

If CBO_AddTime.Value = True Then
    Selection.Value = Me.D9.ControlTipText & ":" & Time()
ElseIf CBO_AddTime.Value = False Then
    Selection.Value = Me.D9.ControlTipText
End If

End Sub



Private Sub Frame1_Click()

End Sub

Private Sub UserForm_Initialize()

Application.EnableEvents = False

ThisDay = Date
ThisMth = Format(ThisDay, "MM")
ThisYear = Format(ThisDay, "YYYY")

For i = 1 To 12
CB_Month.AddItem Format(DateSerial(Year(Date), Month(Date) + i, 0), "mm")
Next
CB_Month.ListIndex = Format(Date, "mm") - Format(Date, "mm")

For i = -10 To 50
If i = 1 Then CB_Year.AddItem Format((ThisDay), "yyyy") Else CB_Year.AddItem _
Format((DateAdd("yyyy", (i - 1), ThisDay)), "yyyy")
Next
CB_Year.ListIndex = 11

CreateCal = True
Call Build_Calendar
Application.EnableEvents = True

LBLTime.Caption = Time()
LBLDate.Caption = Format(Date)

If CreateCal = True Then
UserForm1.Caption = CB_Month.Value & " " & CB_Year.Value
End If
 

End Sub

Private Sub Build_Calendar()
For i = 1 To 42

If i < Weekday((CB_Month.Value) & "/1/" & (CB_Year.Value)) Then
Controls("D" & (i)).Caption = Format(DateAdd("d", (i - Weekday((CB_Month.Value) & "/1/" & (CB_Year.Value))), _
((CB_Month.Value) & "/1/" & (CB_Year.Value))), "d")

Controls("D" & (i)).ControlTipText = Format(DateAdd("d", (i - Weekday((CB_Month.Value) & "/1/" & (CB_Year.Value))), _
((CB_Month.Value) & "/1/" & (CB_Year.Value))), "d/mm/yyyy")


 ElseIf i >= Weekday((CB_Month.Value) & "/1/" & (CB_Year.Value)) Then
 Controls("D" & (i)).Caption = Format(DateAdd("d", (i - Weekday((CB_Month.Value) & "/1/" & (CB_Year.Value))), _
 ((CB_Month.Value) & "/1/" & (CB_Year.Value))), "d")
 
 Controls("D" & (i)).ControlTipText = Format(DateAdd("d", (i - Weekday((CB_Month.Value) & "/1/" & (CB_Year.Value))), _
 ((CB_Month.Value) & "/1/" & (CB_Year.Value))), "d/mm/yyyy")

End If

Next


End Sub

add a date picker to my text box

2 Answers2