LinkBack URL
About LinkBacksBob
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" <twogilvy@msn.com> wrote in message
news:uhli6Co4FHA.3292@tk2msftngp13.phx.gbl...
> 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 news:436d5b5c$1@dnews.tpgi.com.au...
>> The dynamic range sets marching ants around the correct range.
>>
>> "Steve" <No Spam> wrote in message news:436d5afd@dnews.tpgi.com.au...
>> > 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(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" <twogilvy@msn.com> wrote in message
>> > news:ersb%235m4FHA.1476@TK2MSFTNGP10.phx.gbl...
>> >> 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
>> >>
>> >> --
>> >> Regards,
>> >> Tom Ogilvy
>> >>
>> >>
>> >> "Steve" <No Spam> wrote in message
>> >> news:436d50c2$1@dnews.tpgi.com.au...
>> >>> 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
>> >>>
>> >>>
>> >>
>> >>
>> >
>> >
>>
>>
>
>
Register To Reply
Bookmarks