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]

How about

   Dim Ary As Variant
   
   Ary = Evaluate("if({1},row(1930:2230))")
   Me.ComboBox1.List = Ary

 

[leopardhawk]

@Fluff I like this approach but I'm getting an error 'Run-time error '381' Could not set the list property. Invalid property array index.'

 

[leopardhawk]

@Fluff never mind... I had one too many close brackets!!! Sorry about that!!! It works fine, thank you so much!!!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[leopardhawk]

@Fluff I think this is a related question but I can open another post if you think I should. I now have three comboboxes on my userform where users will select month, day and year respectively. Prior to this approach, I was using a single text box and the following code to get the Birth Date over to the worksheet. The line with the 'B" and the /B is the one in question.

With Sheets(11)

        If Not AllmostEmpty(FirstNameTextBox) Then .Range("C9").Value = FirstNameTextBox.Value
                
        If LastNameTextBox <> "Optional" Then
            If Not AllmostEmpty(LastNameTextBox) Then .Range("D9").Value = LastNameTextBox.Value
        End If
        
        [B]If Not AllmostEmpty(DOBTextBox) Then .Range("E9").Value = DOBTextBox.Value[/B]
        If Not AllmostEmpty(GenderComboBox) Then .Range("F9").Value = GenderComboBox.Value
        
        If CompanyTextBox <> "Optional" Then
        If Not AllmostEmpty(CompanyTextBox) Then .Range("G9").Value = CompanyTextBox.Value
        End If
        
        If Not AllmostEmpty(RDTextBox) Then .Range("C15").Value = RDTextBox.Value
        If Not AllmostEmpty(OptionComboBox) Then .Range("D15").Value = OptionComboBox.Value
        If Not AllmostEmpty(ProviderComboBox) Then .Range("E15").Value = ProviderComboBox.Value
        If Not AllmostEmpty(CPPTextBox) Then .Range("F15").Value = CPPTextBox.Value
        If Not AllmostEmpty(OASTextBox) Then .Range("G15").Value = OASTextBox.Value
      
    End With

I found another post in MrExcel where the solution was to combine the three 'boxes' and send them to the worksheet. My problem is coding it in the same manner that I am currently using which allows the DOBTextBox to only send text if there is actually text in it so as not to overwrite something that might already be in that cell on the worksheet. I can't seem to figure it out! HELP!

The other solution (which didn't work for me).

Ah, ok, cool. I went ahead and used

cells(emptyrow, 4).value = MonthComboBox.Value & " " & DayTextBox.Value & ", " & YearTextBox.Value

and custom formatted the destination cell into mmmm dd, yyyy

Thanks for the assistance!

I copied what he has after the first .Value and pasted it after my first .Value but I'm not sure how to combine the three comboboxes into the 'IfNotAllmostEmpty' part. Any suggestions?

 

[Fluff]

You can use this to get the date

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

 

[Rick Rothstein]

How about

   Dim Ary As Variant
  
   Ary = Evaluate("if({1},row(1930:2230))")
   Me.ComboBox1.List = Ary

I am not sure why you are vectoring through a Variant variable as you can make the assignment directly. Also, since the range is fixed, you can use the shortcut notation for the Evaluate function. And finally, as it turns out, you do not need to use the IF({1}, trick for this code. Putting that all together gives this one liner...

Me.ComboBox1.List = [ROW(1930:2230)]

 

[Fluff]

you do not need to use the IF({1}, trick

As I'm never quite sure when it's needed & when it's not, I tend to use it to be on the safe side.

 

[leopardhawk]

@Rick Rothstein thanks Rick, I can see the value in simplifying the code by removing the vectoring et al, but I still face the same problem of combining the three ComboBoxes together (see post #6) as a string and send them to the worksheet (Sheet 11) so that they arrive there in a particular cell (E9) as a valid date. I know there has to be a way to do it. Any suggestions?