Using Date picker in Visual Basic with a Userform

**RetiredPM2012** · 02-06-2021, 05:50 PM

What im wanting to do is this (There are 6 Datepickers in the workbook)

1. I want to be able to when i open a userform in excel i want to have the Date picker to default to the current date.

2. I want know in the code that im about to post what code i should use to make this happen and where to put it in the macro. This code is in the initialize part of the workbook.

3. Im going to attach the excel file also.

4.The Date picker i have used is the one that is in the tool box in the visual basic.

5. If you cant tell me how to do this with the toolbox please let me know how it can be done with my code that i have by adding more code or how ever.

Thanks

Private Sub UserForm_Initialize()
'Combobox1
With ComboBox1
.AddItem "JANUARY"
.AddItem "FEBUARY"
.AddItem "MARCH"
.AddItem "APRIL"
.AddItem "MAY"
.AddItem "JUNE"
.AddItem "JULY"
.AddItem "AUGUST"
.AddItem "SEPTEMBER"
.AddItem "OCTOBER"
.AddItem "NOVEMBER"
.AddItem "DECEMBER"
End With

'ComboBox2
With ComboBox2
.AddItem "JANUARY"
.AddItem "FEBUARY"
.AddItem "MARCH"
.AddItem "APRIL"
.AddItem "MAY"
.AddItem "JUNE"
.AddItem "JULY"
.AddItem "AUGUST"
.AddItem "SEPTEMBER"
.AddItem "OCTOBER"
.AddItem "NOVEMBER"
.AddItem "DECEMBER"
End With

'ComboBox3

With Sheets("OPTIONS PAGE")
Me.ComboBox3.List = .Range("C2", .Cells(.Rows.Count, "C").End(xlUp)).Value
End With

'ComboBox4
With ComboBox4
.AddItem "UTILITY"
.AddItem "RESALE"
.AddItem "INSURANCE"
.AddItem "SUPPLIES"
.AddItem "TAXES"
.AddItem "CO2"
.AddItem "LICENSE"
.AddItem "EQUIPMENT"
.AddItem "MAINTENACE"
.AddItem "MEMBERSHIP"
.AddItem "LABOR"
.AddItem "REPAIRS"
.AddItem "MERCHANT LIC"
End With

'Combobox5
With ComboBox5
.AddItem "JANUARY"
.AddItem "FEBUARY"
.AddItem "MARCH"
.AddItem "APRIL"
.AddItem "MAY"
.AddItem "JUNE"
.AddItem "JULY"
.AddItem "AUGUST"
.AddItem "SEPTEMBER"
.AddItem "OCTOBER"
.AddItem "NOVEMBER"
.AddItem "DECEMBER"
End With

'Combobox 6
With ComboBox6
.AddItem "Sandy"
.AddItem "Kim"
End With

'Combobox 7
With ComboBox7
.AddItem "JANUARY"
.AddItem "FEBUARY"
.AddItem "MARCH"
.AddItem "APRIL"
.AddItem "MAY"
.AddItem "JUNE"
.AddItem "JULY"
.AddItem "AUGUST"
.AddItem "SEPTEMBER"
.AddItem "OCTOBER"
.AddItem "NOVEMBER"
.AddItem "DECEMBER"
End With

'Combobox 8
With ComboBox8
.AddItem "JANUARY"
.AddItem "FEBUARY"
.AddItem "MARCH"
.AddItem "APRIL"
.AddItem "MAY"
.AddItem "JUNE"
.AddItem "JULY"
.AddItem "AUGUST"
.AddItem "SEPTEMBER"
.AddItem "OCTOBER"
.AddItem "NOVEMBER"
.AddItem "DECEMBER"
End With

'Combobox 9
With ComboBox9
.AddItem "JANUARY"
.AddItem "FEBUARY"
.AddItem "MARCH"
.AddItem "APRIL"
.AddItem "MAY"
.AddItem "JUNE"
.AddItem "JULY"
.AddItem "AUGUST"
.AddItem "SEPTEMBER"
.AddItem "OCTOBER"
.AddItem "NOVEMBER"
.AddItem "DECEMBER"
End With

'Combobox 10
With ComboBox10
.AddItem "21.00"
.AddItem "35.00"
.AddItem "38.50"
.AddItem "42.00"
.AddItem "77.00"
End With

'Combobox 11
With Sheets("OPTIONS PAGE")
Me.ComboBox11.List = .Range("G2", .Cells(.Rows.Count, "G").End(xlUp)).Value
End With

End Sub

'Reciepts

Private Sub CommandButton1_Click()
Dim lr As Long
Application.ScreenUpdating = True
Sheet = ComboBox1.Text
If Sheet = "" Then
MsgBox "Select Month", vbInformation, "Error"

Exit Sub
End If

Sheets(Sheet).Select
Set findBlank = Range("H2:H53").Find(What:="", lookat:=xlWhole)
findBlank.Select
ActiveCell.Value = DTPicker1.Value
ActiveCell.Offset(0, 1).Value = TextBox1.Text
ActiveCell.Offset(0, 2).Value = TextBox2.Text
ActiveCell.Offset(0, 3).Value = TextBox3.Text

'Sort Reciepts
Sheets(Sheet).Range("H2:L53").Sort key1:=Range("H2"), order1:=xlAscending, Header:=xlYes

'Clear Form
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""

End Sub

'Expences

Private Sub CommandButton2_Click()
Dim lr As Long
Application.ScreenUpdating = True
Sheet = ComboBox2.Text
If Sheet = "" Then
MsgBox "Select Month", vbInformation, "Error"
Exit Sub
End If

Sheets(Sheet).Select
Set findBlank = Range("A2:A53").Find(What:="", lookat:=xlWhole)
findBlank.Select

If ComboBox3 <> "" Then
ActiveCell.Value = DTPicker2.Value
ActiveCell.Offset(0, 1).Value = TextBox4.Text
ActiveCell.Offset(0, 2).Value = ComboBox3.Text
ActiveCell.Offset(0, 3).Value = ComboBox4.Text
ActiveCell.Offset(0, 4).Value = TextBox5.Text
ActiveCell.Offset(0, 5).Value = TextBox6.Text
End If

'CC fees
If TextBox7.Text <> "" Then
Set findBlank = Range("S2:S53").Find(What:="", lookat:=xlWhole)
findBlank.Select
ActiveCell.Value = DTPicker2.Value
ActiveCell.Offset(0, 1).Value = TextBox7.Text

'Sort CC fees
Sheets(Sheet).Range("S2:T53").Sort key1:=Range("S2"), order1:=xlAscending, Header:=xlYes
End If

'Add UTILITY
If ComboBox4 = "UTILITY" Then
Set findBlank = Range("Z2:Z23").Find(What:="", lookat:=xlWhole)
findBlank.Select
ActiveCell.Value = DTPicker2.Value
ActiveCell.Offset(0, 1).Value = TextBox5.Text

'Sort Utility
Sheets(Sheet).Range("Z2:AA23").Sort key1:=Range("Z2"), order1:=xlAscending, Header:=xlYes
End If

'Add INSURANCE
If ComboBox4 = "INSURANCE" Then
Set findBlank = Range("Z28:Z53").Find(What:="", lookat:=xlWhole)
findBlank.Select
ActiveCell.Value = DTPicker2.Value
ActiveCell.Offset(0, 1).Value = TextBox5.Text

'Sort Insurance
Sheets(Sheet).Range("Z28:AA53").Sort key1:=Range("Z28"), order1:=xlAscending, Header:=xlYes
End If

'Add TAXES PAID
If ComboBox4 = "TAXES" Then
Set findBlank = Range("AK42:AK53").Find(What:="", lookat:=xlWhole)
findBlank.Select
ActiveCell.Value = DTPicker2.Value
ActiveCell.Offset(0, 1).Value = TextBox5.Text

'Sort Taxes
Sheets(Sheet).Range("AK42:AL53").Sort key1:=Range("AK28"), order1:=xlAscending, Header:=xlYes
End If

'Sort Expences
Sheets(Sheet).Range("A2:F53").Sort key1:=Range("A2"), order1:=xlAscending, Header:=xlYes

'Clear Form
TextBox4.Text = ""
ComboBox3.Text = ""
ComboBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""

End Sub

'Labor Costs
Private Sub CommandButton3_Click()
Dim lr As Long
Dim findDate As Date
Application.ScreenUpdating = True
Sheet = ComboBox5.Text
If Sheet = "" Then
MsgBox "Select Month", vbInformation, "Error"
Exit Sub
End If
Sheets(Sheet).Select
iDate = Trim(DTPicker3.Value)

'set findDate = Range("V3:V53").Find(What:=iDate, LookAt:=xlWhole)
Range("V3").Select
For i = 1 To 51
If Trim(ActiveCell.Value) = iDate Then
myName = ComboBox6.Text
If myName = "Sandy" Then myCol = "W"
If myName = "Kim" Then myCol = "X"
Range(myCol & ActiveCell.Row).Value = ComboBox10.Value
'Sort Labor Costs
Sheets(Sheet).Range("V2:X53").Sort key1:=Range("V2"), order1:=xlAscending, Header:=xlYes
'Clear Form
ComboBox6.Text = ""
ComboBox10.Text = ""
Exit Sub
Else
'i = i + 1
Range("V" & i + 2).Select
End If
Next
lr = Range("V53").End(xlUp).Row + 1
myName = ComboBox6.Text
If myName = "Sandy" Then myCol = "W"
If myName = "Kim" Then myCol = "X"
Range("V" & lr).Value = iDate

Range(myCol & lr).Value = ComboBox10.Value

'Sort Labor Costs
Sheets(Sheet).Range("V2:X53").Sort key1:=Range("V2"), order1:=xlAscending, Header:=xlYes

'Clear Form
ComboBox6.Text = ""
ComboBox10.Text = ""

End Sub

'Bank Deposits

Private Sub CommandButton4_Click()
Dim lr As Long
Application.ScreenUpdating = True
Sheet = ComboBox7.Text
If Sheet = "" Then
MsgBox "Select Month", vbInformation, "Error"
Exit Sub
End If
Sheets(Sheet).Select
Set findBlank = Range("AK2:AK53").Find(What:="", lookat:=xlWhole)
findBlank.Select
ActiveCell.Value = DTPicker4.Value
ActiveCell.Offset(0, 1).Value = TextBox9.Text

'Sort Bank Deposits
Sheets(Sheet).Range("AK2:AL53").Sort key1:=Range("AK2"), order1:=xlAscending, Header:=xlYes

'Clear Form
ComboBox7.Text = ""
TextBox9.Text = ""

End Sub

'Product Loss

Private Sub CommandButton5_Click()
Dim lr As Long
Application.ScreenUpdating = True
Sheet = ComboBox8.Text
If Sheet = "" Then
MsgBox "Select Month", vbInformation, "Error"
Exit Sub
End If
Sheets(Sheet).Select
Set findBlank = Range("AC2:AC53").Find(What:="", lookat:=xlWhole)
findBlank.Select
ActiveCell.Value = DTPicker5.Value
ActiveCell.Offset(0, 1).Value = TextBox10.Text
ActiveCell.Offset(0, 2).Value = ComboBox11.Text

'Sort Product Loss

Sheets(Sheet).Range("AC2:AE53").Sort key1:=Range("AC2"), order1:=xlAscending, Header:=xlYes

'Clear Form
TextBox10.Text = ""
ComboBox11.Text = ""
End Sub

'Wendy Sales

Private Sub CommandButton6_Click()
Dim lr As Long
Application.ScreenUpdating = True
Sheet = ComboBox9.Text
If Sheet = "" Then
MsgBox "Select Month", vbInformation, "Error"
Exit Sub
End If
Sheets(Sheet).Select
Set findBlank = Range("AH2:AH53").Find(What:="", lookat:=xlWhole)
findBlank.Select
ActiveCell.Value = DTPicker6.Value
ActiveCell.Offset(0, 1).Value = TextBox12.Text

'Sort Wendy Sales
Sheets(Sheet).Range("AH2:AI53").Sort key1:=Range("AH2"), order1:=xlAscending, Header:=xlYes

'Clear Form
TextBox12.Text = ""

End Sub

Private Sub CommandButton7_Click()
Unload Entryform1
End Sub

Private Sub CommandButton8_Click()
Dim lr As Long
Application.ScreenUpdating = True
Sheets("NEW EQUIP REPAIRS").Select
Set findBlank = Range("A2:a20").Find(What:="", lookat:=xlWhole)
findBlank.Select
ActiveCell.Value = DTPicker7.Value
ActiveCell.Offset(0, 1).Value = TextBox14.Text

Sheets("NEW EQUIP REPAIRS").Range("A3:J20").Sort key1:=Range("A3"), order1:=xlAscending, Header:=xlYes

End Sub

**alansidman** · 02-06-2021, 08:43 PM

Firstly,
Welcome to the Forum. It is important that you read and understand the rules of this forum if you expect to get any help.

1. Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

2. Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. No help to be offered until the link is provided.)

**torachan** · 02-07-2021, 03:16 AM

As well as conforming to the above moderation please upload your workbook, it is evident from the posting you would benifit from more guidance as to loading comboboxes.
Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.