Fill two column combo box

[Steve]

Hi

Excel 2K

I need to fill a combo box from a dynamic range compiled by users. The first
column (bound column, not visible) will contain letters for use in a "begins
with" filter. The second column (visible, not bound) will contain the
description to be seen by users on the form.

I found an example how to fill a multi-column combo in a form at
http://www.erlandsendata.no/english/
The example works. However, when I transfer the form to my spreadsheet and
rename the dynamic range to my dynamic range, the code generates an error at
rArray = Range("DynamicDataRange"). Run-time errot 1004 Method 'Range' of
object Global failed. Pressing help at this point produce an informative
blank page!

My initial thougt was a need to size the array so, I tested with a fixed
size rArray. Still received an error. Can comone give me some help in
filling a two column combo from a dynamic named range?

Steve

The Erlandsen code:

Private Sub UserForm_Initialize()
Dim rArray As Variant

rArray = Range("DynamicDataRange")
With Me.ComboBox3
' use the next line
.List() = rArray
' or the next two lines
'.RowSource = "Data!A4:C23"
'.ColumnHeads = True
.ListIndex = -1 ' no selected item
End With
End Sub

 

[Tom Ogilvy]

Try it this way:

Private Sub UserForm_Initialize()
Dim rArray As Variant
Dim rng as Range
On error Resume Next
set rng = Thisworkbook.Names("DynamicDataRange").RefersToRange
On error goto 0
if rng is nothing then
msgbox "No range named DynamicDataRange"
exit sub
end if
rArray = rng.Value
With Me.ComboBox3
' use the next line
.List() = rArray
' or the next two lines
'.RowSource = "Data!A4:C23"
'.ColumnHeads = True
.ListIndex = -1 ' no selected item
End With
End Sub

 

[Tom Ogilvy]

Note as well, if you are positive you have the named range DynamicDataRange,
then perhaps the formula is returning an error rather than a range.

One way to test is to type DynamicDataRange in the name box and hit enter.
Does it take you to your data?

 

[Steve]

Tom

It's not picking up my dynamic named range:

I have 'TeamArray' defined as:
=OFFSET(TeamDetail!$A$2,0,0,COUNTA(TeamDetail!$A65534:$A996),COUNTA(TeamDetail!$2:$2))

Code below was modified appropriately by copying the name "TeamArray " from
the Define Name and pasting over DynamicDataRange (ie no spelling mistake).

Steve

 

[Steve]

The dynamic range sets marching ants around the correct range.

 

[Tom Ogilvy]

Hard to believe.

Your formula doesn't make much sense.

If you base your formula on A2, then count the entries in A996:A65534 to get
the row count, don't see how this could define the correct range.

It appears you formula is using relative references and will vary based on
the active Cell. If you clean up your formula, then it should work.

 

[Tom Ogilvy]

When you do what I described, you don't get marching ants. The range is
selected.

 

[Steve]

Bob
I couldn't get a dynamic range working with the code below. However, Excel
has many cat-skinning methods.
Worksheet change event now has code to zap the existing name and create a
new one. Result one Combo that works.
Thank you for your guidance on the code.

Steve

 

[Tom Ogilvy]

Fred,
Glad you found a work around.

 

[Steve]

Tom
Don't you mean "Wally" ?

Fred,
Glad you found a work around.

....