Excel

Create custom right-click menu items

Ease of Use

Easy

Version tested with

2000, 2003 

Submitted by:

Killian

Description:

This example demonstrates how to create and use menu items on the user's right-click menu. It allows the user to select a rectangle size from a right-click list to insert at a cell location. 

Discussion:

Context (right-click) menus are extemely useful for frequent worksheet tasks, allowing users to access your code quickly with minimum mouse movement and clicks. 

Code:

instructions for use

			

'### code for the ThisWorkbook code sheet ### Option Explicit Private Sub Workbook_BeforeClose(Cancel As Boolean) 'remove our custom menu before we leave Run ("DeleteCustomMenu") End Sub Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) Run ("DeleteCustomMenu") 'remove possible duplicates Run ("BuildCustomMenu") 'build new menu End Sub '### code for the ThisWorkbook code sheet - END '### code for a new module ### Option Explicit Private Sub BuildCustomMenu() Dim ctrl As CommandBarControl Dim btn As CommandBarControl Dim i As Integer 'add a 'popup' control to the cell commandbar (menu) Set ctrl = Application.CommandBars("Cell").Controls.Add _ (Type:=msoControlPopup, Before:=1) ctrl.Caption = "Insert Shape..." 'add the submenus For i = 50 To 250 Step 50 'add a few menu items Set btn = ctrl.Controls.Add btn.Caption = i & " x " & (i / 2) 'give them a name btn.Tag = i 'we'll use the tag property to hold a value btn.OnAction = "InsertShape" 'the routine called by the control Next End Sub Private Sub DeleteCustomMenu() Dim ctrl As CommandBarControl 'go thru all the cell commandbar controls and delete our menu item For Each ctrl In Application.CommandBars("Cell").Controls If ctrl.Caption = "Insert Shape..." Then ctrl.Delete Next End Sub Private Sub InsertShape() Dim t As Long Dim shp As Shape 'get the tag property of the clicked control t = CLng(Application.CommandBars.ActionControl.Tag) 'use the value of t and the active cell as size and position parameters 'for adding a rectangle to the worksheet Set shp = ActiveSheet.Shapes.AddShape _ (msoShapeRectangle, ActiveCell.Left, ActiveCell.Top, t, t / 2) 'do something with our shape Randomize 'make it a random color from the workbook shp.Fill.ForeColor.SchemeColor = Int((56 - 1 + 1) * Rnd + 1) End Sub '### code for a new module - END

How to use:

  1. From Excel, open the Visual Basic Editor (VBE) by pressing Alt+F11
  2. Select the 'ThisWorkbook' code sheet and paste in the appropriate code
  3. Add a new module by clicking Insert/Module
  4. Paste the appropriate code into the new module
  5. Press Alt + Q to close the VBE window
  6. Save the workbook
 

Test the code:

  1. Right-click a cell on any worksheet
  2. Your custom menu will appear at the top of the right-click menu
  3. Select an item from the sub-menu
  4. A rectangle of the selected size and of a random color will be added at the clicked cell's position
 

Sample File:

Context Menu.zip 11.46KB 

Approved by mdmackillop


This entry has been viewed 402 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express