right-click shortcut menu

[mark]

Could anyone suggest a good reference, or show an example,
of how to enable a right-click shortcut menu with things
like copy/paste, for a textbox in a userform?


Thanks.

 

[Bob Phillips]

Mark,

Here is a technique posted by John Green last year

A short cut menu can be created as a popup commandbar. The menu can be
activated from the MouseUp event of the textbox.

You can create a popup menu using code like the following in a standard
module.

--------------------------------------------------------------------------
Sub MakePopUp()
'Remove any old instance of MyPopUp
On Error Resume Next
CommandBars("MyPopUp").Delete
On Error GoTo 0

With CommandBars.Add(Name:="MyPopUp", Position:=msoBarPopup)
With .Controls.Add(Type:=msoControlButton)
.OnAction = "ShowDataForm"
.FaceId = 264
.Caption = "Data Form"
End With
With .Controls.Add(Type:=msoControlButton)
.OnAction = "Sort"
.FaceId = 210
.Caption = "Sort Ascending"
End With
End With
End Sub

Sub ShowDataForm()
MsgBox "DataForm"
End Sub

Sub Sort()
MsgBox "Sort"
End Sub
----------------------------------------------------------------------

In your userform code module choose the Textbox from the top left dropdown
and the MouseUp event from the top right dropdown and insert something like
the following code:

----------------------------------------------------------------------------
Private Sub TextBox1_MouseUp(ByVal Button As Integer, ByVal Shift As
Integer, ByVal X As Single, ByVal Y As Single)
If Button = 2 Then
Application.CommandBars("MyPopUp").ShowPopup
End If
End Sub
---------------------------------------------------------------------------




--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[mark]

Thank you.

 

[Soo Cheon Jheong]

Mark,

1) Put these in your Standard Code Module:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Public Const CON_POPUP As String = "POPUP_MENU"

Sub TEST()
UserForm1.Show
End Sub

Sub POPUP_ON()
Call POPUP_OFF
With CommandBars.Add(Name:=CON_POPUP, Position:=msoBarPopup)
With .Controls.Add(Type:=msoControlButton)
.OnAction = "Macro_1"
.FaceId = 1234
.Caption = "Print"
End With
With .Controls.Add(Type:=msoControlButton)
.OnAction = "Macro_2"
.FaceId = 1235
.Caption = "Copy"
End With
End With
End Sub

Sub POPUP_OFF()
On Error Resume Next
Application.CommandBars(CON_POPUP).Delete
End Sub

Sub Macro_1()
MsgBox "Macro_1"
End Sub

Sub Macro_2()
MsgBox "Macro_2"
End Sub
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


2) Put these in your UserForm Code Module:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Private Sub UserForm_Initialize()
Call POPUP_ON
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Call POPUP_OFF
End Sub

Private Sub TextBox1_MouseUp(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
If Button = 2 Then Application.CommandBars(CON_POPUP).ShowPopup
End Sub

Private Sub CommandButton1_Click()
Unload Me
End Sub
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


--
Regards,
Soo Cheon Jheong
_ _
^¢¯^
--

 

[mark]

Thanks... I just did that, and see what you are doing.

Is there a list of the face ID constants?

If not, I can just find them through testing.

Mark

 

[mark]

Thanks... I just did that, and see what you are doing.

Is there a list of the face ID constants?

If not, I can just find them through testing.

Mark

 

[Bob Phillips]

John Walkenbach has a utility to show them at
http://j-walk.com/ss/excel/tips/tip67.htm


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

Hi, anyone know how to add SORT in the short cut menu when you right-click?

 

[David McRitchie]

If you just want to sort on a single column
select a cell in that column, then Ctrl+A (need a macro if Excel 2003),
then use the sort button (ascending or descending)
but you can never be completely sure if Excel will assume headers or not.

More specific to your question:

Record a macro and insert it into Right Click Event envelope

Private Sub Worksheet_BeforeRightClick(ByVal Target _
As Excel.Range, Cancel As Boolean)
Cancel = True 'to get out of edit mode
Dim LRow As Long '-- SORT on Col E then A
'Find row before last row in Column A with content
LRow = Cells(Rows.Count, 1).End(xlUp).Offset(-1, 0).Row
Rows("2:" & LRow).Sort Key1:=Range("E2"), _
Order1:=xlAscending, Key2:=Range("A2"), _
Order2:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub

Event Macros, Worksheet Events and Workbook Events
http://www.mvps.org/dmcritchie/excel/event.htm

Also of interest might be
Sorting, Some notes on sorting in Excel
http://www.mvps.org/dmcritchie/excel/sorting.htm#activate

 

[Soo Cheon Jheong]

Macroq,

Try the following code:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Option Explicit
Dim CT As CommandBarControl
Sub Add_SortMenu()
With Application.CommandBars("Cell")
.Enabled = True
For Each CT In .Controls
If CT.ID = 928 Then CT.Delete
Next
.Controls.Add Type:=msoControlButton, ID:=928
End With
End Sub

Sub Del_SortMenu()
With Application.CommandBars("Cell")
For Each CT In .Controls
If CT.ID = 928 Then CT.Delete
Next
End With
End Sub
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


--
Regards,
Soo Cheon Jheong
_ _
^¢¯^
--