Listbox - Rowsource

[Paul W Smith]

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

 

[Bob Phillips]

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]

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]

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]

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?

 

[Toppers]

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

 

[Bob Phillips]

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!

 

[Paul W Smith]

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