Question re: named ranges

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,259
Office Version
  1. 2013
Platform
  1. Windows
Unless I'm sleeping I do not see where you have Lastrow twice in your script.
Dim Lastrow as long is only shown once.
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,259
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
@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

Well-known Member
Joined
Jul 5, 2014
Messages
17,259
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Try this:
VBA Code:
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,259
Office Version
  1. 2013
Platform
  1. Windows
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:
VBA Code:
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

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
@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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,206
Messages
5,600,317
Members
414,376
Latest member
NickYOW

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top