Hyperlink Combo Boxes in Excel with VBA

There is a lot of forums about how to do this and the best I've seen is using named ranges and data validation lists. I think there is a better way.

Problem: I want to make a drop down menu with hyperlinks to other worksheets

Solution: Write a simple VBA macro (I promise, it's really easy!)

Step 1: Make sure all your worksheets are cleanly titled. If you have several distinct groups of worksheets, then the first word of the title should be the same for the group. In this example, I have several worksheets grouped in "Tournament" so my titles are like "Tournament Setup", "Tournament Registration" etc.

Step 2: Create a worksheet to house all the menu options. This is not necessary if you can efficiently generate list items from the original worksheets. For example purposes, we will keep all our entries here. Also, if you want the code to work as shown below follow these steps exactly and name this sheet NavMenu.

Step 3: Designate a column for the menu items. If you want the code provided to work without modifying the cell locations, then the sheet should be built like

Cells B11:B17 are the menu items. For now, just type in the menu items in this range. The menu items should NOT include the group label. This will be added automatically in the code. Cell B8 is the group label. You can go ahead and add this too.

Step 4: Create a form control combobox (not activeX). Right click and click format control. In Input Range select the range containing the menu items. Here it is NavMenu!$B$11:$B$18. I include Cell B18 so I have a blank item to rest the menu to. You'll see what i mean below.

In the cell link box enter NavMenu!$D$9. It is important to specify the NavMenu worksheet since you'll use this on many pages and it must only modify the cells in the NavMenu worksheet, not the current sheet. Now you should have a combo box that contains all the menu items and posts the index in cell D9.

Step 5: Cell D10 is the name of the menu item. To do this we use the offset command. In Cell D10 enter =OFFSET(B8,D9+2,0,,) . This takes B8 as the reference cell, counts value(D9)+2 rows down, and displays whatever is in that cell. In our case, it's the menu item name.

Step 6: In Cell D8 enter =COUNTA(B11:B18). This is not shown in the picture above because i just added it. It counts the total number of non blank cells in the range. I include B18 because when I insert new item, the range will automatically adjust. New items are inserted in the menu by insert (shift cells down) at B18.

Step 7: Open up the Developer menu (search google if it's not already displayed in your ribbon) and visual basic. Create a new module for the menu and paste:

Sub TournamentMenu()

Dim PageName As String, GroupName As String, SheetName As String

Dim ItemNum As Integer

ItemNum = ['NavMenu'!D8] 'number of menu items

PageName = ['NavMenu'!D10] 'page name

GroupName = ['NavMenu'!B8] 'group name

If PageName = "0" Then 'condition to exit if blank

Exit Sub

End If

SheetName = GroupName & " " & PageName 'join the groupname and pagename

Sheets(SheetName).Select 'navigate to the sheet

['NavMenu'!D9] = ItemNum + 1 'reset the menu to the blank entry

End Sub

Step 8: Right click on the combobox and click assign macro. Select TournamentMenu and you're done!

Repeat for different menus. Modify the cell locations for ItemNum PageName and GroupName for each menu.