+ Reply to Thread
Results 1 to 8 of 8

Use Named Range to set ComboBox List?

  1. #1
    Forum Contributor
    Join Date
    03-26-2015
    Location
    Cookeville, TN
    MS-Off Ver
    Excel 2007
    Posts
    155

    Use Named Range to set ComboBox List?

    It was suggested in a response to an earlier post that I could use a range to set the ComboBox List property:
    Please Login or Register  to view this content.
    I'd like to use a named range to set the ComboBox list. But when I try:
    Please Login or Register  to view this content.
    (where RangeName is a dynamic range name), I get:

    Run-time error '381':
    Could not set the List property. Invalid property array index.

    Why can't I use a dynamic named range to set the List? Is there any way to fix this? I've searched in vain online, and every example I can find simply shows the AddItem method being used to add single items of plain text (like "Bananas"). No examples of using named ranges! Help!

    Oh, and as a follow-up, assuming I can figure out how to get my ComboBoxes properly filled (using my named range), if I then want to remove whichever item was selected in ComboBox1 from the list of choices in ComboBox2, can I use:
    Please Login or Register  to view this content.
    or maybe:
    Please Login or Register  to view this content.
    or maybe even simply:
    Please Login or Register  to view this content.
    (I realize that the above would have to be in something like the ComboBox2_Click event, not in the UserForm initialization)

  2. #2
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,904

    Re: Use Named Range to set ComboBox List?

    Try this one.

    Please Login or Register  to view this content.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  3. #3
    Forum Contributor
    Join Date
    03-26-2015
    Location
    Cookeville, TN
    MS-Off Ver
    Excel 2007
    Posts
    155

    Re: Use Named Range to set ComboBox List?

    That gives me:

    Run-time error '70':
    Permission denied.

    Huh???

  4. #4
    Forum Contributor
    Join Date
    03-26-2015
    Location
    Cookeville, TN
    MS-Off Ver
    Excel 2007
    Posts
    155

    Re: Use Named Range to set ComboBox List?

    I should note that I also tried:
    Please Login or Register  to view this content.
    and I get a compile error (saying ListFillRange is not a valid property). What gives? Was ListFillRange deleted from Excel 2016?

  5. #5
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,207

    Re: Use Named Range to set ComboBox List?

    If I recall from the other thread, you have a series of combo boxes and the goal is that the user chooses an item from the first, the second is populated with all the entries from the first less the one selected, and so on.

    1) To fill a Combobox with a named range, dynamic or fixed. you can use code like this
    Please Login or Register  to view this content.
    Note that this assumes that DynamicRange is a single column or values.
    Note also that no method of filling a combo box will automaticaly update as the size of the dynamic range changes. You must call your "fill the combo box" code every time that the values in the named ranges changes.

    2) To get cascading combo boxes, with each box showing the unselected values from the previous boxes:

    I would make the controls so the the .Tag property of a control had the name of the subordinate combo box.
    So ComboBox1's .Tag property would be "ComboBox2". ComboBox2's .Tag property would be "ComboBox3", etc.
    This would be done at Design time.

    I would then use code like this in the user form's code module.
    Please Login or Register  to view this content.
    In the attachment, I used a Dynamic Named Range to fill the initial box. Also, the user form uses ListBoxes rather than combo boxes (so one can see the list without having to click the drop button). The coding is the same for both List and ComboBoxes.
    Attached Files Attached Files
    Last edited by mikerickson; 07-02-2017 at 11:40 AM.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  6. #6
    Forum Contributor
    Join Date
    03-26-2015
    Location
    Cookeville, TN
    MS-Off Ver
    Excel 2007
    Posts
    155

    Re: Use Named Range to set ComboBox List?

    Thanks!!! I will give that a try!

  7. #7
    Forum Contributor
    Join Date
    03-26-2015
    Location
    Cookeville, TN
    MS-Off Ver
    Excel 2007
    Posts
    155

    Re: Use Named Range to set ComboBox List?

    Mikerickerson,
    Unfortunately, when I try:
    Please Login or Register  to view this content.
    I get:

    Run-time error -2147352571 (800200005)':
    Could not set the Value property. Type mismatch.

    My dynamic list refers to 16 contiguous cells in a single column, that are all in General format and contain text. How can this give me a "type mismatch"?

    And I should be setting the LIST for the ComboBox, not its VALUE, right? But, as noted earlier,
    Please Login or Register  to view this content.
    doesn't work either (gives me Run-time error '70': Permission denied).

    Your sample worksheet works fine, but mine doesn't. I'm guessing it must be in how I'm defining the dynamic range. I'll try using a definition more like your sample and see if that works.

    UPDATE:
    Tried changing my dynamic range definition, it still bombs with the Run-time error 70: Permission denied. Any idea why I get this error?
    My (new) dynamic range definition is:
    Please Login or Register  to view this content.
    FINAL (Hopefully!) UPDATE:
    I figured out why I'm getting the Error 70. I still had the RowSource property of my ComboBox filled in, so it wouldn't let me redefine the list. [Smack Forehead!!]
    I had previously deleted the RowSource, but I must have closed the file without saving that change! Sorry for wasting everyone's time!!
    Last edited by Merf; 07-02-2017 at 06:16 PM. Reason: Yet ANOTHER Update!

  8. #8
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,207

    Re: Use Named Range to set ComboBox List?

    If the COUNTA evaluates to 0, then the named will return an error instead of a range.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Variable for named range in combobox
    By bevc in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-02-2016, 05:36 AM
  2. VB UserForm ComboBox List Based On Dynamic Named Range in Worksheet
    By huntethic in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-14-2015, 02:45 PM
  3. [SOLVED] Connecting named range with ComboBox
    By drrazor in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-16-2014, 04:14 PM
  4. [SOLVED] ComboBox add entry to Named Range if not in list
    By pjbassdc in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-11-2014, 03:36 PM
  5. [SOLVED] Populate combobox named range
    By zardof in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-14-2012, 04:00 AM
  6. setting a combobox list property to a named range
    By matpj in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-06-2012, 11:52 AM
  7. Dynamically assign ComboBox.List from named range areas
    By Paul Martin in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-03-2005, 01:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1