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!

 

[leopardhawk]

You can use this to get the date

   Dim Dob As Variant
   Dob = DateSerial(Me.ComboYear, Me.ComboMonth, Me.ComboDay)

I'm not sure how to use this. I tried putting it in my code and I received an error "Method or data member not found."

 

[Fluff]

Did you change the combo names to match you controls?

 

[leopardhawk]

Yes, I changed them to MonthComboBox, DayComboBox, and YearComboBox. I think the part I'm confused about is how this piece of code gets the three comboboxes to the worksheet??

 

[leopardhawk]

Sorry, it's actually a run-time error '13' Type mismatch. The other error was before I changed the names to match the controls.

 

[Fluff]

It doesn't it stores the date created by the 3 combos. I have no idea what your "AllmostEmpty" routine is doing.

 

[Fluff]

What line of code gave that error & how is the code being called?

 

[leopardhawk]

@Fluff post #6 has a portion of the code with this line in it "If Not AllmostEmpty(DOBTextBox) Then .Range("E9").Value = DOBTextBox.Value" which is how the date in my original DOBTextBox was sent to Sheet 11, Cell E9. The UserForm that has the date fields on it can be used multiple times by a user and the 'Not AllMostEmpty' routine is to so that a user doesn't have to fill in any of the fields each and every time they use the form. It works, but I would love to replace the DOBTextBox with these three textboxes.

The line of code that gave the run-time error is Private Sub CommandButton1_Click() 'PersonalInfoUserForm.Show' when I try and open the userform.

If your code stores the date, where does it store it and how do I get it to Sheet 11 Cell E9...?

 

[Fluff]

In the VB Editor select Tolls, Options, General & then select "Break in class module", Ok.
Then try running the code again.

 

[leopardhawk]

Still getting the type mismatch error but now the line of code is
Dim Dob As Variant
Dob = DateSerial(Me.MonthComboBox, Me.DayComboBox, Me.YearComboBox)

 

[Fluff]

You have them in the wrong order, it must be year, month, day.
Then you can use

If Not AllmostEmpty(Dob) Then .Range("E9").Value = Dob

or just

.Range("E9").Value = Dob