This page contains some VBA macros. If you need assistance to install or to use a macro please refer to my «Getting Started with Macros« or delve into it deeper on my Install page.
Rather than just referencing postings by Chip Pearson and the corresponding repairs in the same thread, I'll show what I'm actually using. Proper credits shown in the code. As you can see in the picture at the right four entries have been added: Copy Formula, Paste Formula, AutoSum, GetFormula.background: Shortcut menus, adding to, this particular modification allows for pasting formulas unchanged, Chip Pearson via Drew Paterson -- 2001-04-13 misc. Also see Toolbars page. key phrases: Application.CommandBars("Cell").Controls, !CopyFormula, !PasteFormula, ThisWorkBook, Add(temporary:=True)In my Auto_Open in my personal.xls (I call mine with a different name, you'll have to change code yourself) (#auto_open)Option Explicit Sub auto_open() '-- MsgBox "Version" & Application.Version & _ ' " <-- should be for version 9 h:\...\personal.xls" 'Workbooks.Open Filename:="H:\Excel2K\menumakr.xls" 'ActiveWindow.Visible = False If Application.TransitionNavigKeys Then MsgBox "Found Transition navigation keys, please turn off" 'Application.TransitionNavigKeys = False End If ' Double click on Clippy or his one of his friends. (Excel 2000) ' Unselect everything in sight, and especially "Use the Office Assistant" ' 'It has been removed from Office 2002 ' http://www.officeclippy.com Assistant.On = False 'Turn off the bloody Office Assistant doesn't work in Win98SE 'Application.Calculation = xlManual '---- do not include following code in your personal.xls Auto_Open '-- Run-time error '1004'. '-- Method 'Calculation' of object '_Application' failed 'If Application.Calculation <> -4105 Then ' '-4105 automatic, -4135 manual, 2 semi-automatic ' MsgBox Application.Calculation & "
" & _ ' ActiveWorkbook.FullName ' Application.Calculation = xlAutomatic ' MsgBox Application.Calculation 'End If 'The following line is used to turn off prompting in AutoSave Addin in XL2000 'Result if addin is turned off: Run-time error '9'; subscript out of range ' Workbooks("autosave.xla").Excel4IntlMacroSheets("Loc Table").Range("ud01b.Prompt").Value = False 'Jim Rech 1999-09-16, MS has an XL2000 fix for not saving AutoSave settings ' http://support.microsoft.com/support/kb/articles/Q231/1/17.ASP 'download: http://support.microsoft.com/download/support/mslfiles/ASUpdate.exe 'Chip Pearson via Drew Paterson -- 2001-04-13 misc '--http://groups.google.com/groups?threadm=uiqh89AxAHA.1620%40tkmsftngp05 Application.CommandBars("Cell").Reset 'was not in 2001-04-13 posting With Application.CommandBars("Cell").Controls With .Add .Caption = "C&opy Formula" 'Copy Formula / Paste Formula .OnAction = ThisWorkbook.Name & "!CopyFormula" 'in ChipPearson_RClick .Tag = "Formulas" 'cControlTag .BeginGroup = True End With With .Add .Caption = "P&aste Formula" .OnAction = ThisWorkbook.Name & "!PasteFormula" 'in ChipPearson_RClick .Tag = "Formulas2" 'cControlTag End With With .Add 'adding AutoSum for David Loh 2004-03-20 worksheet.functions .Caption = "A&utoSum" .OnAction = ThisWorkbook.Name & "!Simulate_autosum" 'see macro below .Tag = "Auto Sum via RtClick" '--need to include the button icon End With With .Add 'Adding Clear_Constants .Caption = "Clear Constants (leave formulas)" .OnAction = ThisWorkbook.Name & "!Clear_Constants" 'DMcRitchie_RClick .Tag = "Clear_Constants" End With With .Add 'Adding EndTotal .Caption = "Create SUB&TOTAL at end of column" .OnAction = ThisWorkbook.Name & "!endtotal_sub" 'DMcRitchie_RClick .Tag = "EndGetFormula" End With With .Add 'Adding GetFormula David McRitchie .Caption = "GetFormula" .OnAction = ThisWorkbook.Name & "!GetFormula_sub" 'DMcRitchie_RClick .Tag = "GetFormula" End With End With '-- there are separate commandbar controls for Row and Column Application.CommandBars("Row").Reset With Application.CommandBars("Row").Controls With .Add 'Adding Clear_Constants .Caption = "Clear Constants in Selected Rows (leave formulas)" .OnAction = ThisWorkbook.Name & "!Clear_Constants" 'DMcRitchie_RClick .Tag = "Clear_Constants in Rows" End With End With Application.CommandBars("Column").Reset With Application.CommandBars("Column").Controls With .Add 'Adding Clear_Constants .Caption = "Clear Constants in Selected Columns(leave formulas)" .OnAction = ThisWorkbook.Name & "!Clear_Constants" 'DMcRitchie_RClick .Tag = "Clear_Constants in Columns" End With End With 'instead of Auto_Open use Workbook_Open in the ThisWorkbook ' when you need to fire off a macro when opening with code. End Sub Sub Simulate_AutoSum() 'David Loh 2004-03-20 CommandBars.FindControl(, 226).Execute End Sub
In a separate module which have named "DMcRitchie_RClick" I have the following code:Option Explicit 'see comments below Sub GetFormula_sub() If ActiveCell.Column = 1 Then ActiveCell.Formula = "=personal.xls!GetFormula(" & _ ActiveCell.Offset(-1, 0).Address(0, 0) & ")" MsgBox "couldn't use cell to left so setting to use from cell above" Else ActiveCell.Formula = "=personal.xls!GetFormula(" & _ ActiveCell.Offset(0, -1).Address(0, 0) & ")" End If End Sub Sub EndTotal_sub() Dim end_data As Long 'dmcritchie RClick 2005-05-28 Range(ActiveCell.Address, _ Cells(Rows.Count, ActiveCell.Column).End(xlUp).Address).Select end_data = ActiveSheet.Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row ActiveSheet.Cells(end_data + 1, ActiveCell.Column).Formula = _ "=SUBTOTAL(9," & ActiveSheet.Cells(2, ActiveCell.Column).Address(1, 0) _ & ":OFFSET(" & ActiveSheet.Cells(end_data + 1, _ ActiveCell.Column).Address(0, 0) & ",-1,0))" End Sub Sub Clear_Constants() 'D.McRitchie RClick 2005-11-19 (also see insrtrow.htm) '-- provide for in rightclick cell, row, and column commandbars Dim rng As Range 'prevent expansion of a single cell selection Set rng = Intersect(Selection, Selection.SpecialCells(xlConstants)) If rng Is Nothing Then MsgBox "No constants in selection area(s) -- no removal" Else rng.ClearContents End If End Sub 'change to personal.xls if that is what you use 'see also ChipPearson_Module for Chip Pearson's RClick menus 'these are setup in module1 in an Auto_Open 'Documentation in: http://www.mvps.org/dmcritchie/excel/rightclick.htm
In a separate module which have named "ChipPearson_RClick" I have the following code:Option Explicit Sub CopyFormula() 'Chip Pearson, microsoft.public.excel.worksheet.functions, 2000/05/02 'http://groups.google.com/groups?hl=en&newwindow=1&th=4831aec5cbe19367&rnum=1 'http://groups.google.com/groups?as_umsgid=OWeRetUjBHA.2212@tkmsftngp05 Dim x As New DataObject x.SetText ActiveCell.Formula x.PutInClipboard End Sub Sub PasteFormula() On Error Resume Next Dim x As New DataObject x.GetFromClipboard ActiveCell.Formula = x.GetText End Sub
also see MS KB 159619 - XL97: Sample Macros for Customizing Menus and Submenus
also see Bob Phillips, programming, 2004-05-09
also see Gord Dibben 2002-03-26 to copy a range of formulas without change. (actual author unknown)
You can add to or remove items from the Right Click menu through the intermediate window. I don't really know why someone would actually choose to include Freeze panes but here is a solution offered by JE McGimpsey, 2004-08-31, if you want the context menu to toggle like in the window menu (freeze/unfreeze) you would need a macro (also supplied in thread). Alternately to unfreeze you can Rclick on A1.
To find the needed id, the following returns 443 from the Intermediate Window
?Commandbars("Worksheet Menu Bar").Controls( _
Another method of finding the id is to use Jim Rech's FaceIT addin and use the associated number, BtnFaces, displays the command bar button control faces built into Excel and their associated FaceID numbers. Developers can use the FaceIDs to add faces to their own command bar button controls. Since the same buttons may be used several time you can't really tell if you picked the right one.
There are separate commandbars for Cell, Row, and Column which can be seen in the coding for Auto_Open example above. Below are the Context menus for cell and for Row.
for cell selection for row selection
Problems (#problems)There is no right click menu (context menu missing). Try this command in the Intermediate Window (Ctrl+G) of the VBE (Alt+11)
Missing the right click context menu for sheet tabs (fix with Ctrl+G)
Related (#related)If not familiar with installation and use of macros, see Getting Started with Macros and User Defined Functions
- Additional items you might want to add to your context menu in Excel
- UnSelectActiveCell from selection area, and UnSelectActiveArea from selected areas, by Chip Pearson (2003-04-13, misc).
- Right-click Menus in Excel
- Right click Event macros in Excel. (on my EVENT Macros page)
- Customizing Your Right-click Menus [archived], David Ringstrom (msofficemag formerly OfficeVBA.com), Customization of both the "Cell" and "Ply" commandbar menus, (sample code).
- Setting up a right-click menu, replies by Bernie Deitrick and Paul B., examples
- Listing right-click menus see right-click menus on collections page.
- Right-Click, 213757 - XL2000: How to Turn off Shortcut Menus -- cutomization of "CELL" and "PLY" menus,
- Customize, 213209 - XL2000: Sample Macros that Customize and Control Shortcut Menus using the Commandbar object
- Bookmarklets for Internet Explorer, Netscape, Mozilla, Firefox regain control
- over unwanted webpage features like background images, bad CSS coloring schemes, and do neat things like sort HTML Tables (Mozilla) -- see below.
- Collections, also includes some additional Right Click Menu things. Also see Bar Hopper to extract information and hierarchy of user created menues.
- Excel shortcut keys
- Internet Explorer context menus, rather in depth examples of context (right-click) menus that can be found or added to Internet Explorer. also see the Related area on that page.
- Menus In Excel97 And 2000: manipulation both manually and in VBA, Chip Pearson.
- Menus, User Interface Nick Hodge, understanding Excel through it's menus.
- Toolbars, Custom Buttons and Menus
- QuickLaunch context menus (Windows), applies also to desktop shortcuts and Start Menu.
- Shortcuts, clicks, in Excel and other applications: Right-click: Excel (this page), IE; Firefox(Mozilla); Windows QuickLaunch, Desktop, Start menu; Mouse Operations: Excel, Firefox(Help); Short Cuts (keyboard shortcuts): Excel; IE, OE, Win2000; Toolbars: Excel; Drag/Fillhandle: Excel Fill Handle, Windows drag files; Accessibility Options: MouseKeys; Event Macros: Excel
Bookmarklets; There are a lot of links on this page. The following BookMarklets (Favelets) will distinguish between internal or external links. First click on "highlight links", and then on "int/ext links". Your browser's Reset button (F5) will reload the original unmodified web page.
Excel questions not directly concerning my web pages are best directed to newsgroups
such as news://msnews.microsoft.com/microsoft.public.excel.misc where Excel users all around the clock from at least 6 continents ask and answer Excel questions. Posting suggestions and netiquette. More information on newsgroups and searching newsgroups. Google Groups (Usenet) Advanced Search Excel newsgroups (or search any newsgroup).
This page was introduced on June 02, 2004.
[My Excel Pages -- home] [INDEX to my site and the off-site pages I reference]
[Site Search -- Excel] [Go Back] [Return to TOP]
Please send your comments concerning this web page to: David McRitchie send email comments
Copyright © 1997 - 2004, F. David McRitchie, All Rights Reserved