Right Click menu changed due to macro - how to modify?

[xluser2007]

Hi All,

I was experimenting with creating toolbars and menubars, and came across the attached nifty spreadsheet (I can't remember the source though).

By Changing the ID values in the range C:15 to C:22, you can customise your right click menu in Excel.

Only issue, is how do I change back i.e.e get the ID values for the default right click menu in Excel?

[xluser2007]

Here's the attachment.

[xluser2007]

Hi All,

Just found this link:

http://support.microsoft.com/kb/213552

This lists the relevant command bar number IDs to use for retrieving my right-click functionality.

I get that you just update the cells C:15 to C:22, but how do you know what value to put for B15:B22?
Why is B18 equal to 13 for example and why are the other values in B15 to B:22 equal to 1?

any help would be great, in fact this macro is pretty cool once someone can explain the above.

[Bob Phillips]

[vba]

Application.CommandBars("cell").Reset
[/vba]

A type of 1 means that you are using a standard button popup, 21 is a dropdown.

[Bob Phillips]

BTW, I find this much simpler, much easier to understand, and much easier to extend http://www.j-walk.com/ss/excel/tips/tip53.htm

[xluser2007]

Hi Bob,

Thanks for your help with this one.

I'm now trying to use the original method to generate the right default right-click menu (I'll work on the Walkenbach method as you suggested once I get this macro method down-pat and find all the relevant FaceID's numbers ).

For the right click macro here are the the relevant codes from the microsoft website (Do you agree that the Cell commandbar references are the the best ones to use?).

Cell Cu&t 21
Cell &Copy 19
Cell &Paste 22
Cell Paste &Special 755
Cell &Insert 3181
Cell &Delete 292
Cell Clear Co&ntents 3125
Cell Insert Co&mment 2031
Cell Delete Co&mment 1592
Cell Sh&ow Comment 1593
Cell &Format Cells 855
Cell Pic&k From List 1966
Cell &Hyperlink 1576
Cell &Hyperlink 30094

I tried set up using the macro with the screenshot, but it keeps falling over (I think firstly at Insert and the at the second Hyperlink). Could please help me fix it to get the default right-click menu?

If it's easier with the Walkenbach method could you please show me how to do a right-click with his way, given the above codes.

Sometimes over-experimenting with VBA leads to this kind of mess, but I guess it's the best way to learn.

Any help appreciated.

[xluser2007]

Screenshot of my attempt attached here:

[Bob Phillips]

A type of 20 is a graphical combo box, I doubt very much that you want one of those. Whgere did you get that idea from?

[xluser2007]

A type of 20 is a graphical combo box, I doubt very much that you want one of those. Whgere did you get that idea from?

Hi Bob, I tried 21, and then just 1,2, 3 etc and couldn;t get to work the screenshot was just a frustrated (random) attempt.

Why doesn't 1 work for Insert?

Also how do I add those divider lines in between the menu bars using this method.

The macro is as follows:

[vba]Global NewMenu
Global myMenuBar

Function GetDataElement(s As String, e As Integer)
'=======================
' Uses the Define Data sheet
' and gets and sets the
' string elements
'========================
On Error Resume Next
Set Ws = Sheets("DefineData")
GetDataElement = "nothing"
GetDataElement = _
Ws.Cells(Application.WorksheetFunction.Match(s, Ws.Range("A:A"), 0), e)
On Error GoTo 0
End Function
Sub AboutME()
AboutForm.Show
End Sub
Sub CreateNewBar()
'=======================
' Uses the Tag function and
' "tjbc" as a marker for
' new items. Refers to the
' DefineData sheet for data
' elements
'========================
Set Ws = Sheets("DefineData")
KillOldBars
Dim c
Set myMenuBar = CommandBars.ActiveMenuBar
Set NewMenu = myMenuBar.Controls.Add(Type:=msoControlPopup, Temporary:=True)
If GetDataElement("MENUADDONE", 2) <> "nothing" Then NewMenu.Caption = _
GetDataElement("MENUADDONE", 2) Else End
NewMenu.Tag = "tjbc"
For X = 1 To 5
Set c = NewMenu.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)
a = GetDataElement("ctrl" & X, 1)
If a = "nothing" Then
c.Delete
Exit For
End If
p = setDataForMenus(c, "ctrl" & X)
Next
End Sub
Private Function setDataForMenus(cntrl, cNew As String)
'================
' Pulls the string
' data for menu item
' set up from the
' DefineData Sheet
'================
With cntrl
.Caption = GetDataElement(cNew, 2)
.Tag = GetDataElement(cNew, 3)
.TooltipText = GetDataElement(cNew, 4)
.Style = msoButtonCaption
.OnAction = GetDataElement(cNew, 5)
End With
End Function

Sub KillOldBars()
'===================
' Uses the Tag function and
' "tjbc" as a marker for
' what to delete"
'====================
On Error Resume Next
Set myMenuBar = CommandBars.ActiveMenuBar
For Each m In myMenuBar.Controls
If m.Tag = "tjbc" Then m.Delete
Next
On Error GoTo 0
End Sub

Sub AddToRightclick()
s = "Cell"
For Each c In Application.CommandBars(s).Controls
c.Delete
Next
For X = 1 To 50
a = GetDataElement("RC" & X, 2)
Debug.Print a

b = GetDataElement("RC" & X, 3)
Debug.Print b

If a = "nothing" Or a = "" Then Exit For
Set t = Application.CommandBars(s).Controls.Add(a, b)
Next
'msoControlButton, msoControlEdit, msoControlDropdown, msoControlComboBox, or msoControlPopup
End Sub
[/vba]
How do you do this easily using the Walkenbach method btw, do you have keep finding all the FaceID's for the built in command bar buttons?

[Bob Phillips]

Odd isn't it. I tried this

[vba]

With Application.CommandBars("Cell")

.Controls.Add Type:=1, ID:=3181
End With
[/vba]

and it fails too.

No idea about the dividers, the code doesn't look to hot to me, not a good advert for TJB Consulting.

As I said, I find JW's easy to modify, and you would need to modify the code to use built-in functions.

[xluser2007]

Odd isn't it. I tried this

[vba]

With Application.CommandBars("Cell")

.Controls.Add Type:=1, ID:=3181
End With
[/vba]
and it fails too.

No idea about the dividers, the code doesn't look to hot to me, not a good advert for TJB Consulting.

As I said, I find JW's easy to modify, and you would need to modify the code to use built-in functions.

That is strange indeed.

With Walkenbach's, the dividers are easily done, but how do you get the right click happening, it seems to be creating workbook menu bars only.

This is the Walkenbach Code:

[VBA] Option Explicit

Sub CreateMenu()
' This sub should be executed when the workbook is opened.
' NOTE: There is no error handling in this subroutine

Dim MenuSheet As Worksheet
Dim MenuObject As CommandBarPopup

Dim MenuItem As Object
Dim SubMenuItem As CommandBarButton
Dim Row As Integer
Dim MenuLevel, NextLevel, PositionOrMacro, Caption, Divider, FaceId

''''''''''''''''''''''''''''''''''''''''''''''''''''
' Location for menu data
Set MenuSheet = ThisWorkbook.Sheets("MenuSheet")
''''''''''''''''''''''''''''''''''''''''''''''''''''

' Make sure the menus aren't duplicated
Call DeleteMenu

' Initialize the row counter
Row = 2

' Add the menus, menu items and submenu items using
' data stored on MenuSheet

Do Until IsEmpty(MenuSheet.Cells(Row, 1))
With MenuSheet
MenuLevel = .Cells(Row, 1)
Caption = .Cells(Row, 2)
PositionOrMacro = .Cells(Row, 3)
Divider = .Cells(Row, 4)
FaceId = .Cells(Row, 5)
NextLevel = .Cells(Row + 1, 1)
End With

Select Case MenuLevel
Case 1 ' A Menu
' Add the top-level menu to the Worksheet CommandBar
Set MenuObject = Application.CommandBars(1). _
Controls.Add(Type:=msoControlPopup, _
Before:=PositionOrMacro, _
Temporary:=True)
MenuObject.Caption = Caption

Case 2 ' A Menu Item
If NextLevel = 3 Then
Set MenuItem = MenuObject.Controls.Add(Type:=msoControlPopup)
Else
Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
MenuItem.OnAction = PositionOrMacro
End If
MenuItem.Caption = Caption
If FaceId <> "" Then MenuItem.FaceId = FaceId
If Divider Then MenuItem.BeginGroup = True

Case 3 ' A SubMenu Item
Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
SubMenuItem.Caption = Caption
SubMenuItem.OnAction = PositionOrMacro
If FaceId <> "" Then SubMenuItem.FaceId = FaceId
If Divider Then SubMenuItem.BeginGroup = True
End Select
Row = Row + 1
Loop
End Sub

Sub DeleteMenu()
' This sub should be executed when the workbook is closed
' Deletes the Menus
Dim MenuSheet As Worksheet
Dim Row As Integer
Dim Caption As String

On Error Resume Next
Set MenuSheet = ThisWorkbook.Sheets("MenuSheet")
Row = 2
Do Until IsEmpty(MenuSheet.Cells(Row, 1))
If MenuSheet.Cells(Row, 1) = 1 Then
Caption = MenuSheet.Cells(Row, 2)
Application.CommandBars(1).Controls(Caption).Delete
End If
Row = Row + 1
Loop
On Error GoTo 0
End Sub

Sub DummyMacro()
MsgBox "This is a do-nothing macro."
End Sub[/VBA]

I'm confused though, to make it right click, is it just a case of changing the msoControlPopup to something else?

What is the best way to make a right click option?

[Bob Phillips]

Here you are mate (I don't know your name to properly address you), here is an updated version of my enhanced version of John's MenuMarker which should do what you want.

[xluser2007]

Bob,

Mate, you really go out of your way to help us newbs out don't you?

I appreciate it very very much . I seriously learn more about VBA by just sifting through other VBAXer's, often simple, but more often than not, brilliant queries and reading Guru responses from yourself, malcolm, mike, lucas, Aussiebear etc. I guess this is just a long-winded way of thanking you for your time.

As for the macro, just so I understand it better, what are the key toggles you are using when adding buttons? For example, if I want to create the usual Insert..., or Delete..., what do I need to change in the spreadsheet in relation to the pre-defined command button ID's from http://support.microsoft.com/kb/213552?

regards,

Sam