Creating a drop down menu to select a sheet

**digitised** · 01-23-2017, 08:07 AM

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:

Sub Menu()
    Sheets("Menu").Select
End Sub
Sub BlankForms()
    Sheets("Blank Forms").Select
End Sub
Sub June2016()
    Sheets("Jun16").Select
End Sub
Sub July2016()
    Sheets("Jul16").Select
End Sub
Sub August2016()
    Sheets("Aug16").Select
End Sub
Sub September2016()
    Sheets("Sept16").Select
End Sub
Sub October2016()
    Sheets("Oct16").Select
End Sub
Sub November2016()
    Sheets("Nov16").Select
End Sub
Sub December2016()
    Sheets("Dec16").Select
End Sub
Sub January2017()
    Sheets("Jan17").Select
End Sub
Sub February2017()
    Sheets("Feb17").Select
End Sub
Sub SaveWorkBook()
    ActiveWorkbook.Save
End Sub
Sub ExitExcel()
    Application.Quit
End Sub

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.

**Richard Buttrey** · 01-23-2017, 09:09 AM

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.

**digitised** · 01-23-2017, 09:48 AM

Originally Posted by Richard Buttrey

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.

**Richard Buttrey** · 01-23-2017, 12:23 PM

Please upload the workbook to this forum

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

**digitised** · 01-24-2017, 06:50 AM

Figured it out.

**digitised** · 01-25-2017, 05:45 AM

Bump.
Managed to upload spreadsheet.

**cytop** · 01-25-2017, 05:56 AM

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

**digitised** · 01-25-2017, 06:06 AM

Originally Posted by cytop

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

Sorry I forgot I had password protected it. It should be removed now.

**JohnTopley** · 01-25-2017, 09:21 AM

Sub Get_sheet()
Dim ws As String
ws = Range("Sheet_month") & Right(Range("Sheet_year"), 2)
If Not WorksheetExists(ws) Then
    MsgBox "Worksheet " & ws & " not found", vbExclamation, "Error"
    Exit Sub
End If
Sheets(ws).Select

End Sub
Function WorksheetExists(WSName As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(WSName).Name = WSName
On Error GoTo 0
End Function

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

**digitised** · 01-25-2017, 09:56 AM

Originally Posted by JohnTopley

Sub Get_sheet()
Dim ws As String
ws = Range("Sheet_month") & Right(Range("Sheet_year"), 2)
If Not WorksheetExists(ws) Then
    MsgBox "Worksheet " & ws & " not found", vbExclamation, "Error"
    Exit Sub
End If
Sheets(ws).Select

End Sub
Function WorksheetExists(WSName As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(WSName).Name = WSName
On Error GoTo 0
End Function

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

**JohnTopley** · 01-25-2017, 10:03 AM

You are very welcome and thank you for the rep.

Creating a drop down menu to select a sheet

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Creating a drop down menu to select a sheet

Re: Creating a drop down menu to select a sheet

Re: Creating a drop down menu to select a sheet

Re: Creating a drop down menu to select a sheet

Re: Creating a drop down menu to select a sheet

Re: Creating a drop down menu to select a sheet

Re: Creating a drop down menu to select a sheet

Re: Creating a drop down menu to select a sheet

Re: Creating a drop down menu to select a sheet

Re: Creating a drop down menu to select a sheet

Re: Creating a drop down menu to select a sheet

Thread Information

Users Browsing this Thread

Similar Threads

Select from a drop down menu using VBA

Drop down menu, when selecting from a drop down menu to show a different sheet

select a sheet from a drop down menu and move the row in the selected sheet

Creating a Drop Down Menu

Creating a drop down menu

Creating Personal Menu Drop Down

select multiple items from drop-down menu

Bookmarks

Bookmarks

Posting Permissions