Dynamic range not resized for RowSource usage
 

I've defined a range name "rng":

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$6553 6),1)

and then used that as the rowsource for a listbox in a userform.

Problem is, when I add a new value to the list, the range is not resized
until I close/reopen the form.

Anybody know a workaround?

Thanks.

Dynamic range not resized for RowSource usage
 

Hi Tim:

I'm assuming you are using a modeless UserForm. This worked for me:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then UserForm1.ListBox1.RowSource = "rng"
End Sub

--

Vasant

"Tim Zych" wrote in message
...
I've defined a range name "rng":

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$6553 6),1)

and then used that as the rowsource for a listbox in a userform.

Problem is, when I add a new value to the list, the range is not resized
until I close/reopen the form.

Anybody know a workaround?

Thanks.

Dynamic range not resized for RowSource usage
 

rowsource doesn't work very well with defined names - at least not very
dynamically. Reassign the rowsource should make it use the latest value.

--
Regards,
Tom Ogilvy

"Tim Zych" wrote in message
...
I've defined a range name "rng":

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$6553 6),1)

and then used that as the rowsource for a listbox in a userform.

Problem is, when I add a new value to the list, the range is not resized
until I close/reopen the form.

Anybody know a workaround?

Thanks.

Dynamic range not resized for RowSource usage
 

Vasant, Tom:
I thought I tried that and it didn't work. Guess not. Programmatically
resetting it seems to fix it.
Thanks

"Tom Ogilvy" wrote in message
...
rowsource doesn't work very well with defined names - at least not very
dynamically. Reassign the rowsource should make it use the latest value.

--
Regards,
Tom Ogilvy

"Tim Zych" wrote in message
...
I've defined a range name "rng":

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$6553 6),1)

and then used that as the rowsource for a listbox in a userform.

Problem is, when I add a new value to the list, the range is not resized
until I close/reopen the form.

Anybody know a workaround?

Thanks.