Fred,
Glad you found a work around.
--
Regards,
Tom Ogilvy
"Steve" <No Spam wrote in message ...
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