Get cursor position in cell

Question

I am trying to create a custom context menu in the formula bar (the one that appears when you right-click when you are inside a cell by typing, for example). I managed to get the button that I want by right-clicking, but I can not find how to determine where the cursor is inside the cell.

For example, if the cursor is after the 5th character in a cell, how can I determine what happens before or during the BeforeRightClick event? The only thing I found is 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 ultimate goal is to insert some standard substrings as you type into the cell. For example, I would like to insert [hi this is standard string X-XXX-XXXX] , right-clicking as you type into the cell.

EDIT:

I tried using sendkeys to send my string directly, but I just get the "ping" sound from Excel, which indicates that this is not possible. This was what I 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, it just pings directly, because I'm in edit mode. It does not activate a breakpoint if I put it there. So Sendkeys may not be as much a problem as running a function.

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

 '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 are in cell editing mode, you do not have events triggered in VBA. Although there might be some deeper Windows dll code that 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

In particular, you want to catch the TextChanged event:

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

Dozens of events are fired at this facility, including KeyDown, KeyPress, and KeyUp, so I'm sure you can find everything that is best for your scenario.