Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
range usage question | Excel Worksheet Functions | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Variable usage in Range Command | Excel Programming | |||
Listbox Rowsource Headings Multi columns Copy Range If Then Conditional | Excel Programming | |||
ComboBox RowSource --- can I use a userform OWC10 spreadsheet range? | Excel Programming |