Interactive Settings Menu for Dashboards

If you are working on an excel dashboard, you need to create great-looking navigation. Everyone loves great UX! I will show you how to build an interactive settings menu for your spreadsheet in today’s guide.

Take a close look at the picture below! Take a close look at the picture below! This simple menu enables you to interact with your Worksheets using clickable buttons and icons. Look’s great, isn’t it?

interactive settings menu ribbon

Build an interactive settings menu

Step 1: Plan the menu structure

  • Check how many worksheets want to show
  • Design the buttons for all Worksheet functions

In the example, I’ll show you how to easily build an interactive setting menu to manage your Worksheets. OK, let us start the work! Based on the design rules, I’ll use three sheets: ‘Dashboard’, ‘Data’ and ‘Calculations’.

Step 2: Pick the icons for the interactive settings menu

I’ll choose the proper icons. We have three Worksheets, so we need to select three icons to control the Workbook. Because I want to add two extra functions (link to my About page and YouTube channel page), I’ll prepare two additional icons.

It’s a great solution to control your spreadsheet from the ribbon.

Here are my icons:

interactive settings menu icons

Step 3: Download a custom UI editor

In this section, we will show you the steps to create an interactive settings menu:

  1. Close Excel and Open the Workbook for editing
  2. Add a CustomUI.xml part to your Workbook
  3. Add custom buttons and icons for the menu
  4. Check the syntax and validate the code
  5. Add callbacks (small macros)

Let’s see the details:

At first, we need a small app to edit the XML header. Download the latest version of Office Ribbon X Editor from Github. Move the archive to your Documents folder and extract the zip file content.

First, close Microsoft Excel, start the Editor, and open the Excel file that you want to edit:

Click on the Insert menu and choose the Office 2010+ Custom UI Part from the menu.

open the tool and insert a custom UI part

Step 4: Edit the XML header

Copy the code and paste it into the CustomUI14.xml section.

Don’t worry; you can edit the menu if you download the example.

office ribbonx editor code

Just a few words about the variables:

  • tab: The new Tab will appear on the ribbon, in this case; HR template
  • button Id: all buttons have a unique id from btn1 to btn5
  • label: you can define the labels freely
  • onAction: call the macro from Excel
  • image: the name of the inserted icon

Step 5: Insert icons

The next step is to insert your custom icons.

The best size is 32×32 pixels, and we recommend you using transparent ‘PNG’ format.

insert icons

Click on the Insert tab and choose icons. Locate the folder which contains your png files and click OK.

Step 6: Create callbacks for Worksheet selections

So, you want to manage your worksheets from the ribbon. To create a callback, open the Visual Basic Editor window by clicking Alt + F11.

Insert a new macro.

This snippet will activate the selected Worksheet.

onaction ribbon control

Finally, add a small Sub to call the macro from the ribbon. That’s all!

Important: Repeat these steps for all Worksheets and save the Workbook as xlsm or xlsb format.

Thanks for being with us today!

Additional resources: