Date combo boxes

[Mattlake]

HI

Sorry about these questions they seem difficult when i am trying to figure the code out

I want to have a date box on a userform. I have previously had a text box which was fine until October (the formatting then failed) this time i want to use 3 combo boxes (date month and year)

how would i get the three to appear into 1 column on the sheet?

 

[Joe4]

Why not just use a Date picker instead of three separate fields for one date?

This also helps ensure that they do not pick some impossible date, like February 30 or September 31.

 

[Mattlake]

I was just looking at those (for once i was almost on the right track)

 

[Akuini]

If Combobox1 is day, Combobox2 is month and Combobox3 is year, then:

Range("A1") = DateSerial(Combobox3.Value, Combobox2.Value, Combobox1.Value)

 

[Mattlake]

Below is the VBA I have written
but it is coming up run time error 13 type mismatch.

The date doesn't seem to be inputted and is doesnt clear.

I think i need to do a separate clear button (but would prefer it to be as one)


any help would be welcome

Private Sub UserForm_Initialize()
ComboBox1.List = [other!A2:A20].Value
ComboBox2.List = [other!d2:d6].Value
ComboBox8.List = [other!L1:L5].Value
ComboBox4.List = [other!H2:H32].Value
ComboBox5.List = [other!I2:I13].Value
ComboBox6.List = [other!J2:J8].Value
ComboBox3.List = [other!A2:A20].Value
ComboBox7.List = [other!N1:N3].Value
ComboBox9.List = [other!f1:f2].Value
ComboBox11.List = [other!H2:H32].Value
ComboBox12.List = [other!I2:I13].Value
ComboBox10.List = [other!J2:J8].Value
ComboBox13.List = [other!f1:f2].Value

End Sub
Private Sub CommandButton1_Click()

Dim LR As Long
LR = Sheets("2023").Cells(Rows.Count, "A").End(xlUp).Row + 1

With Sheets("2023")
.Range("b" & LR).Value = ComboBox1.Value
.Range("a" & LR).Value = ComboBox2.Value
.Range("d" & LR).Value = ComboBox3.Value
.Range("j" & LR).Value = ComboBox7.Value
.Range("h" & LR).Value = ComboBox8.Value
.Range("l" & LR).Value = ComboBox9.Value
.Range("c" & LR).Value = TextBox1.Value
.Range("e" & LR).Value = TextBox2.Value
.Range("i" & LR).Value = TextBox3.Value
.Range("j" & LR).Value = TextBox4.Value
.Range("k" & LR).Value = TextBox5.Value
.Range("f") = DateSerial(ComboBox4.Value, ComboBox5.Value, ComboBox6.Value)
.Range("m") = DateSerial(ComboBox11.Value, ComboBox12.Value, ComboBox10.Value)


End With

Me.ComboBox1.Value = Null
Me.ComboBox2.Value = Null
Me.ComboBox3.Value = Null
Me.ComboBox4.Value = Null
Me.ComboBox5.Value = Null
Me.ComboBox6.Value = Null
Me.ComboBox7.Value = Null
Me.ComboBox8.Value = Null
Me.ComboBox9.Value = Null
Me.ComboBox10.Value = Null
Me.ComboBox11.Value = Null
Me.ComboBox12.Value = Null
Me.ComboBox13.Value = Null
Me.ComboBox4.Value = Null
Me.ComboBox5.Value = Null
Me.ComboBox6.Value = Null
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""

End Sub

 

[Mattlake]

ignore this, the first section works as it loads the combo boxes from a data tab.

The rest i was trying to do a submit button and a clear button.

I need to do more research on this

 

[Mattlake]

This seems to work but any thoughts would be appreciated


Private Sub cmdAdd_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("2023")

iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

With ws
.Cells(iRow, 1).Value = Me.ComboBox2.Value
.Cells(iRow, 2).Value = Me.ComboBox1.Value
.Cells(iRow, 3).Value = Me.TextBox1.Value
.Cells(iRow, 4).Value = Me.ComboBox3.Value
.Cells(iRow, 5).Value = Me.TextBox2.Value
.Cells(iRow, 7).Value = Me.ComboBox7.Value
.Cells(iRow, 8).Value = Me.ComboBox8.Value
.Cells(iRow, 9).Value = Me.TextBox3.Value
.Cells(iRow, 10).Value = Me.TextBox4.Value
.Cells(iRow, 11).Value = Me.TextBox5.Value
.Cells(iRow, 12).Value = Me.ComboBox9.Value
.Cells(iRow, 14).Value = Me.ComboBox13.Value

End With
Me.ComboBox1.Value = ""
Me.ComboBox2.Value = ""
Me.ComboBox3.Value = ""
Me.ComboBox4.Value = ""
Me.ComboBox5.Value = ""
Me.ComboBox6.Value = ""
Me.ComboBox7.Value = ""
Me.ComboBox8.Value = ""
Me.ComboBox9.Value = ""
Me.ComboBox10.Value = ""
Me.ComboBox11.Value = ""
Me.ComboBox12.Value = ""
Me.ComboBox13.Value = ""
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""


End Sub

Private Sub cmdclose_Click()
Unload Me
End Sub



Private Sub UserForm_Initialize()
ComboBox1.List = [other!A2:A20].Value
ComboBox2.List = [other!d2:d6].Value
ComboBox8.List = [other!L1:L5].Value
ComboBox4.List = [other!H2:H32].Value
ComboBox5.List = [other!I2:I13].Value
ComboBox6.List = [other!J2:J8].Value
ComboBox3.List = [other!A2:A20].Value
ComboBox7.List = [other!N1:N3].Value
ComboBox9.List = [other!f1:f2].Value
ComboBox11.List = [other!H2:H32].Value
ComboBox12.List = [other!I2:I13].Value
ComboBox10.List = [other!J2:J8].Value
ComboBox13.List = [other!f1:f2].Value

End Sub