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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >>
> >>
> >
> >
>
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:%[email protected]...
>> > 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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
>> >>
>> >>
>> >
>> >
>>
>>
>
>

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" <[email protected]> wrote in message
> news:%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> >> news:%[email protected]...
> >> > 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" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> 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
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>
>

"Toppers" <[email protected]> wrote in message
news:[email protected]...
> 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!

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:%[email protected]...
>> > 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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
>> >>
>> >>
>> >
>> >
>>
>>
>
>