+ Reply to Thread
Results 1 to 11 of 11

Select Sheet based on Combo box selection

  1. #1
    Registered User
    Join Date
    11-27-2012
    Location
    Birmingham England
    MS-Off Ver
    Excel 2010
    Posts
    10

    Lightbulb Select Sheet based on Combo box selection

    I am trying to set up a (simple?) routine that asks a user to select their name from a combo box then go to a sheet based on their selection. So the name they select in the combo box goes into cell c11 and I use a vlookup formula "=VLOOKUP(C11,PEOPLEDATA,5)" on the range of alphabetically listed names to give me the name of the sheet I want to goto in cell d11. Then I'm stuck for the code that will take me to the page identified in cell d11.

    Can it be done directly from the combo box selection?

    Ultimately this will be in a protected / shared workbook (2010)

  2. #2
    Forum Contributor
    Join Date
    10-18-2012
    Location
    Telford, England
    MS-Off Ver
    Excel 2010 (2003)
    Posts
    294

    Re: Select Sheet based on Combo box selection

    You cannot change sheet with any formula I am aware of.
    You can easily do it with VBA triggered at the Combobox.change event.

  3. #3
    Registered User
    Join Date
    11-27-2012
    Location
    Birmingham England
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Select Sheet based on Combo box selection

    Sorry, bit of a total noob, how exactly?

    I ask the user to select his name from the list in the combobox, the result given is his name. The action I want is to go the the tab (sheet) identified by the 5th column in the list range

    Sheets (??????).Select
    Last edited by Dave Herbert; 11-30-2012 at 05:06 AM.

  4. #4
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Select Sheet based on Combo box selection

    hi Dave, if sheet name is in D11:

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    11-27-2012
    Location
    Birmingham England
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Select Sheet based on Combo box selection

    Thank you, I will try that out. My solution ran to over 90 lines!

  6. #6
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Select Sheet based on Combo box selection

    please check attachment, press Go button, change value in D11
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Select Sheet based on Combo box selection

    if you post sample file we could work something out for you

  8. #8
    Registered User
    Join Date
    11-27-2012
    Location
    Birmingham England
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Select Sheet based on Combo box selection

    Hi waterserv, your first solution works just once, on subsequent selections I get an error. The attached file "Dave.xlsm" isn't a user selection combobox. This is a sample of the code I'm using

    Please Login or Register  to view this content.

  9. #9
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Select Sheet based on Combo box selection

    Quote Originally Posted by Dave Herbert View Post
    Then I'm stuck for the code that will take me to the page identified in cell d11.
    The file posted was an answer to your remark in post #1. The file works as expected within the set conditions.
    As for the example with the combobox the sample file is attached
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    11-27-2012
    Location
    Birmingham England
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Select Sheet based on Combo box selection

    Thank you watersev, that looks brilliant!

  11. #11
    Forum Contributor
    Join Date
    10-18-2012
    Location
    Telford, England
    MS-Off Ver
    Excel 2010 (2003)
    Posts
    294

    Re: Select Sheet based on Combo box selection

    Thanks watersev for your help, I have been too busy to log in.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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