 Back to jkp-ads.com |
Ron de Bruin
|
 |
|
Back to jkp-ads.com |
Ron de Bruin
|
|
Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.
Luckily, Ron was kind enough to allow me to publish all of his Excel content here.
Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.
Kind regards
Jan Karel Pieterse
Making a menu that is working in every Excel version is not so easy
anymore because in Excel 2007 and up Microsoft replaced the Worksheet Menu
Bar with the Ribbon. One thing that is working in almost every Excel version
is to add menu items to a Context Menu with VBA code, for more information
visit:
Customizing Context Menus with VBA in every
Excel version
Another way that will work in every Excel version
is to create a PopUp menu like I show you on this page.
Note: in 2013 and 2016 there can be a problem with some code (selection sheets for example), after running the macro you can no longer close the workbook by going up to the top right corner and clicking the "X". In addition you cannot scroll down the sheet using the wheel on the mouse (although you can by dragging the scroll bar down on the right hand side.) The fix is to add the problem code in another macro and call the macro like this Application.OnTime Now, "MacroName"
Note: You can find this info also in my MSDN article:
http://msdn.microsoft.com/en-us/library/gg987030.aspx
Overview of PopUp menus in Excel
Making a PopUp menu is almost
the same as making a Context Menu. The big differents is that Context Menus
are only visible when you right click for example on a cell/range or a
specific object and Popup menus are visible when you want it. To display a
PopUp menu that you have created with VBA code you must use a code line like
this to let it popup Application.CommandBars(Popup
name).ShowPopup
Let's start
1:
Open a new workbook
2: Save the file with a nice name,
save as xls or xlsm(Macro enabled workbook in Excel 2007-2013)
3:
Press Alt-F11 to open the VBA editor
4: Use
Insert>Module in the menubar to create a Module to store the code
5: paste the code below in the module (note it is possible
that Option Explicit is already in the module)
Option Explicit
Public Const Mname As String = "MyPopUpMenu"
Sub DeletePopUpMenu()
'Delete PopUp menu if it exist
On Error Resume Next
Application.CommandBars(Mname).Delete
On Error GoTo 0
End Sub
Sub CreateDisplayPopUpMenu()
'Delete PopUp menu if it exist
Call DeletePopUpMenu
'Create the PopUpmenu
Call Custom_PopUpMenu_1
'Show the PopUp menu
On Error Resume Next
Application.CommandBars(Mname).ShowPopup
On Error GoTo 0
End Sub
Sub Custom_PopUpMenu_1()
Dim MenuItem As CommandBarPopup
'Add PopUp menu
With Application.CommandBars.Add(Name:=Mname, Position:=msoBarPopup, _
MenuBar:=False, Temporary:=True)
'First add two buttons
With .Controls.Add(Type:=msoControlButton)
.Caption = "Button 1"
.FaceId = 71
.OnAction = "'" & ThisWorkbook.Name & "'!" & "TestMacro"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "Button 2"
.FaceId = 72
.OnAction = "'" & ThisWorkbook.Name & "'!" & "TestMacro"
End With
'Second Add menu with two buttons
Set MenuItem = .Controls.Add(Type:=msoControlPopup)
With MenuItem
.Caption = "My Special Menu"
With .Controls.Add(Type:=msoControlButton)
.Caption = "Button 1 in menu"
.FaceId = 71
.OnAction = "'" & ThisWorkbook.Name & "'!" & "TestMacro"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "Button 2 in menu"
.FaceId = 72
.OnAction = "'" & ThisWorkbook.Name & "'!" & "TestMacro"
End With
End With
'Third add one button
With .Controls.Add(Type:=msoControlButton)
.Caption = "Button 3"
.FaceId = 73
.OnAction = "'" & ThisWorkbook.Name & "'!" & "TestMacro"
End With
End With
End Sub
Sub TestMacro()
MsgBox "Hi There, greetings from the Netherlands"
End Sub
6: Alt q to close the VBA editor
7:
Save the file
Now we have code in the file that create a PopUp menu
with a few buttons and a menu with two buttons
but how do we display
this PopUp menu when we want it ?
Note: for testing
every button option call the same macro.
1: Alt F8 to open the macro dialog
2:
Select the CreateDisplayPopUpMenu macro
3:
Click on the Options button in the dialog
4: Insert the
letter m for example and press OK
5:Close
this dialog and try the shortcut Ctrl m
6:
If correct you see the menu
7: Save the file
Note: If
another workbook that is opened after this workbook is using the same
shortcut this workbooks shortcut will be used, the last workbook that you
open will win the game. This shortcut will also work if another workbook is
active.
When we close the workbook we not delete the menu now. This
is not a big deal because we create the
menu with the last argument
Temporary to True so it will automaticly deleted when you close Excel.
but it is good practice to delete it in the Workbook_Deactivate event of the
workbook.
1: Alt F11 to open the VBA Editor
2: Go to the ThisWorkbook module
3:
Paste the code below in this module
Private Sub Workbook_Deactivate()
Call DeletePopUpMenu
End Sub
4: Alt q to close the VBA editor
5:
Save the file
Note: The cell menu is the context menu that you see
when you right click on a cell or selection Before we go try this example we
save the file with a different name so we leave the first example untouched.
1: Alt F11 to open the VBA Editor
2:
Go to the module with the code
3: Paste the code below
in the module below the other code
Sub AddToCellMenu()
Dim ContextMenu As CommandBar
'Delete the control first to avoid duplicates
Call DeleteFromCellMenu
'Set ContextMenu to the Cell menu
Set ContextMenu = Application.CommandBars("Cell")
'Add one custom button to the Cell menu
With ContextMenu.Controls.Add(Type:=msoControlButton, before:=1)
.OnAction = "'" & ThisWorkbook.Name & "'!" & "CreateDisplayPopUpMenu"
.FaceId = 59
.Caption = "My PopUp menu"
.Tag = "My_Cell_Control_Tag"
End With
End Sub
Sub DeleteFromCellMenu()
Dim ContextMenu As CommandBar
Dim ctrl As CommandBarControl
'Set ContextMenu to the Cell menu
Set ContextMenu = Application.CommandBars("Cell")
'Delete custom controls with the Tag : My_Cell_Control_Tag
For Each ctrl In ContextMenu.Controls
If ctrl.Tag = "My_Cell_Control_Tag" Then
ctrl.Delete
End If
Next ctrl
End Sub
4: Go to the ThisWorkbook module
5:
Paste the code below in this module (replace the Deactivate event of the
example above with the one below)
Private Sub Workbook_Activate()
Call AddToCellMenu
End Sub
Private Sub Workbook_Deactivate()
Call DeleteFromCellMenu
Call DeletePopUpMenu
End Sub
6: Alt q to go to Excel
7: Save the
file and close and reopen
If correct you can call your PopUp now with
the menu option on top of the Cell menu.
Screenshot below is from the
Excel 2010 cell menu
What if you want to display a different menu for each sheet or only
display the menu on a few sheets ?
Open the workbook from the example
above and add the code below to this workbook.
1: Alt F11 to open the
VBA Editor
2: Go to the module with the code
3: Paste the macro below
in the module below the other code
Sub Custom_PopUpMenu_2()
'Add PopUp menu with three buttons
With Application.CommandBars.Add(Name:=Mname, Position:=msoBarPopup, _
MenuBar:=False, Temporary:=True)
With .Controls.Add(Type:=msoControlButton)
.Caption = "Button 1"
.FaceId = 71
.OnAction = "'" & ThisWorkbook.Name & "'!" & "TestMacro"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "Button 2"
.FaceId = 72
.OnAction = "'" & ThisWorkbook.Name & "'!" & "TestMacro"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "Button 3"
.FaceId = 73
.OnAction = "'" & ThisWorkbook.Name & "'!" & "TestMacro"
End With
End With
End Sub
Then replace the macro named CreateDisplayPopUpMenu with the macro below
Sub CreateDisplayPopUpMenu()
'Delete PopUp menu if it exist
Call DeletePopUpMenu
'Create the correct menu based on the active worksheet
Select Case ActiveSheet.Name
Case "Sheet1": Call Custom_PopUpMenu_1
Case "Sheet2": Call Custom_PopUpMenu_2
Case Else: MsgBox "Sorry no PopUp Menu"
End Select
'Show the PopUp menu
On Error Resume Next
Application.CommandBars(Mname).ShowPopup
On Error GoTo 0
End Sub
When worksheet Sheet1 is active it will open the PopUp menu with 3
buttons and a Menu with 2 buttons.
And when worksheet Sheet2 is active it
will open a PopUp menu with 3 buttons. If any other worksheet is active no
PopUp menu will be displayed. This way you have full control of what your
users menu options are on each worksheet.
In the example above we have add a button to call the menu in the Cell
context menu but if you use
Excel 2007-2013 it is also possible to add a
button to the QAT.
Open the workbook from the first example and be
sure you save it as xlsm(macro enabled file)
Right click on the QAT
and choose Customize Quick Access Toolbar In the "Choose commands from"
dropdown choose "Macros" and in the Customize Quick Access Toolbar dropdown
choose "For YourWorkbookName.xlsm"
Select the CreateDisplayPopUpMenu
macro and press Add and then OK. You can use Modify to change the icon if
you want, I use the green button as you see. Then press OK and save the
file.
Note: You only have to do this one time
because the button is saved with the workbook.
If you send the file to
other users they can use your button on the QAT.
If you do it correct
it looks like this
Note: For another very easy PopUp menu example for Excel
2007-2013 visit the page below :
Menu for favorite macros in Quick Access
Toolbar in Excel 2007-2013
You see that I use FaceId 71 for a image with the number 1 and FaceId 59
for the smiley in the examples,
but how do i know which FaceId numbers I
can use?
Tip: You can download showfaceids.xls from my site to find all FaceID numbers.