Question re: VBA ComboBox with many items

[leopardhawk]

Hello forum friends, can anyone suggest the best way to code a ComboBox in VBA that would potentially have a few hundred items in it?? It will be for users to select a year, starting say with 1930 and going as high as 2230.

I'm just not sure whether 'array' or 'additem' would be better or maybe I should be looking at some other alternative, like a text box with a spinner on it???

Hoping for ideas! Thanks!

 

[Fluff]

Remove the .Value from Dob

 

[leopardhawk]

@Fluff perfect! I now have a valid date transferred to my worksheet and no errors! I am so grateful for all your help. If I could be indebted, I would be, but I have no Excel skills to offer!

With all of this troubleshooting, I'm not sure which post to mark as the solution. Ideas?

 

[Fluff]

You're welcome & thanks for the feedback.
I'd suggest marking post#32 as the solution.

 

[leopardhawk]

@Fluff oops, I may have spoken too soon. I just noticed that if I click the OKCommandButton with the three comboboxes empty, I get an error (run-time error 13: type mismatch) and the line of code "Dob = DateSerial(Me.YearComboBox, Month(DateValue(Me.MonthComboBox & "/01/2020")), Me.DayComboBox)" is highlighted in yellow. Can this be solved?

 

[leopardhawk]

I think it's important that users can update or change other controls on the userform without having to enter a date each time.

 

[Fluff]

How about

   Dim Dob As Variant
   On Error Resume Next
   Dob = DateSerial(Me.ComboBox1, Month(DateValue(Me.ComboBox2 & "/01/2020")), Me.ComboBox3)
   On Error GoTo 0
   If IsDate(Dob) Then .Range("E9").Value = Dob.Value

 

[leopardhawk]

@Fluff that seems to work but I removed the .Value from the end of the last line of code. Is that OK?? I will continue to test the code and let you know how it turns out. Thank you again!

 

[Fluff]

Is that OK??

Yes that's fine, I forgot to remove it.