+ Reply to Thread
Results 1 to 11 of 11

Creating a drop down menu to select a sheet

  1. #1
    Registered User
    Join Date
    03-19-2015
    Location
    Newcastle, England
    MS-Off Ver
    365
    Posts
    33

    Creating a drop down menu to select a sheet

    Hi, I am looking for a solution to have a drop down menu that selects a certain sheet within my spreadsheet. Please bare with me as my knowledge of Excel is fairly basic.
    I have designed a spreadsheet to document the monthly checks of the work vans at the request of the boss.
    I'm currently using click buttons to link to the relevant sheet. Here is the current setup of it:
    \1

    These are the macros being used:
    Please Login or Register  to view this content.
    Instead I'm looking for a drop down menu so that I have one per year e.g. 2016, 2017 etc then when I click on that it it gives the months so that when I click on it it takes me to the relevant sheet.
    It would be great if it automatically added any new sheets I created.
    I'm using windows 7 and Excel 2007 if it makes a difference.
    Any help is greatly appreciated Thanks.
    Last edited by digitised; 01-23-2017 at 08:28 AM.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Creating a drop down menu to select a sheet

    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

    One thought though. Whenever I see that a system has separate sheets for things like months/departments/staff names/..etc alarm bells ring since almost without exception this is the wrong way to hold your data if you want to do any serious analysis or summarisation. ALWAYS keep data on a single sheet in a normalised two dimensional table consisting of column labels for different fields (or types/categories) of data and rows to record every unique piece of information.

    In your case if you do keep all your data in one table you'll need to add an additional column to hold the date of the record so that a particular month can be extracted for it when necessary for a report. With a single database you also have the advantage of being able to use the powerful pivot table functionality.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    03-19-2015
    Location
    Newcastle, England
    MS-Off Ver
    365
    Posts
    33

    Re: Creating a drop down menu to select a sheet

    Quote Originally Posted by Richard Buttrey View Post
    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

    One thought though. Whenever I see that a system has separate sheets for things like months/departments/staff names/..etc alarm bells ring since almost without exception this is the wrong way to hold your data if you want to do any serious analysis or summarisation. ALWAYS keep data on a single sheet in a normalised two dimensional table consisting of column labels for different fields (or types/categories) of data and rows to record every unique piece of information.

    In your case if you do keep all your data in one table you'll need to add an additional column to hold the date of the record so that a particular month can be extracted for it when necessary for a report. With a single database you also have the advantage of being able to use the powerful pivot table functionality.
    Thanks Richard, spreadsheet is available here
    HTML Code: 
    I'm not concerned if all data is on 1 sheet I only did it this way because it was the only way I knew how to separate the data by month & be able to link to it easily.

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Creating a drop down menu to select a sheet

    Please upload the workbook to this forum

    Many of us prefer not to use file hosting sites of unknown provenance.

  5. #5
    Registered User
    Join Date
    03-19-2015
    Location
    Newcastle, England
    MS-Off Ver
    365
    Posts
    33

    Re: Creating a drop down menu to select a sheet

    Figured it out.
    Last edited by digitised; 01-31-2017 at 05:26 AM.

  6. #6
    Registered User
    Join Date
    03-19-2015
    Location
    Newcastle, England
    MS-Off Ver
    365
    Posts
    33

    Re: Creating a drop down menu to select a sheet

    Bump.
    Managed to upload spreadsheet.

  7. #7
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: Creating a drop down menu to select a sheet

    It's pointless uploading a password protected workbook...

  8. #8
    Registered User
    Join Date
    03-19-2015
    Location
    Newcastle, England
    MS-Off Ver
    365
    Posts
    33

    Re: Creating a drop down menu to select a sheet

    Quote Originally Posted by cytop View Post
    It's pointless uploading a password protected workbook...
    Sorry I forgot I had password protected it. It should be removed now.

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Creating a drop down menu to select a sheet

    Please Login or Register  to view this content.
    Drop down on menu for year and month

    Tab "Tables" added for list of months but could hard code as list for months drop down
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    03-19-2015
    Location
    Newcastle, England
    MS-Off Ver
    365
    Posts
    33

    Re: Creating a drop down menu to select a sheet

    Quote Originally Posted by JohnTopley View Post
    Please Login or Register  to view this content.
    Drop down on menu for year and month

    Tab "Tables" added for list of months but could hard code as list for months drop down
    Thanks JohnTopley, that's exactly what I was looking for

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Creating a drop down menu to select a sheet

    You are very welcome and thank you for the rep.

+ 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. Select from a drop down menu using VBA
    By Johnnyv015 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-18-2014, 02:55 PM
  2. Replies: 1
    Last Post: 02-07-2013, 11:03 AM
  3. select a sheet from a drop down menu and move the row in the selected sheet
    By AlienPump in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-27-2012, 02:21 PM
  4. Creating a Drop Down Menu
    By JoeHan in forum Excel General
    Replies: 5
    Last Post: 11-29-2011, 11:29 AM
  5. Creating a drop down menu
    By djt in forum Excel General
    Replies: 3
    Last Post: 11-21-2009, 04:54 PM
  6. Creating Personal Menu Drop Down
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-29-2009, 10:25 PM
  7. select multiple items from drop-down menu
    By jcavigli in forum Excel General
    Replies: 2
    Last Post: 05-25-2008, 11:50 AM

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