Filling a list box with a dynamic named range

**Alicita** · 02-01-2011, 01:23 PM

Hello guys!

I'm having problems with this: I need to fill a list box (from the controls toolbox) when a command button is clicked with a named range from another sheet, this range is dynamic. For some reason, none of the codes I've tried are working! Is there any short straighforward piece of VB code I can use to do this??

My dynamic named range is defined by

Please Login or Register  to view this content.

And I intend to copy the VB code into my sheet under

Please Login or Register  to view this content.

That's all, thank you!

**jaslake** · 02-01-2011, 01:43 PM

Hi Alicita

Is the ListBox on a UserForm?

**Alicita** · 02-01-2011, 01:48 PM

Hi!

No, it's in the worksheet

**jaslake** · 02-01-2011, 01:56 PM

Hi Alicita

Probably something like this

Please Login or Register  to view this content.

where "Stuff" is the name of your named range.

**Alicita** · 02-01-2011, 02:12 PM

I tried it, but an error prompts: Run time error 438, object doesn't support this property or method

?

**jwright650** · 02-01-2011, 02:14 PM

Add this code to your Workbook and name your range "lbxlist"

Please Login or Register  to view this content.

see example
A listbox on Sheet1 filled with list of names in Column B on Sheet2

**snb** · 02-01-2011, 02:16 PM

Please Login or Register  to view this content.

VBA is better in creating dynamic ranges than Excel's formulae.

Avoid using additem to populate a combobox/Listbox with multiple items (it isn't designed for this purpose, and it is extremely slow). List is especially designed for this task (see VBEditor's help).

**jaslake** · 02-01-2011, 02:20 PM

Hi Alicita

Is the ListBox a Forms Control ListBox or an Activex Controls ListBox?

**Alicita** · 02-01-2011, 02:21 PM

Now I'm getting "could not set the listcursor property. Not enough storage is available to complete this operation"

It's from ActiveX controls ListBox

**snb** · 02-01-2011, 02:27 PM

Please post your workbook.

**jwright650** · 02-01-2011, 02:29 PM

Does my example run Ok for you?...or is that where you are getting the error message?

**jaslake** · 02-01-2011, 02:34 PM

Hi Alicita

Works for me...see attached.

**Alicita** · 02-01-2011, 02:37 PM

@jwright650 : Your code did populate the list but It doesn't refresh it, I have to change of worksheets and then It shows refreshed, thanks anyway

I feel I'm closer to the answer
@snb: I'll post it later

thanks
@jaslake : Your code isn't working

thanks anyway!

**Alicita** · 02-01-2011, 02:41 PM

@jaslake: your code is working now! It was my mistake, but the problem is that it doesn't refresh*, how can I do that?

* I want it to refresh everytime I click the command button (the source list changes, since is dynamic)

**jaslake** · 02-01-2011, 02:53 PM

Try this

Please Login or Register  to view this content.

**Alicita** · 02-01-2011, 03:46 PM

double post~

**Alicita** · 02-01-2011, 03:48 PM

It isn't refreshing

weird thing is, that everytime I change of tab (worksheet) and come back to my sheet with my listbox, It's refreshed :S

**jaslake** · 02-01-2011, 03:54 PM

Hi Alicita

How is the Dynamic Named Range getting changed? What's the process and how and where in the process does it get changed?

We may need a copy of your workbook to fully understand what you're doing.

**Alicita** · 02-01-2011, 04:09 PM

Problem Solved!

It was a problem with Application.ScreenUpdating now Is good

THANK YOU!!

**jaslake** · 02-01-2011, 04:12 PM

Good detective work. Glad you got it working.

Filling a list box with a dynamic named range

LinkBack

Thread Tools

Rate This Thread

Display

Filling a list box with a dynamic named range

Re: Filling a list box with a dynamic named range

Re: Filling a list box with a dynamic named range

Re: Filling a list box with a dynamic named range

Re: Filling a list box with a dynamic named range

Re: Filling a list box with a dynamic named range

Re: Filling a list box with a dynamic named range

Re: Filling a list box with a dynamic named range

Re: Filling a list box with a dynamic named range

Re: Filling a list box with a dynamic named range

Re: Filling a list box with a dynamic named range

Re: Filling a list box with a dynamic named range

Re: Filling a list box with a dynamic named range

Re: Filling a list box with a dynamic named range

Re: Filling a list box with a dynamic named range

Re: Filling a list box with a dynamic named range

Re: Filling a list box with a dynamic named range

Re: Filling a list box with a dynamic named range

Re: Filling a list box with a dynamic named range

Re: Filling a list box with a dynamic named range

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions