+ Reply to Thread
Results 1 to 7 of 7

Macro to open worksheet based on drop down selection

  1. #1
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,224

    Macro to open worksheet based on drop down selection

    Hi all

    wondering if i can get some assistance...i require a macro where the
    1) "index" sheet will open always open
    2) user selects name from cell E10 of company name
    b) once user name selects name, the macro will open the company listed main sheet and any other data sheets that relate to drop down name

    3) "index" sheet, if cell E11 = yes, then copy all active tabs open of selected company (exlcuding index and details) and create them in new workbook

    can someone kindly assist? thx

    i have attached sample

    FYI: the details page would have input of all company names and their respective additional tabs that are hidden/unhidden when company name selected. also, more company names will be added
    Attached Files Attached Files

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Macro to open worksheet based on drop down selection

    Hi there,

    Take a look at the attached version of your workbook and see if it does what you need - it uses the following code in the "ThisWorkbook" VBA CodeModule:

    Please Login or Register  to view this content.

    the following code in the VBA CodeModule of the "Index" worksheet:

    Please Login or Register  to view this content.

    and the following code in a standard VBA CodeModule:

    Please Login or Register  to view this content.
    The highlighted values may be altered to suit your requirements.


    Hope this helps - please let me know how you get on.

    Regards,

    Greg M
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,224

    Re: Macro to open worksheet based on drop down selection

    hi there

    this is fantastic

    couple of qs:
    1) if i wanted to hide all sheets, can a macro be added for that?
    2) if i add more companies, do I need to simply add their names in the "detail" tab, or do i need to define it in the macro each time i add companies and the respective worksheets?

    thxs so much

  4. #4
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Macro to open worksheet based on drop down selection

    Hi again,

    The attached workbook should do what you want. It contains a "Hide All Worksheets" button which will hide all worksheets except the "Index" worksheet - a workbook must contain at least one visible worksheet. The button runs the following code:

    Please Login or Register  to view this content.
    I've also declared the "gsSELECTED_COMPANY" constant as Public rather than as routine-level.

    The Company names are now contained in a dynamic Named Range ("ptrCompanies") which can contain up to 50 entries - this means that you just need to enter new Company Names immediately below existing Company Names, and the entries in the dropdown list on the "Index" worksheet will be updated automatically - the names should be entered on consecutive rows (i.e. without any blank rows separating names).


    Hope this helps - as before, please let me know how you get on.

    Regards,

    Greg M
    Attached Files Attached Files

  5. #5
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Macro to open worksheet based on drop down selection

    Hi again,

    Many thanks for the Reputation increase - much appreciated!

    Best regards,

    Greg M

  6. #6
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,224

    Re: Macro to open worksheet based on drop down selection

    Hi Greg

    THanks for the great help!

    I added the macro to my master template. Just wondering, how do I assign the "hide all sheets" macro to a radio button? I noticed it's "private", however, even if I remove the private portion in the macro, i cant seem to locate the macro to assign? Thanks

    also. i may require an add to the macro, i will let you know soon. thanks again!

  7. #7
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Macro to open worksheet based on drop down selection

    Hi again,

    If you temporarily comment out the "Option Private Module" statement at the top of the VBA CodeModule you should see the "HideAllWorksheets" routine in the list of available macros when you try to assign a macro to a control.

    Feel free to ask if you need anything further - I'll probably check this thread for the next few days, so send me a Private Message after that.

    Regards,

    Greg M

+ 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. Extract data from one worksheet to another based on selection in drop-down menu
    By deathrowdreams in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-22-2017, 08:52 AM
  2. Cut and paste row from worksheet to another worksheet based on drop down selection
    By faizanin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-01-2014, 08:07 AM
  3. [SOLVED] Using Command Button to Open Worksheet Based on ComboBox Selection
    By laura_jean in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-10-2014, 09:23 AM
  4. [SOLVED] Reference another worksheet, based on the drop down selection of another cell
    By VTX in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-25-2013, 11:27 AM
  5. Replies: 3
    Last Post: 03-10-2013, 12:56 PM
  6. Replies: 9
    Last Post: 01-30-2013, 01:25 PM
  7. Drop Down Menu Selection & New Value Worksheet based on Cell
    By shelton_4 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-15-2012, 09:46 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