adding the freeze pane command to menu

[MDC]

Is it possible to add the Freeze Pane command to the menu
that appears when you right click in a cell?

thanks.

 

[JE McGimpsey]

One way:

Type ALT-F11 to enter the VBE. Choose View/Immediate Window. In the
Immediate Window, type or paste

Application.CommandBars("Cell").Controls.Add Id:=443

followed by Enter.

Note: At least in XL04, the control will toggle freezing panes, but the
caption won't automatically change to "Unfreeze Panes" as it does in the
Window menu.

 

[JulieD]

Hi Jim

is this the only way to add items to the shortcut menus?
if so, where do you get a list of the IDs from?

Cheers
JulieD

 

[JE McGimpsey]

using VBA is the only way I know.

I don't have a list, though I'm sure one exists somewhere. Instead I
entered this in the VBE's Immediate Window:

?Commandbars("Worksheet Menu Bar").Controls( _
"Window").Controls("Freeze Panes").Id
443

 

[icestationzbra]

hi,

i tried to use it, now i have 5 additional Freeze Panes items in th
context menu ...

now i would like to remove them. how do i do it?

thanks,

mac

 

[David McRitchie]

Use the following 5 times in the Intermediate Window to
remove the 5 additions that you added:
Application.CommandBars("Cell").Controls("freeze panes").Delete


Summary and a couple of additional items:
To add / To remove to Context Menu via the Intermediate Window
Application.CommandBars("Cell").Controls.Add Id:=443
Application.CommandBars("Cell").Controls("freeze panes").Delete

To Find id
?Commandbars("Worksheet Menu Bar").Controls( _
"Window").Controls("Freeze Panes").Id

In Excel 2000 (on a PC), the Window Menu does change when the change
is is made via the context (RightClick) menu addition. JE has a Mac
which might be a difference.

Another way of seeing the id number is via Jim Rech's FaceID
addin, which lists all of the button ids in Excel,
http://www.bmsltd.ie/MVP/MVPPage.asp
But the previous method of finding the ID via the Intermediate
Window is a lot more practical, and there are lots of duplications
of the buttons so it would be hard to tell if you chose the correct one.

I've included this information in an addition to by
Right Click Menus (Context Menus) in Excel
http://www.mvps.org/dmcritchie/excel/rightclick.htm#CtrlG

 

[JE McGimpsey]

No - the Window menu's "Freeze Panes" item changes to "Unfreeze Panes",
but the Cell Menu's doesn't.

 

[JE McGimpsey]

You could use this macro to toggle adding/removing the command (it will
also remove all the duplicates before adding one back):

Public Sub ToggleAddingFreezePanesToCellMenu()
Const nID As Long = 443
Dim ct As CommandBarControl
With Application.CommandBars("Cell")
Set ct = .FindControl(Id:=nID)
If Not ct Is Nothing Then
ct.Delete
Else
.Controls.Add Id:=nID
End If
End With
End Sub