Popup Menu / Shortcut Menu

[Guest]

In Access, I can create popup menus that I can link to a form or controls on
a form. Whenever I right click in that control, a popup menu appears that
allows me to perform whatever functionality I code into it.

I've looked in the Excel 2003 toolbar, but did not see a way to create or
utilize this functionality. Is it available? If so, how do I implement it?

Dale

 

[Chip Pearson]

Yes, you can do something similar to that. The first procedure create a
command bar named "MyBar". The next three procs are placeholders for real
code.

Sub CreateMenuItems()

Dim CmdBar As Office.CommandBar
Dim Ctrl As Office.CommandBarControl

On Error Resume Next
Application.CommandBars("MyBar").Delete
On Error GoTo 0

Set CmdBar = Application.CommandBars.Add(Name:="MyBar",
Position:=msoBarPopup, temporary:=True)

Set Ctrl = CmdBar.Controls.Add(Type:=msoControlButton)
With Ctrl
.Caption = "Click Me AAA"
.OnAction = "'" & ThisWorkbook.Name & "'!AAA"
End With

Set Ctrl = CmdBar.Controls.Add(Type:=msoControlButton)
With Ctrl
.Caption = "Click Me BBB"
.OnAction = "'" & ThisWorkbook.Name & "'!BBB"
End With
Set Ctrl = CmdBar.Controls.Add(Type:=msoControlButton)
With Ctrl
.Caption = "Click Me CCC"
.OnAction = "'" & ThisWorkbook.Name & "'!CCC"
End With

End Sub


Sub AAA()
MsgBox "AAA"
End Sub

Sub BBB()
MsgBox "BBB"
End Sub

Sub CCC()
MsgBox "CCC"
End Sub

The next question is how you want to trigger this command bar to display.
The following code in the Sheet1 module will substitute "MyBar" for the
default right-click command bar (named "Cell") if you right-click anywhere
within the range A1:C10 on Sheet1.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
If Not Application.Intersect(Target, Me.Range("A1:C10")) Is Nothing Then
Cancel = True ' don't display default menu
Application.CommandBars("MyBar").ShowPopup ' show ours
Exit Sub
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Dale Fye]

Chip,

Exactly what I was looking for. In Access I can do this via the
View-Toolbar-Customize menu, and build commandbars that reside in the
database. I don't have to do the code, so I had forgotten about that
option. Does the code creating the command bar have to be run every time my
UserForm is loaded, or is a one-time event?

Dale

Yes, you can do something similar to that. The first procedure create a
command bar named "MyBar". The next three procs are placeholders for real
code.

Sub CreateMenuItems()

Dim CmdBar As Office.CommandBar
Dim Ctrl As Office.CommandBarControl

On Error Resume Next
Application.CommandBars("MyBar").Delete
On Error GoTo 0

Set CmdBar = Application.CommandBars.Add(Name:="MyBar",
Position:=msoBarPopup, temporary:=True)

Set Ctrl = CmdBar.Controls.Add(Type:=msoControlButton)
With Ctrl
.Caption = "Click Me AAA"
.OnAction = "'" & ThisWorkbook.Name & "'!AAA"
End With

Set Ctrl = CmdBar.Controls.Add(Type:=msoControlButton)
With Ctrl
.Caption = "Click Me BBB"
.OnAction = "'" & ThisWorkbook.Name & "'!BBB"
End With
Set Ctrl = CmdBar.Controls.Add(Type:=msoControlButton)
With Ctrl
.Caption = "Click Me CCC"
.OnAction = "'" & ThisWorkbook.Name & "'!CCC"
End With

End Sub


Sub AAA()
MsgBox "AAA"
End Sub

Sub BBB()
MsgBox "BBB"
End Sub

Sub CCC()
MsgBox "CCC"
End Sub

The next question is how you want to trigger this command bar to display.
The following code in the Sheet1 module will substitute "MyBar" for the
default right-click command bar (named "Cell") if you right-click anywhere
within the range A1:C10 on Sheet1.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
If Not Application.Intersect(Target, Me.Range("A1:C10")) Is Nothing
Then
Cancel = True ' don't display default menu
Application.CommandBars("MyBar").ShowPopup ' show ours
Exit Sub
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)