+ Reply to Thread
Results 1 to 8 of 8

Listbox - Rowsource

  1. #1
    Paul W Smith
    Guest

    Listbox - Rowsource

    On a user form I have a listbox that I am populating by having set it's
    rowsource to a range on a named range worksheet.

    I have actions which are run from the userform which add or subtract entries
    from this named range. I have code which changes the named range when new
    items need to be added/subtracted and this works. However what do I have to
    do to have the listbox updated.

    It seems to me that I have to physically move the scroll bar of the listbox
    to have the changes recognised.

    PWS



  2. #2
    Bob Phillips
    Guest

    Re: Listbox - Rowsource

    If that code is executed outwith the form, all you need to do is set the
    rowsource property on the userform initialize event

    Listbox1.RowSource = Range("range_name").Address

    as it is a string property.

    --
    HTH

    Bob Phillips

    (remove nothere from email address if mailing direct)

    "Paul W Smith" <pwsNOSPAM@twelve.me.uk> wrote in message
    news:43fd9d49$0$9268$ed2619ec@ptn-nntp-reader01.plus.net...
    > On a user form I have a listbox that I am populating by having set it's
    > rowsource to a range on a named range worksheet.
    >
    > I have actions which are run from the userform which add or subtract

    entries
    > from this named range. I have code which changes the named range when new
    > items need to be added/subtracted and this works. However what do I have

    to
    > do to have the listbox updated.
    >
    > It seems to me that I have to physically move the scroll bar of the

    listbox
    > to have the changes recognised.
    >
    > PWS
    >
    >




  3. #3
    Paul W Smith
    Guest

    Re: Listbox - Rowsource

    The rowsource is set at run-time, and works fine until an event on the
    userform causes an update to the rowsource.

    It seems I have to physically make a large change to the scroll bar to make
    the listbox xhnage to reflect the change of it's rowsource.

    PWS


    "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
    news:%232i9H8GOGHA.2012@TK2MSFTNGP14.phx.gbl...
    > If that code is executed outwith the form, all you need to do is set the
    > rowsource property on the userform initialize event
    >
    > Listbox1.RowSource = Range("range_name").Address
    >
    > as it is a string property.
    >
    > --
    > HTH
    >
    > Bob Phillips
    >
    > (remove nothere from email address if mailing direct)
    >
    > "Paul W Smith" <pwsNOSPAM@twelve.me.uk> wrote in message
    > news:43fd9d49$0$9268$ed2619ec@ptn-nntp-reader01.plus.net...
    >> On a user form I have a listbox that I am populating by having set it's
    >> rowsource to a range on a named range worksheet.
    >>
    >> I have actions which are run from the userform which add or subtract

    > entries
    >> from this named range. I have code which changes the named range when
    >> new
    >> items need to be added/subtracted and this works. However what do I have

    > to
    >> do to have the listbox updated.
    >>
    >> It seems to me that I have to physically move the scroll bar of the

    > listbox
    >> to have the changes recognised.
    >>
    >> PWS
    >>
    >>

    >
    >




  4. #4
    Bob Phillips
    Guest

    Re: Listbox - Rowsource

    Paul,

    This worked for me

    Private Sub CommandButton1_Click()
    ActiveWorkbook.Names.Add Name:="range_name", RefersTo:="=A1:A5"
    Me.ListBox1.RowSource = Range("range_name").Address
    End Sub

    Private Sub UserForm_Initialize()
    Me.ListBox1.RowSource = Range("range_name").Address
    End Sub


    --
    HTH

    Bob Phillips

    (remove nothere from email address if mailing direct)

    "Paul W Smith" <pwsNOSPAM@twelve.me.uk> wrote in message
    news:43fda4b9$0$9229$ed2619ec@ptn-nntp-reader01.plus.net...
    > The rowsource is set at run-time, and works fine until an event on the
    > userform causes an update to the rowsource.
    >
    > It seems I have to physically make a large change to the scroll bar to

    make
    > the listbox xhnage to reflect the change of it's rowsource.
    >
    > PWS
    >
    >
    > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
    > news:%232i9H8GOGHA.2012@TK2MSFTNGP14.phx.gbl...
    > > If that code is executed outwith the form, all you need to do is set the
    > > rowsource property on the userform initialize event
    > >
    > > Listbox1.RowSource = Range("range_name").Address
    > >
    > > as it is a string property.
    > >
    > > --
    > > HTH
    > >
    > > Bob Phillips
    > >
    > > (remove nothere from email address if mailing direct)
    > >
    > > "Paul W Smith" <pwsNOSPAM@twelve.me.uk> wrote in message
    > > news:43fd9d49$0$9268$ed2619ec@ptn-nntp-reader01.plus.net...
    > >> On a user form I have a listbox that I am populating by having set it's
    > >> rowsource to a range on a named range worksheet.
    > >>
    > >> I have actions which are run from the userform which add or subtract

    > > entries
    > >> from this named range. I have code which changes the named range when
    > >> new
    > >> items need to be added/subtracted and this works. However what do I

    have
    > > to
    > >> do to have the listbox updated.
    > >>
    > >> It seems to me that I have to physically move the scroll bar of the

    > > listbox
    > >> to have the changes recognised.
    > >>
    > >> PWS
    > >>
    > >>

    > >
    > >

    >
    >




  5. #5
    Paul W Smith
    Guest

    Re: Listbox - Rowsource

    I am sure it does, but you are not understanding my problem.

    Using your example below, with the userform open, i.e. through events fired
    by this you may want to put a button on or something, make a change to the
    range A1:A5. Does the contents of your listbox instantly change to reflect
    this, or do you have to something to make the change register with the
    listbox.

    What event do I fire through code to update the listbox?


    "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
    news:%23uOfCOHOGHA.740@TK2MSFTNGP12.phx.gbl...
    > Paul,
    >
    > This worked for me
    >
    > Private Sub CommandButton1_Click()
    > ActiveWorkbook.Names.Add Name:="range_name", RefersTo:="=A1:A5"
    > Me.ListBox1.RowSource = Range("range_name").Address
    > End Sub
    >
    > Private Sub UserForm_Initialize()
    > Me.ListBox1.RowSource = Range("range_name").Address
    > End Sub
    >
    >
    > --
    > HTH
    >
    > Bob Phillips
    >
    > (remove nothere from email address if mailing direct)
    >
    > "Paul W Smith" <pwsNOSPAM@twelve.me.uk> wrote in message
    > news:43fda4b9$0$9229$ed2619ec@ptn-nntp-reader01.plus.net...
    >> The rowsource is set at run-time, and works fine until an event on the
    >> userform causes an update to the rowsource.
    >>
    >> It seems I have to physically make a large change to the scroll bar to

    > make
    >> the listbox xhnage to reflect the change of it's rowsource.
    >>
    >> PWS
    >>
    >>
    >> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
    >> news:%232i9H8GOGHA.2012@TK2MSFTNGP14.phx.gbl...
    >> > If that code is executed outwith the form, all you need to do is set
    >> > the
    >> > rowsource property on the userform initialize event
    >> >
    >> > Listbox1.RowSource = Range("range_name").Address
    >> >
    >> > as it is a string property.
    >> >
    >> > --
    >> > HTH
    >> >
    >> > Bob Phillips
    >> >
    >> > (remove nothere from email address if mailing direct)
    >> >
    >> > "Paul W Smith" <pwsNOSPAM@twelve.me.uk> wrote in message
    >> > news:43fd9d49$0$9268$ed2619ec@ptn-nntp-reader01.plus.net...
    >> >> On a user form I have a listbox that I am populating by having set
    >> >> it's
    >> >> rowsource to a range on a named range worksheet.
    >> >>
    >> >> I have actions which are run from the userform which add or subtract
    >> > entries
    >> >> from this named range. I have code which changes the named range when
    >> >> new
    >> >> items need to be added/subtracted and this works. However what do I

    > have
    >> > to
    >> >> do to have the listbox updated.
    >> >>
    >> >> It seems to me that I have to physically move the scroll bar of the
    >> > listbox
    >> >> to have the changes recognised.
    >> >>
    >> >> PWS
    >> >>
    >> >>
    >> >
    >> >

    >>
    >>

    >
    >




  6. #6
    Toppers
    Guest

    Re: Listbox - Rowsource

    Paul,
    From very limited testing, the only "event" required is logic
    which recognises the listsource range has changed and then execute Bob's code:

    ActiveWorkbook.Names.Add Name:="range_name", RefersTo:="=A1:A5"
    Userform1.ListBox1.RowSource = Range("range_name").Address

    I placed the above code in a general module and called it on a IF condition.
    Listbox was updated immediately.


    HTH

    "Paul W Smith" wrote:

    > I am sure it does, but you are not understanding my problem.
    >
    > Using your example below, with the userform open, i.e. through events fired
    > by this you may want to put a button on or something, make a change to the
    > range A1:A5. Does the contents of your listbox instantly change to reflect
    > this, or do you have to something to make the change register with the
    > listbox.
    >
    > What event do I fire through code to update the listbox?
    >
    >
    > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
    > news:%23uOfCOHOGHA.740@TK2MSFTNGP12.phx.gbl...
    > > Paul,
    > >
    > > This worked for me
    > >
    > > Private Sub CommandButton1_Click()
    > > ActiveWorkbook.Names.Add Name:="range_name", RefersTo:="=A1:A5"
    > > Me.ListBox1.RowSource = Range("range_name").Address
    > > End Sub
    > >
    > > Private Sub UserForm_Initialize()
    > > Me.ListBox1.RowSource = Range("range_name").Address
    > > End Sub
    > >
    > >
    > > --
    > > HTH
    > >
    > > Bob Phillips
    > >
    > > (remove nothere from email address if mailing direct)
    > >
    > > "Paul W Smith" <pwsNOSPAM@twelve.me.uk> wrote in message
    > > news:43fda4b9$0$9229$ed2619ec@ptn-nntp-reader01.plus.net...
    > >> The rowsource is set at run-time, and works fine until an event on the
    > >> userform causes an update to the rowsource.
    > >>
    > >> It seems I have to physically make a large change to the scroll bar to

    > > make
    > >> the listbox xhnage to reflect the change of it's rowsource.
    > >>
    > >> PWS
    > >>
    > >>
    > >> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
    > >> news:%232i9H8GOGHA.2012@TK2MSFTNGP14.phx.gbl...
    > >> > If that code is executed outwith the form, all you need to do is set
    > >> > the
    > >> > rowsource property on the userform initialize event
    > >> >
    > >> > Listbox1.RowSource = Range("range_name").Address
    > >> >
    > >> > as it is a string property.
    > >> >
    > >> > --
    > >> > HTH
    > >> >
    > >> > Bob Phillips
    > >> >
    > >> > (remove nothere from email address if mailing direct)
    > >> >
    > >> > "Paul W Smith" <pwsNOSPAM@twelve.me.uk> wrote in message
    > >> > news:43fd9d49$0$9268$ed2619ec@ptn-nntp-reader01.plus.net...
    > >> >> On a user form I have a listbox that I am populating by having set
    > >> >> it's
    > >> >> rowsource to a range on a named range worksheet.
    > >> >>
    > >> >> I have actions which are run from the userform which add or subtract
    > >> > entries
    > >> >> from this named range. I have code which changes the named range when
    > >> >> new
    > >> >> items need to be added/subtracted and this works. However what do I

    > > have
    > >> > to
    > >> >> do to have the listbox updated.
    > >> >>
    > >> >> It seems to me that I have to physically move the scroll bar of the
    > >> > listbox
    > >> >> to have the changes recognised.
    > >> >>
    > >> >> PWS
    > >> >>
    > >> >>
    > >> >
    > >> >
    > >>
    > >>

    > >
    > >

    >
    >
    >


  7. #7
    Bob Phillips
    Guest

    Re: Listbox - Rowsource


    "Toppers" <Toppers@discussions.microsoft.com> wrote in message
    news:83CD0F0B-C437-4C72-A384-F215D6D18868@microsoft.com...
    > Paul,
    > From very limited testing, the only "event" required is logic
    > which recognises the listsource range has changed and then execute Bob's

    code:

    Exactly!



  8. #8
    Paul W Smith
    Guest

    Re: Listbox - Rowsource

    How does this work if you have multiple worksheets?

    Your code works if you only have one, but when you add the worksheets
    reference the resource no longer updates!

    Paul Smith


    "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
    news:%23uOfCOHOGHA.740@TK2MSFTNGP12.phx.gbl...
    > Paul,
    >
    > This worked for me
    >
    > Private Sub CommandButton1_Click()
    > ActiveWorkbook.Names.Add Name:="range_name", RefersTo:="=A1:A5"
    > Me.ListBox1.RowSource = Range("range_name").Address
    > End Sub
    >
    > Private Sub UserForm_Initialize()
    > Me.ListBox1.RowSource = Range("range_name").Address
    > End Sub
    >
    >
    > --
    > HTH
    >
    > Bob Phillips
    >
    > (remove nothere from email address if mailing direct)
    >
    > "Paul W Smith" <pwsNOSPAM@twelve.me.uk> wrote in message
    > news:43fda4b9$0$9229$ed2619ec@ptn-nntp-reader01.plus.net...
    >> The rowsource is set at run-time, and works fine until an event on the
    >> userform causes an update to the rowsource.
    >>
    >> It seems I have to physically make a large change to the scroll bar to

    > make
    >> the listbox xhnage to reflect the change of it's rowsource.
    >>
    >> PWS
    >>
    >>
    >> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
    >> news:%232i9H8GOGHA.2012@TK2MSFTNGP14.phx.gbl...
    >> > If that code is executed outwith the form, all you need to do is set
    >> > the
    >> > rowsource property on the userform initialize event
    >> >
    >> > Listbox1.RowSource = Range("range_name").Address
    >> >
    >> > as it is a string property.
    >> >
    >> > --
    >> > HTH
    >> >
    >> > Bob Phillips
    >> >
    >> > (remove nothere from email address if mailing direct)
    >> >
    >> > "Paul W Smith" <pwsNOSPAM@twelve.me.uk> wrote in message
    >> > news:43fd9d49$0$9268$ed2619ec@ptn-nntp-reader01.plus.net...
    >> >> On a user form I have a listbox that I am populating by having set
    >> >> it's
    >> >> rowsource to a range on a named range worksheet.
    >> >>
    >> >> I have actions which are run from the userform which add or subtract
    >> > entries
    >> >> from this named range. I have code which changes the named range when
    >> >> new
    >> >> items need to be added/subtracted and this works. However what do I

    > have
    >> > to
    >> >> do to have the listbox updated.
    >> >>
    >> >> It seems to me that I have to physically move the scroll bar of the
    >> > listbox
    >> >> to have the changes recognised.
    >> >>
    >> >> PWS
    >> >>
    >> >>
    >> >
    >> >

    >>
    >>

    >
    >




+ 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