Return caption of right click selection

[mikeymay]

I am adding a custom menu to the options when a right click is performed using the following

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

Dim objMenu As Object

Dim rngSource As Range

Dim strSource As String

Dim intCount As Integer

If Not Application.Intersect(Target, Range("E9:E508")) Is Nothing Then
   Else
   Exit Sub
End If

For Each objMenu In Application.ShortcutMenus(xlWorksheetCell).MenuItems
   If objMenu.Caption = "SOURCES" Then
      objMenu.Delete
      Else
   End If
Next objMenu

If ActiveCell.Offset(0, -4) = False Then
   Exit Sub
   Else
End If

Set objMenu = Application.ShortcutMenus(xlWorksheetCell).MenuItems.AddMenu("SOURCES", 1)

With objMenu.MenuItems
   Set rngSource = Sheets("File Data").Range("FD_SourceStart")
   
   intCount = 1
   
   Do Until rngSource = ""
      strSource = rngSource
      
      .Add strSource, "AddSource", intCount
      
      intCount = intCount + 1
      
      Set rngSource = rngSource.Offset(1, 0)
   Loop
End With

Set rngSource = Nothing

Set objMenu = Nothing

End Sub

When the right click menu is shown to the user and the make a selection from the SOURCES menu, I need to return the caption of the selection made as this needs adding to the activecell.


TIA

 

[mikeymay]

Found it after a bit more digging!

ActiveCell = Application.CommandBars.ActionControl.Caption