Dynamic Named Range as Row Source in Userform Combo Box using Excel VBA

[Maneesh Massey]

Hi,
How can I use a dynamic named range as a RowSource in a userform Combo Box. I have a Combo Box on a userform. I would like to have the rowsource for the Combo Box keep updating as a new ID number is added to the worksheet in column B. The name of the worksheet is 'Agent'. I am using Excel 2013.
Appreciate all help!

 

[Smallman]

Maneesh

You can have the combo box update with the help of a dynamic named range I have originally titled Dynamic. THe following is the coding to populate the combobox. Example file attached.

Private Sub UserForm_Initialize()
    Me.ComboBox1.RowSource = "Dynamic"
End Sub

or what I would prefer you could just trap the Dynamic range on the fly.

Private Sub UserForm_Initialize()
    Range("B2", Range("B" & Rows.Count).End(xlUp)).Name = "Dynamic"
    Me.ComboBox1.RowSource = "Dynamic"
End Sub

Take care

Smallman

 

[Maneesh Massey]

Thanks a lot Smallman. You're a life saver!

 

[Smallman]

No worries Maneesh

See you next time.

Smallman

 

[Lolo]

Hello,

However if the dynamic range is bases on a horizontal range :
a b c
Instead of usuel vertical range
a
b
c

This is not working and only 'a' is shown in the excel combo box(type =from control, not active X control)... ?

In my case i have dynamic horizontal range (so i can have a b c or a b c d or a b c d e, etc...), so How can i fil in a combobox based on a dynamic horizontal range ? Currently i use an range name calle 'dynamic' based on the offset function. And it représents the right range. Ok. But the combo box which use the range as row source, shows only the first elem 'a'

One solution is to transpose the horizontal range in a vertical range, but i don t manage to do it since my range is dynamic and can contains a variable nimber of values.

Anyone as an idea ? Thank you

 

[Lymm]

Lolo could you post a sample sheet, it would make it easier for someone to help with a solution.

 

[Lolo]

Yes for sure,
find in attached the sample.

By the way I have found a workaround with INDEX and ROWS function, in order to transpose the list in a column, then use the column as source of the combobox, but if the number of elem grows in the horizontal list, I need to drill down formula in order to adapt the column list...

So if if the horizontal range would be directly ok it would be better...

 

[Smallman]

Lolo

This method was set up as a Vertical method which is what works best with combo boxes. As with most things in XL there is a hack for it.

You can either have a button which updates your combo box or have a worksheet activate event that updates the combo box or as you have already done flip the range to being vertical and use a dynamic range. This would ease the need for any coding - best option I think.

However here is the work around.

Private Sub Worksheet_Activate()
Dim r As Range
ComboBox1.Clear
    For Each r In [A1].CurrentRegion
        ComboBox1.AddItem r.Value
    Next r
    ComboBox1.ListIndex = 0
End Sub

File attached to show workings. To test remove some items from row 1 click in Sheet1 and then click back and select the combo box.

take care

Smallman

 

[Lolo]

Thank you for your answer.

However, i m just wondering if there was a solution without vba. Indeed, with vba, i was aware of this
And an horizontal range is ok when using a list / data validation but i need à combo box in this case

In my solution with formulas, i need a little piece of code in order to copy the formula. So if there is vba, it is probably better to use your solution in full vba, instead of having a combination of formula + vba. I will change it

 

[Smallman]

You can certainly source some formula which transposes the data to vertical. This is probably more down the VBA path where as you add or remove data from Row 1 the combo box will update. I have had a rethink on this and the following works nicely.

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
    If Not Intersect(Target, Rows("1:1")) Is Nothing Then
        ComboBox1.Clear
            For Each r In [A1].CurrentRegion
                ComboBox1.AddItem r.Value
            Next r
            ComboBox1.ListIndex = 0
    End If
End Sub

Take care

Smallman

 

[G.Manikandan]

Smallman, your code for combobox was a treasure for me. Thanks for the help.

 

[Lolo]

In attached a way to transpose the horizontal list in a column list (so the combo boxes are based on the vertical list. I use an array formula, based on a dynamic named range. So no VBA. However the list is static, but it should be failrly easy to make it dynamic

 

[shrivallabha]

With your current list you could use:
In the name manager Initiallist:
=Feuil1!$C$3:INDEX(Feuil1!$3:$3;MATCH("zzz";Feuil1!$3:$3))
And then in cell B7 enter normally:
=IFERROR(INDEX(Initiallist;1;ROWS($A$1:A1));"")
And then copy down

 

[Hussain]

Hi,
I would like to assign the values to combox based on the value from another combo box. For ex:- I have different streams in a college like Engineering, Medical, Arts etc.
I have an excel sheet with columns like STREAM,COURSE etc.. Each stream will have multiple courses and we are having hundreds of streams. What ever stream selected based on that . I need to pull all the course. I tried vlookup but it returns only the first match. I need to do loop function. Can you help .

Thanks in advance.

 

[NARAYANK991]

Hi Hussain ,

First , please post your question in a thread of its own ; click on New Thread and post your question.

Second , please upload a sample file with data.

Narayan

 

[Jimit]

form_name.dropdown_name.RowSource = "=name_of_namerange"

 

[Lolo]

Dear Jimit,

Your post refers to the initial question ? Because, You refer to a VBA form, while we are talking about a combobox (ActiveX or Form control) directly in an Excel sheet. And the initial problem was about an horizontal range as source. In this case only the 1st elem of the range is displayed in the combobox. And I tried to find a solution without VBA.
So if you can explain more please.

 

[Jimit]

Dear Jimit,

Your post refers to the initial question ? Because, You refer to a VBA form, while we are talking about a combobox (ActiveX or Form control) directly in an Excel sheet. And the initial problem was about an horizontal range as source. In this case only the 1st elem of the range is displayed in the combobox. And I tried to find a solution without VBA.
So if you can explain more please.

Yup,my answer was based on the the initial question and i used combo-box and in that dropdown i gave the dynamic range which was in hidden sheet of my workbook.And it works fine.

 

[Lolo]

Could you post a file with an example ? Not sure we are talking about the same thing... The dynamic range is horzontal ? The combobox is in a sheet, and not in a form ?
If you are right, it would be a good thing, but seems too easy said like that. A combobox, a range and it works... This is exaclty what it is not working, so I'm eager to see your solution...

 

[Csalam]

Maneesh

You can have the combo box update with the help of a dynamic named range I have originally titled Dynamic. THe following is the coding to populate the combobox. Example file attached.

Private Sub UserForm_Initialize()
    Me.ComboBox1.RowSource = "Dynamic"
End Sub

or what I would prefer you could just trap the Dynamic range on the fly.

Private Sub UserForm_Initialize()
    Range("B2", Range("B" & Rows.Count).End(xlUp)).Name = "Dynamic"
    Me.ComboBox1.RowSource = "Dynamic"
End Sub

Take care

Smallman

This code saved me alot of time !! Thanks