PDA

View Full Version : Solved: VBA RIGHT CLICK MENU



cyborg18
03-08-2011, 11:36 PM
hi,

I want to create a custom right click menu in excel (2003), but i don't know how to create more then one level. When i use this code, i get two captions (in level 1) :

1 LEVEL
FIND
COPY

THE CODE FOR 1 LEVEL
Set Control_1 = Application.CommandBars("Cell").Controls.Add
With Control_1
.Caption = "FIND"
.OnAction = "main.FIND"
.BeginGroup = True
End With
'----------------------------------------
Dim Control_2 As CommandBarControl
Set Control_2 = Application.CommandBars("Cell").Controls.Add
With Control_2
.Caption = "COPY"
.OnAction = "main.COPY"
.BeginGroup = True
End With


What is the the code for more levels?

For instance :

2 LEVELS
FIND #- FIND SHEET
#####- FIND COLUMN
#####- FIND ROW
COPY #- COPY CELL
#####- COPY RANGE

Thanks :-)

Bob Phillips
03-09-2011, 01:06 AM
Dim Control_1 As CommandBarControl
Dim Control_2 As CommandBarControl

Set Control_1 = Application.CommandBars("Cell").Controls.Add(Type:=msoControlPopup)
With Control_1

.BeginGroup = True
.Caption = "FIND"

With .Controls.Add(Type:=msoControlButton)

.Caption = "Find Sheet"
.OnAction = "main.FINDSheet"
End With

With .Controls.Add(Type:=msoControlButton)

.Caption = "Find Column"
.OnAction = "main.FINDColumn"
End With

With .Controls.Add(Type:=msoControlButton)

.Caption = "Find Row"
.OnAction = "main.FINDRow"
End With
End With


Set Control_2 = Application.CommandBars("Cell").Controls.Add
With Control_2

.BeginGroup = True
.Caption = "COPY"

With .Controls.Add(Type:=msoControlButton)

.Caption = "Copy Cell"
.OnAction = "main.COPYCell"
End With

With .Controls.Add(Type:=msoControlButton)

.Caption = "CopyRange"
.OnAction = "main.COPYRange"
End With
End With

cyborg18
03-12-2011, 12:38 AM
Thanks :-) :-)

CaptRon
03-17-2011, 08:30 PM
You can use this to reset the built-in menus to their original condition.



Sub ResetBuiltInCommandBars()
'You can also run this with the Workbook_BeforeClose event

For Each bar In Application.CommandBars
If bar.BuiltIn Then bar.Reset
Next
End Sub

Ron