RowSource in VB Editor can't keep up

[Fallenwing]

I have created a project with a custom form. The form contains list boxes that get their list items from cells in Excel spreadsheets. In the properties window (in the Visual Basic Editor) for this form, under the RowSource heading I list the cells that I want to appear in the list box. Entered now is Sheet1!A11:B220. The problem is that the excel data keeps growing. I find I am continually updating the cell range to make it keep up. I don't really want to add 1000 blank rows into the list box either. Is there a way I can enter code that will search for the number of cells inside the Property Window of the Visual Basic Editor?

 

[SkipVought]

Hi,

Name the list range using the OFFSET worksheet function in menu item Insert/Name/Define, like...

=offset(sheet1!A2,0,0,counta(sheet1!$A:$A)-1,1)

THen use the range name in the listfillrange



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Fallenwing]

I named the range like you said, I can't seem to get ListFillRange to work. Are there any other ways of doing this?

 

[SkipVought]

Listen, it works!

Reposition you list to row 1. If you have a heading for your list

1. using Insert/Name/Define you assign a NAME in the Names in Workbook textbox

2. in the RefersTo textbox you enter the OFFSET formula,

=OFFSET(A,B,C,D,E)

where
A is the worksheet!STARTING ADDRESS reference of your list
B & C are ZERO,
D is COUNTA(F)-X
where F is the worksheet!column reference of your list and
X is ROW that your list starts in MINUS ONE (My list starts in Row 2 since row 1 is a heading value)
F is 1

AND there is NOTHING ABOVE & BELOW the list.

Then use the NAME you entered in step 1 in the ListFillRange property.

AND THEN

read up on the OFFSET worksheet function =-- VERY POWERFUL!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com