Question re: named ranges

[leopardhawk]

Hello forum friends, I have a userform with a couple of combo-boxes in it that appear as drop-downs when the user selects them. Currently, they have 5 or 6 items on the list but I was trying to expand that to approximately 100 only to receive the "Too many line continuations" error. I think I might be able to get around this by putting the list of 100 or so names somewhere (where, I have no idea!) and then name the range and somehow add that to the combo-box properties???? If anyone knows the best way to do this, I will be grateful for any suggestions. Thanks!

 

[My Aswer Is This]

Unless I'm sleeping I do not see where you have Lastrow twice in your script.
Dim Lastrow as long is only shown once.

 

[leopardhawk]

I'm sorry, I should have explained it better. The first Dim LastRow is your code that I copied into mine. It works fine. When I tried to copy it 'as is' and use it to replace the other ComboBox, that's when I got the error. The other comboBox is called SProviderComboBox and it is working now, but I want it to work with the list of almost 100 items that the other one is accessing. I sure hope this makes sense.

 

[My Aswer Is This]

I'm sorry, I should have explained it better. The first Dim LastRow is your code that I copied into mine. It works fine. When I tried to copy it 'as is' and use it to replace the other ComboBox, that's when I got the error. The other comboBox is called SProviderComboBox and it is working now, but I want it to work with the list of almost 100 items that the other one is accessing. I sure hope this makes sense.

You should only use Dim lastrow as long once.
and it should work for both comboboxes

 

[leopardhawk]

@My Aswer Is This

Thank you so much! I was trying to copy the whole segment. Now it is working perfectly! You don't know how much I appreciate your help.

 

[My Aswer Is This]

Try this:

Private Sub UserForm_Initialize()
'Modified  1/7/2021  12:57:48 AM  EST
Dim Lastrow As Long
Dim SheetName As String
SheetName = "Alpha"
Lastrow = Sheets(SheetName).Cells(Rows.Count, "A").End(xlUp).Row
ComboBox1.List = Sheets(SheetName).Range("A1:A" & Lastrow).Value
ComboBox2.List = Sheets(SheetName).Range("A1:A" & Lastrow).Value
End Sub

 

[My Aswer Is This]

I see you used an Array which works. Here is how I normally do it.
Now an array may be faster. But this is easier for me to read:
See the Zero after Other. That puts other at the top of the list in case you want to:
List them is some sort of order not the way you listed them.
And you could put a 2 there to put it third in line.
Just showing you other ways to do things:

With Me.OptionComboBox
'Modified  1/7/2021  1:29:48 AM  EST
    .AddItem "60% Joint Life 5-year guarantee"
    .AddItem "60% Joint Life 10-year guarantee"
    .AddItem "60% Joint Life 15-year guarantee"
    .AddItem "Single Life no guarantee"
    .AddItem "Single Life 5-year guarantee"
    .AddItem "Other", 0
End With

 

[leopardhawk]

@My Aswer Is This

that's awesome and I appreciate you taking the time to show me some alternatives. I learn something every time I come to Mr Excel. Thanks again!

 

[My Aswer Is This]

@My Aswer Is This

that's awesome and I appreciate you taking the time to show me some alternatives. I learn something every time I come to Mr Excel. Thanks again!

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.