+ Reply to Thread
Results 1 to 10 of 10

Fill two column combo box

  1. #1
    Steve
    Guest

    Fill two column combo box

    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



  2. #2
    Tom Ogilvy
    Guest

    Re: Fill two column combo box

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




  3. #3
    Tom Ogilvy
    Guest

    Re: Fill two column combo box

    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?

    --
    Regards,
    Tom Ogilvy

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

    >
    >




  4. #4
    Steve
    Guest

    Re: Fill two column combo box

    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

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

    >
    >




  5. #5
    Steve
    Guest

    Re: Fill two column combo box

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

    >>
    >>

    >
    >




  6. #6
    Tom Ogilvy
    Guest

    Re: Fill two column combo box

    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.

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

    > >
    > >

    >
    >




  7. #7
    Tom Ogilvy
    Guest

    Re: Fill two column combo box

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

    > >
    > >

    >
    >




  8. #8
    Steve
    Guest

    Re: Fill two column combo box

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

    >>
    >>

    >
    >




  9. #9
    Tom Ogilvy
    Guest

    Re: Fill two column combo box

    Fred,
    Glad you found a work around.

    --
    Regards,
    Tom Ogilvy

    "Steve" <No Spam> wrote in message news:436e700d@dnews.tpgi.com.au...
    > 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" <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
    > >> >>>
    > >> >>>
    > >> >>
    > >> >>
    > >> >
    > >> >
    > >>
    > >>

    > >
    > >

    >
    >




  10. #10
    Steve
    Guest

    Re: Fill two column combo box

    Tom
    Don't you mean "Wally" ?


    "Tom Ogilvy" <twogilvy@msn.com> wrote in message
    news:u6i2fkx4FHA.2600@tk2msftngp13.phx.gbl...
    > Fred,
    > Glad you found a work around.
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    > "Steve" <No Spam> wrote in message news:436e700d@dnews.tpgi.com.au...
    >> Bob

    ....



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1