• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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

Excel Ninja
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.


Code:
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.

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

Smallman
 

Attachments

Last edited:

Lolo

Member
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

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

Lolo

Member
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...
 

Attachments

Smallman

Excel Ninja
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.

Code:
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
 

Attachments

Lolo

Member
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

Excel Ninja
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.

Code:
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
 

Lolo

Member
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
 

Attachments

shrivallabha

Excel Ninja
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

New Member
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

Excel Ninja
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
 

Lolo

Member
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

New Member
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

Member
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

New Member
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.


Code:
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.

Code:
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
 
Top