+ Reply to Thread
Results 1 to 8 of 8

Macro to create a dynamic drop down list

  1. #1
    Registered User
    Join Date
    06-17-2015
    Location
    Philadelphia, USA
    MS-Off Ver
    2010
    Posts
    11

    Macro to create a dynamic drop down list

    Hi Gurus,

    I need to create a drop down list in my work book. The requirement is as follows.

    Sheet1 has the list of departments (which are not unique).

    I need a dropdown list in sheet2 with the department list from sheet1 to be put into once cell.

    I need to write a macro that will filter the duplicate records and create a sorted (ascending ) dropdownlist.

    I have written this but it shows an error in the highlighted line. Further, it is not recognizing the sheet names. Variables (stvalue and i) record the range of the list in sheet 1.
    Please Login or Register  to view this content.

  2. #2
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,528

    Re: Macro to create a dynamic drop down list

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    As a reminder, once your original request has been fulfilled please mark this thread as SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.

    If you are happy with my help, then please consider clicking the add reputation button in the lower left hand corner of this post.

  3. #3
    Registered User
    Join Date
    06-17-2015
    Location
    Philadelphia, USA
    MS-Off Ver
    2010
    Posts
    11

    Re: Macro to create a dynamic drop down list

    Hi,

    I am attaching the workbook and what I want with the Macro.Department example.xlsx

    Note: I haven't put the macro in.

  4. #4
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2019
    Posts
    6,559

    Re: Macro to create a dynamic drop down list

    Nice sample: all the numbers in the column Department are unique>
    Joking?
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  5. #5
    Registered User
    Join Date
    06-17-2015
    Location
    Philadelphia, USA
    MS-Off Ver
    2010
    Posts
    11

    Re: Macro to create a dynamic drop down list

    Hi Keebelah,

    I am sorry.Here is the new one.Department example.xlsx

  6. #6
    Registered User
    Join Date
    06-17-2015
    Location
    Philadelphia, USA
    MS-Off Ver
    2010
    Posts
    11

    Re: Macro to create a dynamic drop down list

    HI, I changed the code I gave you guys a little, but now it is showing the next line as an error.

    Please Login or Register  to view this content.
    Last edited by wunderbarre; 11-09-2015 at 12:14 PM.

  7. #7
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,528

    Re: Macro to create a dynamic drop down list

    Here is the code and the worksheet.
    Some things to know:
    There is a new sheet called List, this will be the sheet with the data source for the drop down. This sheet can be hidden or very hidden. If you want very hidden then in the project explorer from the view menu choose properties and left click to select the sheet and in the properties window next to Visible choose very hidden.

    The macro code can be moved to the activate event for the sheet with the drop down so that it will run every time that sheet is activated or you can leave it where it is and just run it as needed. There is a dynamic named range on the list sheet which is referenced in the drop down as the source for data.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by skywriter; 11-09-2015 at 01:08 PM.

  8. #8
    Registered User
    Join Date
    06-17-2015
    Location
    Philadelphia, USA
    MS-Off Ver
    2010
    Posts
    11

    Re: Macro to create a dynamic drop down list

    Works a treat.

    Thank you, sir.

+ 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. Create a Dynamic Drop Down List
    By sinspawn56 in forum Excel General
    Replies: 1
    Last Post: 01-05-2015, 09:07 PM
  2. create dynamic drop down list for large list of data
    By Dariusd7 in forum Excel General
    Replies: 2
    Last Post: 05-10-2014, 04:39 AM
  3. Create a dependent drop down list with dynamic list
    By JSmith1504 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-01-2014, 09:15 AM
  4. Way to create a dynamic drop down list.
    By jensca in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-17-2013, 10:55 AM
  5. Replies: 0
    Last Post: 04-15-2013, 09:22 AM
  6. create a dynamic drop down list based on specific criteria
    By stevegrobertson in forum Excel General
    Replies: 4
    Last Post: 02-21-2012, 08:07 PM
  7. Excel VBA Macro to create a drop down list
    By jeffgraser in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-23-2009, 08:08 PM

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