Dutch Gemini's Weblog Pages

November 27, 2009

Excel ‘Could not get the List property’ error.

Filed under: Excel,Programming,VBA — dutchgemini @ 2:55 pm

If you ever bump into the ‘Could not get the List property. Not enough storage space is available to complete this operation‘ error in Excel, then you are likely to be using a ListBox either as an ActiveX control on the sheet or as a ListBox control on a UserForm, that is linked to a Range on one of your worksheets and you trying to obtain the ListBox’s .Value property (usually the default property) .

This error can occur for 2 reasons:
  1. your ListBox does not have a valid .ListFillRange, i.e. empty or pointing to an invalid Range. The .ListFillRange property tells Excel the Range of a sheet in the workbook that contains the data used to populate the list
  2. your ListBox contains a textual entry that is not part of the accepted values

An invalid Range occurs when for instance you delete the sheet or the range it points to. Excel is unable to make the match and returns the above error.

In my case the error came up while using an ActiveX ComboBox with 2 columns pointing to a 2 column range and hiding the first column (Setting the .ColumnWidths to ‘0 pt; 150 pt‘).

Dutch

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: