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

add a date picker to my text box

1 Answers1