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?
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:
Step 3: Download a custom UI editor
In this section, we will show you the steps to create an interactive settings menu:
- Close Excel and Open the Workbook for editing
- Add a CustomUI.xml part to your Workbook
- Add custom buttons and icons for the menu
- Check the syntax and validate the code
- 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.
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.
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.
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.
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!