+ Reply to Thread
Results 1 to 3 of 3

Assigning dynamic named ranges to listfillrange via macro code

  1. #1
    Registered User
    Join Date
    01-29-2007
    Posts
    92

    Assigning dynamic named ranges to listfillrange via macro code

    Ok, I believe what I'm trying to do is not complicated and that I'm simply missing some sintax but I will have to explain in full what I'm trying to do.

    So I've created two comboboxes in Sheet1. What I would like to do is make the listfillrange in the second combobox depend on the selection in the first combobox.

    Example.

    Combobox1:
    Numbers
    Letters

    If i select "Numbers", the listfillrange in the second combobox will display numbers 1-10

    If i select "Letters", the second combobox will display letters a-z

    In order to do this, the code for the first combobox assignes the listfillrange of the second combobox using named ranges which i've created but this is where i get an error.

    If cells(1,1) = "Numbers" then (cell A1 is linked to the combobox and displays the same value)
    ActiveSheet.Shapes("ComboBox").Select
    With Selection
    .ListFillRange = "I would like to insert the named range here"
    End Select

    What the proper syntax here, I've been through forums for hours. (Note: I need to be able to call the named range even if its not on the same sheet as the combobox)

    Please Help
    Thanks

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello Schwizer,

    Add a Standard VBA Module to your project and then copy and paste the code into it. You will need to change the names of the Drop Downs to match the names on your work sheet. Assign the Macro to the First DropDown only. The one the user selects Letters or Numbers. THe names of the Named Ranges are Numbers and Letters. You can change these to match the names you are using, if they are different.

    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

  3. #3
    Registered User
    Join Date
    01-29-2007
    Posts
    92

    Thank you

    That worked well. I couldn't get the .list(.listindex) command to work but thats ok.

    Thanks for the help. Lots of great posts here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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