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(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
"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