Get line no from right click menu ("Cell")

[Jorgen Bondesen]

Hi NG

When I'm right clicking in a cell, I want my owen menu.
I can do this.
The menu depends on text in 5, 10 og 20 consecutive cells. I'm using the
text.

snip **** start

Dim lCount As Long
For lCount = 1 To lListCount
Dim MyList
MyList = data(lCount) '// text from cells

Dim cBut As CommandBarButton
Set cBut = .CommandBars("Cell").Controls.Add(Type:=msoControlButton,
Temporary:=True)

With cBut
.Caption = MyList
.Style = msoButtonIconAndCaption ' msoButtonCaption
.FaceId = lCount + 50
.SetFocus
.OnAction = "RunMe"
.Tag = lCount
End With

snip **** end

When I'm clicking on e.g 3th line in right click menu ("Cell"), how can I
knew this, if I only have .OnAction = "RunMe"

If .OnAction = "RunMe_" & lCount then I must have aboute 30 macros or trap
error, because I do not have any macro, and read trapinfo or macro name and
number.

Perhaps and quite different approach?

 

[GS]

Jorgen Bondesen brought next idea :

Hi NG

When I'm right clicking in a cell, I want my owen menu.
I can do this.
The menu depends on text in 5, 10 og 20 consecutive cells. I'm using the
text.
When I'm clicking on e.g 3th line in right click menu ("Cell"), how can I
knew this, if I only have .OnAction = "RunMe"

If .OnAction = "RunMe_" & lCount then I must have aboute 30 macros or trap
error, because I do not have any macro, and read trapinfo or macro name and
number.

Perhaps and quite different approach?

Different approach!

If all your test values are text then you can use a delimited string to
check the contents of the cell right-click against using InStr(). This
assumes the text entered is an element of an expected list.

In a standard module declarations section:
Public Const gsValidText As String = "Text1,Text2,Text3,Text4,Text5"

I suggest you position your menu at the top of the popup so it's more
readily available to your user, AND makes your managing of the menuitem
in code a bit easier.

**Note: The dot preceeding 'CommandBars' in your Set statement isn't
necessary. (The CommandBars collection is one of the Application
globals you can access without specifying 'Application.' as the
object reference**

You can use a single proc for the OnAction and just redirect code flow
within that proc using a 'Select Case' construct that determines what
code to execute based on the menuitem's Caption.

<aircode>
Sub RunMe()
Select Case CommandBars.ActionControl.Caption
Case "Text1": Call Text1Proc
Case "Text2": Call Text2Proc
Case "Text3": Call Text3Proc
'and so on...
End Select
End Sub
**Note that you could use separate procs for each text item and
redirect to there from this entry point (as shown here)
OR
You could write the code under each 'Case' and run all from here.**


You can set Caption/Visible props via the Worksheet_BeforeRightClick
event.

<aircode>
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)
With CommandBars("Cell").Controls("RunMe")
If InStr(1, gsValidText, Target.Value, vbTextCompare) > 0 Then
.Caption = Target.Value: .Visible = True
Else
.Caption = "RunMe": .Visible = False
End If
End With
End Sub

This will make the menuitem appear only if the cell content
meets your criteria.


To build the menuitem:

In the Workbook_Open event:
Private Sub Workbook_Open()
Call AddMenus
End Sub

OR.. in a standard module:
Sub Auto_Close()
Call AddMenus
End Sub

Sub AddMenus()
' Delete the menu if it exists, then replace it
Dim NewMenu As CommandBarControl
On Error Resume Next
With CommandBars("Cell").Controls(1)
If InStr(1, gsValidText, .Caption, vbTextCompare) > 0 _
Or .Caption = "RunMe" Then .Delete
End With
On Error GoTo 0
Set NewMenu = _
CommandBars("Cell").Controls.Add(Type:=msoControlButton, _
Before:=1, Temporary:=True)
With NewMenu
.Caption = "RunMe": .OnAction = "RunMe": .Visible = False
End With
End Sub

To remove the menu:

In the Workbook_BeforeClose event:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
CommandBars("Cell").Reset
End Sub

OR.. in a standard module:
Sub Auto_Close()
CommandBars("Cell").Reset
End Sub

 

[Dave Peterson]

I'm not sure what you're doing where you'd need that many choices inside the
rightclick menu.

Maybe you could use the activecell.row or the row with the number of rows in the
first area of the selection????

But since you're using the .tag property, you could call the same RunMe
procedure and just decide based on that .tag of the button you clicked.

I don't know what data() does, so I just plopped in some text:

Option Explicit
Sub auto_open()

Dim lCount As Long
Dim MyList As Variant
Dim lListCount As Long
Dim cBut As CommandBarButton

lListCount = 4

For lCount = 1 To lListCount
MyList = "runme " & lCount
Set cBut = Application.CommandBars("Cell").Controls _
.Add(Type:=msoControlButton, Temporary:=True)
With cBut
.Caption = MyList
.Style = msoButtonIconAndCaption ' msoButtonCaption
.FaceId = lCount + 50
'.SetFocus
.OnAction = ThisWorkbook.Name & "!RunMe"
.Tag = lCount
End With
Next lCount
End Sub

Sub RunMe()
With Application.CommandBars.ActionControl
'MsgBox .Caption & vbLf & .Tag
Select Case .Tag
Case Is <= 2
MsgBox "it's small"
Case Else
MsgBox "it's not small"
End Select
End With
End Sub

 

[GS]

If you have several menu choices to add then I suggest using your own
custom popup menu to replace "Cell". This would be easier to manage
than working with multiple menuitems added to "Cell". Let me know if
this is a viable option for you...

 

[Jorgen Bondesen]

Hi Garry

Thanks, very usefull.

 

[Jorgen Bondesen]

Hi Dave.

Thanks, very usefull.