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]

Try something like this:
Modify Range to your needs:

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]

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.

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]

@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]

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)

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

 

[leopardhawk]

@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]

@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]

@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)

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]

@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]

@My Aswer Is This

I hope this is what you are asking for. The section I want to replace is the SProviderComboBox.

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