Drop Down Navigation for Client

**adanglst** · 10-08-2010, 07:56 PM

I am trying to create a dashboard for a client that will have a drop down menu for navigating from worksheet to worksheet (and from a cell in a worksheet to another cell in a different worksheet).

excel version: 2010

My goal:

A free floating navigation drop down menu that navigates between worksheets and cells.

Ideally I would like to use "combo box" from Developer>insert>form/activex>combo box because it is free floating and does not size with the Excel column height or widths.

Ideally, I would also like this "combo box" to self populate with a set of links I define in a column somewhere a worksheet so that I can make complicated specific links on the fly

So in the column I would have "June revenue" linking to sheet 2
or "June revenue for Steve" linking to sheet 2 cell a4
or "Income statement" linking to sheet 3

**adanglst** · 10-11-2010, 01:32 PM

Somewhere on your sheet, create a range containing all the Sheet names in your workbook. For instance, "Sheet1" in cell A1, "Sheet2" in cell A2, etc.

Then from the main menu, select View, Toolbars, Control Toolbox. Use the Control Toolbox to put a Combo Box on your sheet. Right click on the ComboBox, select Properties, and set ListFillRange to the range containing your Sheet names.

Then double-click on the ComboBox to get to the Visual Basic Editor. In the Worksheet object, enter the following vba code:

Private Sub ComboBox1_Change()
ThisWorkbook.Sheets(Me.ComboBox1.Value).Select
End Sub

If you're not familiar with vba (Visual Basic for Applications), or with using the Control Toolbox, you must learn to do so in order to accomplish this task.

Drop Down Navigation for Client

LinkBack

Thread Tools

Rate This Thread

Display

Drop Down Navigation for Client

Re: Drop Down Navigation for Client

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions