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!
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,259
Office Version
  1. 2013
Platform
  1. Windows
Try something like this:
Modify Range to your needs:
VBA Code:
Private Sub UserForm_Initialize()
'Modified  1/5/2021  6:22:47 PM  EST
ComboBox1.List = Sheets("Alpha").Range("A1:A24").Value
End Sub
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,259
Office Version
  1. 2013
Platform
  1. Windows
Now if you wanted a Range that is always expanding you can also use a Table Range like this:
Using a Excel Table.
And there are other ways also when your range is always expanding.
Like using Lastrow.

VBA Code:
Private Sub UserForm_Initialize()
'Modified  1/5/2021  6:34:47 PM  EST
ComboBox1.List = Sheets("Alpha").ListObjects("Table1").DataBodyRange.Columns(1).Value
End Sub
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
@My Aswer Is This Thank you, I will probably use your second option in the event I need to add or remove anything from my list. If I might ask, can I put the list on any sheet in the workbook and then just substitute the name of my sheet for where you have "Alpha"? And I'm also assuming that your "Table1" is the named range or is it something else and that this is also something I can change to whatever I want to call it? Thanks!
 

My Aswer Is This

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

ADVERTISEMENT

Since it appears as if your not familiar with Tables
It would be easier for you to use a script like this:
In my script you see the name "Alpha"
SheetName = "Alpha"
Just change this to the name of your sheet where the script can get the Combobox values
And you should see A.
Lastrow = Sheets(SheetName).Cells(Rows.Count, "A").End(xlUp).Row
ComboBox1.List = Sheets(SheetName).Range("A1:A" & Lastrow).Value

This means search column A of the sheet. If it's not column A change A to what ever column.
And the script starts looking in row(1)
VBA Code:
Private Sub UserForm_Initialize()
'Modified  1/5/2021  8:56:05 PM  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
End Sub
 
Solution

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
@My Aswer Is This thanks, I will work on this over the next day or so. Seems like a good place to start for me. I may come back at some point if I run into any problems. Thanks again!
 

My Aswer Is This

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

ADVERTISEMENT

@My Aswer Is This thanks, I will work on this over the next day or so. Seems like a good place to start for me. I may come back at some point if I run into any problems. Thanks again!
Glad I was able to help you.
If you need more help let me know.
Come back here to Mr. Excel next time you need additional assistance.
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
@My Aswer Is This

OK, I'm back with a quick question. Your solution below works great for one ComboBox but I have two of them on my UserForm that need to reference the same table and when I use your code twice in the UserForm, I am getting a compile error "Duplicate declaration in current scope". I think it has something to do with the "LastRow As Long" portion. Thanks!
Since it appears as if your not familiar with Tables
It would be easier for you to use a script like this:
In my script you see the name "Alpha"
SheetName = "Alpha"
Just change this to the name of your sheet where the script can get the Combobox values
And you should see A.
Lastrow = Sheets(SheetName).Cells(Rows.Count, "A").End(xlUp).Row
ComboBox1.List = Sheets(SheetName).Range("A1:A" & Lastrow).Value

This means search column A of the sheet. If it's not column A change A to what ever column.
And the script starts looking in row(1)
VBA Code:
Private Sub UserForm_Initialize()
'Modified  1/5/2021  8:56:05 PM  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
End Sub
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,259
Office Version
  1. 2013
Platform
  1. Windows
@My Aswer Is This

OK, I'm back with a quick question. Your solution below works great for one ComboBox but I have two of them on my UserForm that need to reference the same table and when I use your code twice in the UserForm, I am getting a compile error "Duplicate declaration in current scope". I think it has something to do with the "LastRow As Long" portion. Thanks!
Show me the script your now using for two comboboxes.
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
@My Aswer Is This

I hope this is what you are asking for. The section I want to replace is the SProviderComboBox.
VBA Code:
Private Sub UserForm_Initialize()
Dim objControl As MSForms.Control

    For Each objControl In Me.Controls
        If TypeName(objControl) = "TextBox" And objControl.Tag <> "" Then
           Me.setupPlaceholder objControl.Name, False
        End If
    Next objControl

    Me.GenderComboBox.List = Array("M", "F")
    
    Me.OptionComboBox.List = Array("100% Joint Life", _
        "60% Joint Life 5-year guarantee", _
        "60% Joint Life 10-year guarantee", _
        "60% Joint Life 15-year guarantee", _
        "Single Life no guarantee", _
        "Single Life 5-year guarantee", _
        "Single Life 10-year guarantee", _
        "Single Life 15-year guarantee", _
        "Other")

    Dim LastRow As Long
    Dim SheetName As String
    SheetName = "Sheet20"
    LastRow = Sheets(SheetName).Cells(Rows.Count, "A").End(xlUp).Row
    Me.ProviderComboBox.List = Sheets("Sheet20").Range("A2:A" & LastRow).Value
    
    Me.SGenderComboBox.List = Array("M", "F")
    
    Me.SOptionComboBox.List = Array("100% Joint Life", _
        "60% Joint Life 5-year guarantee", _
        "60% Joint Life 10-year guarantee", _
        "60% Joint Life 15-year guarantee", _
        "Single Life no guarantee", _
        "Single Life 5-year guarantee", _
        "Single Life 10-year guarantee", _
        "Single Life 15-year guarantee", _
        "Other")
    
    Me.SProviderComboBox.List = Array("College", _
        "Municipal", _
        "Public Service", _
        "Teachers'", _
        "WorkSafeBC", _
        "Other")
    
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,207
Messages
5,600,321
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