Get cursor position in a cell

Question

I'm trying to create a custom right click menu in the formula bar (the one that appears when right clicking as you're inside a cell, typing for example). I have managed to get the button I want in my right click, but I can't find how to figure out where the cursor is located inside the cell.

For example, if the cursor is after the 5th character in the cell, how can I determine that either before or during the BeforeRightClick event occurs? The only thing I found was the opposite problem; placing the cursor in a specific part of the cell, using SendKeys:

'===================================================================================
'- MOVE CURSOR IN THE FORMULA BAR AS REQUIRED
'- Brian Baulsom September 2007
'===================================================================================
Sub test()
    Dim c As Integer
    c = 3
    '------------------------------------------------------------------------------
    Range("A1").Select      ' select cell
    SendKeys "{F2}", True   ' activate formula bar. cursor is to right of contents.
    DoEvents
    '------------------------------------------------------------------------------
    '- move cursor 3 characters to the left in the formula bar
    '- or in the cell if Tools/Options/Edit .. "Edit directly in cell" is checked
    SendKeys "{LEFT " & CStr(c) & "}", True         ' ="{LEFT 3}"
    DoEvents
End Sub
'-----------------------------------------------------------------------------------
source: http://www.mrexcel.com/forum/excel-questions/282172-setting-cursor-position-cell-text-using-visual-basic-applications.html

The end goal is to make it possible to insert some standard substrings while typing in the cell. For example I'd want to insert [hi this is standard string X-XXX-XXXX] by using the right click as I type in the cell.

EDIT:

I tried using sendkeys to send my string directly, but I just get a "ping" sound from Excel indicating it's not possible. This was what I had tried:

With fbar.Controls.add(Temporary:=True, Type:=msoControlButton, Before:=1)
    .BeginGroup = False
    .FaceId = 267
    .Caption = wsLabels.GetLabel("rcRefMoM")
    .OnAction = "'" & ThisWorkbook.Name & "'!'rcAddRef2 '"
End With

Function rcAddRef2()
    SendKeys (" [Ref:X \NAME]")
End Function

EDIT 2: Actually it doesn't even enter my rcAddRef2 function at all, it just pings straight away because I'm in edit mode. It doesn't activate a break point if I put one there. So Sendkeys may not be the problem, as much as getting the function to run.

It works fine in the Cell command bar if I try it, so I'm at a loss a bit:

'This runs
 With cbar.Controls.add(Temporary:=True, Type:=msoControlButton, Before:=1)
    .BeginGroup = False
    .FaceId = 267
    .Caption = wsLabels.GetLabel("rcRefMoM")
    .OnAction = "'" & ThisWorkbook.Name & "'!'rcAddRef2 '"
End With

Answer 1

Once you're in cell edit mode you don't have events being fired into VBA. Although there might be some deeper Windows dll code you could insert, a much better option would be to use a UserForm with a TextBox object:

https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.controls.textbox.aspx

Specifically, you want to intercept the TextChanged event:

https://msdn.microsoft.com/en-us/library/system.windows.forms.control.textchanged.aspx

There are a few dozen events triggered on that Object, including KeyDown, KeyPress, and KeyUp so I'm sure you can find whatever is best for your scenario.