Introduction
Introducing Burger Menu
Burger Menu - Burger Icon with Tooltip and Popup Menu
A Burger Menu is an Icon with 3 horizontal bars, that when clicked by the Mouse, renders a Menu of some description as a Drop-down list of selectable items. This Burger Menu is a Graphic Shape with a classic Burger-style Icon from the 365 Icons collection. It is linked via a Hyperlink technique that both allows a Hyperlink Tooltip and a Macro to be triggered. The Macro then runs a Popup Menu that renders a list of single items and a Sub Menu with nested Items to the Screen for the user to select
Update: I have performed a deep-dive excercise looking at the Hyperlink Tooltip and considering also the '=HYPERLINK()' Function used with the Rollover technique. You can read all about what I discovered (and what I didn't) Here · Hyperlink Tooltip (deep-dive)
Did you know that I have discovered many different ways to run macros following Rollovers? I have now discovered a perfect method for allowing a Proper Tooltip and a Rollover which is not used in this Project. If you want to read about these different techniques and purchase my latest Hyperlink Rollovers Project then you can do so Here · Hyperlink Rollovers for Excel


If you like this Project you may also enjoy
Hyperlink Rollovers · Pretty Buttons · Rollover Burger Buttons with Tooltips & Animations · Flat UI · Small Toggle Buttons · Menubar · Toggle Buttons · xlui · Burger Buttons · Popup Burger Buttons
Prerequisites
- Excel 2016 (32bit or 64bit)
- Basic to moderate Excel Skills and VBA skills to change the Code for different FaceId's in the Menu and to link your own Macros up to the Menu
- Windows PC · NOT a Mac!
- No support is provided for customization of this Software
Features
- A nifty Excel Workbook with a Double Bubble patterned background and helpful links/advice
- One default Burger Graphic Icon from the 365 Office Icons that can be changed to any Fill Colour. If you don't like the Burger Icon, change it to whatever you want
- VBA Code demonstrates how to add in a Sub Menu anywhere in the List
- VBA Code demonstrates how to pass in a String Parameter from the Menu into one of your own Subroutines
- An example of navigating to a Sheet and to an external Website link or URL
- Some default Face Id's to get you started and a link to an online Page where you can view lots of different Face Id's and their respective numbers
Installation
Open the BurgerMenu.xlsm File and test out the Burger Menu to see how it operates. Please read all of the info on the main Sheet and in this online documentation before you begin to edit and create your own Burger Menu or before turning to Support
Usage
BurgerMenu.xlsm
Test out the Burger Menu
Before you start editing and trying to create your own Burger Menu, take some time to look at the Example Burger Menu and see what it does. Hoever on it to see the Tooltip. Click on it to see the Drop-down list of Menu and Sub Menu items. Select an item to see a Message. On the right-hand side there is some useful information, together with a link to the nifty background (Double Bubble Pattern by: Tomislava Babić) and the online Help documentation
Editing the Burger Menu Shape (Selections and Formatting Panes)
The Burger Menu Shape is a Graphic object (called Menu - view in the Named Ranges box, top, LHS) which is an Icon from the Office 365 Icons set. In order to select it, you must first Unprotect the Sheet as Code protection is added to the Project by default to protect the interface (protects the Shapes from user interference, either on purpose or accidently) - do this from the Review Tab on the Ribbon. Right-click one the Burger Menu Icon. Now, use the Selection Pane on the Editing Group of the Home Tab of the Ribbon to make Selections easier and make viewing of the Names of any other objects easier. To format the Burger Menu Icon, you can hover near the Selection and use right-click Format and then change the formatting using the formatting Pane or use the options on the context Ribbon that will pop up for each object selection. I like to toggle on a Shape between the Format Pane and the Selection Pane. Here I have the Selection Pane visible and have selected the 'Menu' object:

The Fake Hyperlink used to create the Tooltip Hyperlink
The Fake Hyperlink used to create the Tooltip Hyperlink can be edited for your own needs. If you want to link to other Popup Menus you create then you can create the Tooltip again. Here is the Code, simply change anything wrapped within the '<>' tags and obviously, don't include the tags themselves!
' AddTooltip, used to add a Hyperlink and Tooltip to our Burger Menu Shape. ran once and then this does not need to be ever ran again unless you want to change the Tooltip Private Sub AddTooltip() ActiveSheet.Shapes("<Shape Name>").Parent.Hyperlinks.Add ActiveSheet.Shapes("<Shape Name>"), "", "<MacroName()>", ScreenTip:="Click for my super-cool menu" End Sub
How to change the FaceId's
FaceId is another name for the small Icons that accompany items on the Command Bar. To change the FaceId you need to add the number (or Index) of the FaceId that you want. You can view FaceId's Here
So, in the Code you need to change them as per below:
With Menu.Controls.Add(Type:=msoControlButton) .Caption = "Save Data" .FaceId = 3 ' < --- change the number here to the Face Id that you want. this one is a Save Icon
Using your own Icons or Images instead of the standard FaceId's
You are not limited to the standard FaceID's using the Popup Burger Menu. You can use any Icon or image but they need to be standard size 16 pixels by 16 pixels. First of all, download or design your Icon. Next insert it into the Excel Sheet. Then add the Code below for one of the Menu Control Buttons - Menu.Controls.Add(Type:=msoControlButton) to Copy & Paste the image on-the-fly:
.Style = msoButtonIconAndCaption 'icon and caption .Caption = "Own Icon" Sheet1.Shapes("Smiley").Copy ' copy the custom icon .PasteFace ' paste the custom icon .OnAction = "'" & ThisWorkbook.Name & "'!" & "SaveData"
How to change the Linked Macro
So you won't want to use my Macros I am sure! You can link yours up easily using the Code as per below to the '.OnAction' memeber of the Command Bar Control - simply change the Macro Name to your own, the one wrapped within the '<>' tags and obviously, don't include the tags themselves!:
.OnAction = "'" & ThisWorkbook.Name & "'!" & "<SaveData>" End With
How to create a New Sub Menu
To to create a New Sub Menu you just need to setup a new variable like this:
Dim SubMenu As CommandBarPopupThen at the point where you want to insert your Sub Menu place the following Code:
Set SubMenu = Menu.Controls.Add(Type:=msoControlPopup)Then add in your new Sub Menu item Code like this for example:
With SubMenu .Caption = "Create Chart" With .Controls.Add(Type:=msoControlButton) .Caption = "Create a Pie Chart" .FaceId = 429 .OnAction = "'" & ThisWorkbook.Name & "'!" & "CreatePie" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Create Donut Chart" .FaceId = 449 .OnAction = "'" & ThisWorkbook.Name & "'!" & "CreateDonut" End With End With
How to pass a Parameter back to your own separate Subroutines ran by the '.OnAction' member of the Command Bar Control
To pass a Parameter back to your own separate Subroutines ran by the '.OnAction' member of the Command Bar Control, you need to change the '.OnAction' member of the Command Bar Control, wrapping the String in double-quotes, like this to pass the word 'About' into the Information() Subroutine 'Foobar' String:
With Menu.Controls.Add(Type:=msoControlButton) .Caption = "Information" .FaceId = 487 .OnAction = "'Information ""About""'" End With ' Callback from the Popup Menu ' demonstrates how to use pass a variable from the Callback into a Subroutine Public Sub Information(ByVal Foobar As String) MsgBox "The Callback Parameter: '" & Foobar & "' was passed into the Information Subroutine" End Sub
Hyperlink Tooltip (deep-dive)
You can download the file that follows this article and includes all of the tests with full Code examples Here · Hyperlink Tooltip (deep-dive).xlsm
Did you know that I have discovered many different ways to run macros following Rollovers? This deep-dive is more around Shapes and the Hyperlink Tooltip. If you want to read about these techniques and purchase my latest Hyperlink Rollovers Project then you can do so Here · Hyperlink Rollovers for Excel
Let's look at the Hyperlink used to create a Tooltip for a Shape (I will refer to this as the Hyperlink Tooltip), that allows us to run a Macro at the same time and see if we can unlock some of its behaviour. I understand that a Function should be used instead of a Subroutine. I know that the Function should always use 'Set <Something> = Selection' to return an 'Object' or 'Range' that has some influences on its behaviour in terms of what can be ran using Code once it has been applied. The first thing I found out is that you can add a Hyperlink Tooltip to an Object like a Shape that can then run one or more Functions by using a comma-delimited list of Macro Names, to get 2 messages, "foo" and "bar", like this:' AddTooltip1, used to add a Hyperlink and Tooltip to our Shape ' - ran once and then this does not need to be ever ran again unless you want to change the Shape or Tooltip Private Sub AddTooltip1() ActiveSheet.Shapes("Icon1").Parent.Hyperlinks.Add ActiveSheet.Shapes("Icon1"), "", "TestA(), TestB()", ScreenTip:="This is a Hyperlink Tooltip deep-dive" End Sub Public Function TestA() As Variant Set TestA = Selection MsgBox "foo" End Function Public Function TestB() As Variant Set TestB = Selection MsgBox "bar" End FunctionThe next thing that I found was that you could use the first Function (or Macro) called to call another Function, to get a single Message "foobar", like this:
' AddTooltip2, used to add a Hyperlink and Tooltip to our Shape ' - ran once and then this does not need to be ever ran again unless you want to change the Shape or Tooltip Private Sub AddTooltip2() ActiveSheet.Shapes("Icon2").Parent.Hyperlinks.Add ActiveSheet.Shapes("Icon2"), "", "TestC()", ScreenTip:="This is a Hyperlink Tooltip deep-dive" End Sub Public Function TestC() As Object Set TestC = Application.Run("TestD") End Function Public Function TestD() As Variant Set TestD = Selection MsgBox "foobar" End FunctionRealising that the retun value could be an Object or a Range, I then explored changing the return value as a Range to enable us to select another Sheet via the Hyperlink Tooltip and found that this worked to Select Sheet2 for Cell "A1". You can use the '[]' square brackets as well to do this Range select (see the commented out Code below). I alo noticed that if you now add a MsgBox to the below routine (I added this and it is commented out below) that the Message is sent to the Screen first and then the Range is selected for Sheet2, which makes sense as the Range is the Object returned to the hyperlink:
' AddTooltip3, used to add a Hyperlink and Tooltip to our Shape ' - ran once and then this does not need to be ever ran again unless you want to change the Shape or Tooltip Private Sub AddTooltip3() ActiveSheet.Shapes("Icon3").Parent.Hyperlinks.Add ActiveSheet.Shapes("Icon3"), "", "TestE()", ScreenTip:="This is a Hyperlink Tooltip deep-dive" End Sub Public Function TestE() As Range Set TestE = Sheet2.Range("A1") 'Set TestE = Sheet2.[A15] 'MsgBox "foobar" End FunctionOne more test I wanted to check was whether I could pass a variable from inside the Hyperlink Tooltip. Well it turns out that you can, but it will only store a value at the moment that you set the Hyperlink, which makes sense I guess, meaning it doesn't query a variable again to find out its current value or property. I tried storing the Headings state of the ActiveWindow in a local variable to see if the variable changed whether the hyperlink Tooltip would pass the updated state but it only updates when the hyperlink is applied again. In example 6 I show a method to pass a variable into another Function via the first Function
Option Explicit Private HeadingsState As Boolean ' AddTooltip4, used to add a Hyperlink and Tooltip to our Shape ' - ran once and then this does not need to be ever ran again unless you want to change the Shape or Tooltip Private Sub AddTooltip4() ActiveSheet.Shapes("Icon4").Parent.Hyperlinks.Add ActiveSheet.Shapes("Icon4"), "", "TestF(" & HeadingsState & ")", ScreenTip:="This is a Hyperlink Tooltip deep-dive" End Sub ' Headings only changes if you run the AddTooltip4 Macro again, meaning it doesn't update in real time, which makes sense but is annoying ;) Public Function TestF(ByVal Headings As Boolean) As Range Set TestF = Selection MsgBox Headings End Function Public Sub SetHeadingsState() HeadingsState = True 'False End SubOkay, let's take a time out. So what have we learnt so far? Well we know we can run multiple Functions using the technique which may or may not be of any use at this point. We know that we can call another Function from the main Function using Application.Run - that's useful, we return an Object that is another Function (or Macro Name) that gets ran via the technique. We also know that we can select another Sheet by changing the return value to a Range and using the 'Set <FunctionName> = Sheet2.Range("A1")' or 'Set <FunctionName> = Sheet2.[A15]' - something that I have been unable to do in the past, hence the Timer technique whereby I fire a Timer thread to run another Subroutine and then kill it immediately, before continuing to run my Code. So can what we have learnt be put to use to negate the use of a Timer. Well that's my next Test. I will take one of my Pretty Buttons Toolbar Buttons and see if I can use these findings to make a workaround so that I don't use any API's, onwards...
Well it appears that trying a simple Task of Toggling the headings On/Off or adding a Sheet does not work using this technique. A workaround is to set a Cell which then fires the 'Worksheet_SelectionChange' event in a Worksheet Code Module, which to me is not a solution - I would rather use the Timer technique (SetTimer() can callback a regular function when fired) as you cannot re-fire the Code without deselecting or selecting another Cell in the Sheet. I demonstrate this in the fifth deep-dive test here:
' In a Worksheet Code Module Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$1" Then ActiveWindow.DisplayHeadings = Not ActiveWindow.DisplayHeadings End If End Sub ' in a standard Code Module ' AddTooltip5, used to add a Hyperlink and Tooltip to our Shape ' - ran once and then this does not need to be ever ran again unless you want to change the Shape or Tooltip Private Sub AddTooltip5() ActiveSheet.Shapes("Icon5").Parent.Hyperlinks.Add ActiveSheet.Shapes("Icon5"), "", "TestG()", ScreenTip:="This is a Hyperlink Tooltip deep-dive" End Sub ' By selecting a Cell the Worksheet_SelectionChange event handler fires in the Worksheet Code Module to toggle the headings Public Function TestG() As Range Set TestG = Sheet1.Range("A1") End FunctionI found that you can pass a variable form one Function into another Function like this (but this still doen't help us to execute the toggle headings code):
' AddTooltip6, used to add a Hyperlink and Tooltip to our Shape ' - ran once and then this does not need to be ever ran again unless you want to change the Shape or Tooltip Private Sub AddTooltip6() ActiveSheet.Shapes("Icon6").Parent.Hyperlinks.Add ActiveSheet.Shapes("Icon6"), "", "TestH()", ScreenTip:="This is a Hyperlink Tooltip deep-dive" End Sub ' you can pass variables into another Function Public Function TestH() As Range Set TestH = Application.Run("TestI", True) End Function Public Function TestI(ByVal Headings As Boolean) As Variant Set TestI = Selection MsgBox Headings End FunctionLet's now look at some other possibilities using Windows API. The first one I examine is the 'CallWindowProc' API passing the Procedure name into the API and we get our "foo" message but it still doesn't do much else, for example it fails on Sheet selection and toggling the headings:
Option Explicit Private Declare PtrSafe Function CallWindowProc Lib "user32" Alias "CallWindowProcA" (ByVal lpPrevWndFunc As LongPtr, ByVal hWnd As LongPtr, ByVal Msg As Long, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As LongPtr ' AddTooltip7, used to add a Hyperlink and Tooltip to our Shape ' - ran once and then this does not need to be ever ran again unless you want to change the Shape or Tooltip Private Sub AddTooltip7() ActiveSheet.Shapes("Icon7").Parent.Hyperlinks.Add ActiveSheet.Shapes("Icon7"), "", "TestJ()", ScreenTip:="This is a Hyperlink Tooltip deep-dive" End Sub ' you can use the CallWindowProc API to call another Procedure but it still doesn't let you select or toggle the headings ' however you can use a Sub and you don't have to Set the Selection Public Function TestJ() As Range Set TestJ = Selection CallWindowProc AddressOf TestK, Application.hWnd, 0&, 0&, 0& End Function Public Sub TestK() MsgBox "foo" Sheet2.Select ActiveWindow.DisplayHeadings = Not ActiveWindow.DisplayHeadings End SubOkay, now let's look at a techique I found ages ago when writing my Booking System for Excel, that does actually work. I use the 'SetTimer' API to fire a Timer which I then instantly Kill when my Procedure runs. This allows me to do anything following a Hyperlink Tooltip or a '=Hyperlink()' Function Cell Rollover, like this (now we can Select other Sheets, toggle the headings, have a MsgBox etc. and still return the Selection for the Range Object):
Option Explicit #If VBA7 And Win64 Then Private Declare PtrSafe Function SetTimer Lib "user32" (ByVal hWnd As LongPtr, ByVal nIDEvent As LongPtr, ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As LongPtr Private Declare PtrSafe Function KillTimer Lib "user32" (ByVal hWnd As LongPtr, ByVal nIDEvent As LongPtr) As Long Private TimerEvent As LongPtr #Else Private Declare Function SetTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long Private Declare Function KillTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long Private TimerEvent As Long #End If ' AddTooltip8, used to add a Hyperlink and Tooltip to our Shape ' - ran once and then this does not need to be ever ran again unless you want to change the Shape or Tooltip Private Sub AddTooltip8() ActiveSheet.Shapes("Icon8").Parent.Hyperlinks.Add ActiveSheet.Shapes("Icon8"), "", "TestL()", ScreenTip:="This is a Hyperlink Tooltip deep-dive" End Sub ' using my Timer technique you can do almost anything following a Hyperlink Tooltip or a '=Hyperlink()' Function Cell Rollover Public Function TestL() As Range Set TestL = Selection If TimerEvent <> 0 Then KillTimer 0&, TimerEvent TimerEvent = SetTimer(0&, 0&, 0, AddressOf TestM) End Function Public Sub TestM() KillTimer 0&, TimerEvent TimerEvent = 0 MsgBox "foo" Sheet2.Select ActiveWindow.DisplayHeadings = Not ActiveWindow.DisplayHeadings End SubSo what about precedent? I mean what if you have a Hyperlink Tooltip in a Cell and a Rollover in the same Cell, what happens? Well I tested this here and found that the Hyperlink Tooltip will take precedent over the '=HYPERLINK' Rolover Function. Here is the Code that I used and all of this is included in the File that you can download, which will make more sense when you see it:
' i added the Hyperlink Tooltip to Cell "B37" Private Sub AddRangeTooltip() ActiveSheet.Range("B37").Hyperlinks.Add ActiveSheet.Range("B37"), "", "TestN()", ScreenTip:="This is a Hyperlink Tooltip deep-dive" End Sub ' I added the Rollover Hyperlink to Cells "B33" and "B37", '=IFERROR(HYPERLINK(Rollover(D33)),"#D33")', '=IFERROR(HYPERLINK(Rollover(D37)),"#D37")' Public Function Rollover(ByVal RolloverCell As Range) As Range RolloverCell.Value2 = Rnd(999) End Function Public Function TestN() As Range Set TestN = Sheet2.Range("A1") End Function
Conclusion
In conclusion I have made a few discoveries, but nothing comes close to using a Timer to allow a Macro, Function, Procedure (call them what you will) to run and do anything following a Hyperlink Tooltip or a '=Hyperlink()' Function Cell Rollover. Therefore until some other methodolgy is found I will continue with my method. I hope you like this deep-dive and found some useful concepts around the Hyperlink Tooltip when applied to Shapes
You can download the file that follows this article and includes all of the tests with full Code examples Here · Hyperlink Tooltip (deep-dive).xlsm
Did you know that I have discovered many different ways to run macros following Rollovers? This deep-dive is more around Shapes and the Hyperlink Tooltip. If you want to read about these techniques and purchase my latest Hyperlink Rollovers Project then you can do so Here · Hyperlink Rollovers for Excel
Screen Shots
Burger Icon with Tooltip

Burger Icon Popup Menu

Editing the Buger Menu Graphic Icon

FAQ
Q. Can I use my own Icons instead of the standard FaceID's?
A. Yes, see the Usage Section for details on how to do this via VBA (Using your own Icons or Images instead of the standard FaceId's)
Videos
This is a video of the Burger Menu in action. In this demonstration, I demonstrate the Burger Menu and Sub Menu items that are linked to Macros. Music is 'Immortal - NEFFEX' · view the Burger Menu video on YouTube
Support
Support is 100% optional and I provide it for your convenience, so please be patient, polite and respectful
Support for my Software
- Responding to questions or problems regarding the Software and its features
- Fixing valid (replicated) bugs and reported issues for the VERSION I HAVE WRITTEN
Software support does not include
- Customization and installation services
- Support for third party software or ANY kind of development whatsoever
Before seeking support
- Make sure your question is a valid Software Issue and not a customization request
- Make sure you have read through the FAQ's, online documentation and any related video guides before asking support on how to accomplish a task
- Ensure that you access to the VBOM is allowed and that Macros can run in Excel
- Make sure to provide 'proof of purchase' and state the name / version of the Software that you are having issues with when requesting support by Email or via Facebook
How to get Support
Contact Mark Kubiszyn on the Email address provided when you purchased the Software, including the Order Number
Remember to be patient, if there has been an issue with your download, Mark will always respond within 48 hours and will Email you the File directly if neccessary. For other issues the response time may be considerably longer and I may choose to respond to specific questions only (as is my right), depending on what has been asked
Future Builds
Update: I have now found a method that no longer requires a Timer for firing for Selections following a hyperlink or a Rollover! I will be releasing this method with this Project for the next release
Bug Fixes
* There are currently no bugs identified
Changelog
You can find the version history in the Code Module for any Macro-enabled Software or read more information on the status of each release
- the latest Version including a description of any changes made is always shown first
12.10.2022 - (Version 1)
General release of Burger Menu