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" wrote in message
...
When you do what I described, you don't get marching ants. The range is
selected.

--
Regards,
Tom Ogilvy

"Steve" <No Spam wrote in message ...
The dynamic range sets marching ants around the correct range.

"Steve" <No Spam wrote in message ...
Tom

It's not picking up my dynamic named range:

I have 'TeamArray' defined as:

=OFFSET(TeamDetail!$A$2,0,0,COUNTA(TeamDetail!$A65 534:$A996),COUNTA(TeamDeta
il!$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

"Tom Ogilvy" wrote in message
...
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").RefersToRan ge
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

--
Regards,
Tom Ogilvy


"Steve" <No Spam wrote in message
...
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