Dynamic range not resized for RowSource usage

[Tim Zych]

I've defined a range name "rng":

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$65536),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.

 

[Vasant Nanavati]

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

 

[Tom Ogilvy]

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

 

[Tim Zych]

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