Dynamic Named Range in Userform ListBox

MSingh
3StarLounger
Posts: 366
Joined: 12 May 2010, 06:49

Dynamic Named Range in Userform ListBox

Post by MSingh »

Hi,

Can a dynamic named range be assigned as the Row Source at design time to Listbox1 userform?
If have tried MyList:
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A2001),1)

But i get an error when i try to set MyList as the Rowsource property.

The objective is that ListBox1 must only display rows with data in the range A2:A2001

Please advise.

Kind Regards
Mohamed

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Dynamic Named Range in Userform ListBox

Post by HansV »

You should set the RowSource to MyList, not to =MyList. See the attached sample workbook.
Test.xls
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

MSingh
3StarLounger
Posts: 366
Joined: 12 May 2010, 06:49

Re: Dynamic Named Range in Userform ListBox

Post by MSingh »

Hi Hans,

Thank you, thank you, thank you...

That prevents soo many attempts at workarounds & the user making invalid selections setting-off another macro!

I did google this before posting here, but never found your solution.

Kind Regards
Mohamed