# Date combo boxes



## Mattlake (Dec 22, 2022)

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 (Dec 22, 2022)

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 (Dec 22, 2022)

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


----------



## Akuini (Dec 22, 2022)

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


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


----------



## Mattlake (Dec 22, 2022)

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 (Dec 23, 2022)

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 (Dec 23, 2022)

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


----------

