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 CommandBarPopup

Then 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 Function

The 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 Function

Realising 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 Function

One 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 Sub

Okay, 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 Function

I 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 Function

Let'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 Sub

Okay, 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 Sub

So 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

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