Help making a macro to modify Left arrow key behavior when editing

[zxyzgt]

Hi there,

When editing/input text in cells, I need to move the cursor back 'in-between' characters using the left arrow key. However, Excel would simply select the cell left of the current cell unless I press F2 to enter into editing mode. I was wondering if there is any way to make a macro to:
1. Check the status of the cursor.
If the cursor is in cell-selecting mode, keep the default behavior;
elif in text inputting mode, sendkeys "{F2} {Left}".

2. This macro could be triggered by the left arrow key.

Thanks in advance!

 

[Jaafar Tribak]

Welcome to MrExcel.

I don't think you can do that unless you use a windows timer which I think is overkill since it will have a peformance impact on excel.

 

[Jaafar Tribak]

You could also try setting Application.EditDirectlyInCell to False

 

[zxyzgt]

Welcome to MrExcel.

I don't think you can do that unless you use a windows timer which I think is overkill since it will have a peformance impact on excel.

Thank you so much for your prompt reply!
I tried EditDirectlyInCell, not exactly what I need. Just want a smart solution to check the scenarios itself.
Still thank you for your suggestion.

 

[Jaafar Tribak]

Try this (using the windows timer approach to a minimum to avoid performance impact)

1- In a new Standard Module:

Option Explicit

#If VBA7 Then
    Declare PtrSafe Function SetTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr, ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As LongPtr
    Declare PtrSafe Function KillTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr) As Long
    Declare PtrSafe Function IsWindowVisible Lib "user32" (ByVal hwnd As LongPtr) As Long
    Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr
#Else
    Declare Function SetTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
    Declare Function KillTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long
    Declare Function IsWindowVisible Lib "user32" (ByVal hwnd As Long) As Long
    Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
#End If


Private bEditMode As Boolean


Public Property Let SetEditMode(ByVal bSet As Boolean)
   
    Call KillTimer(Application.hwnd, 0)
    If bSet Then
        bEditMode = False
        Call SetTimer(Application.hwnd, 0, 0, AddressOf TimerProc)
    End If
   
End Property

Private Sub TimerProc()

    #If Win64 Then
        Dim hwnd As LongLong
    #Else
        Dim hwnd As Long
    #End If
   
    On Error Resume Next
   
    hwnd = FindWindowEx(Application.hwnd, 0, "XLDESK", vbNullString)
    hwnd = FindWindowEx(hwnd, 0, "EXCEL6", vbNullString)
    If IsWindowVisible(hwnd) Then
        If Not bEditMode Then
            bEditMode = True
            SendKeys "{F2}"
            DoEvents
        End If
    Else
        bEditMode = False
    End If

End Sub

Private Sub Auto_Close()
    SetEditMode = False
End Sub

2- Usage example applied to range("A1:D20") ... change range address as required)
Code oes in the worksheet module:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Intersect(Target, Range("A1:D20")) Is Nothing Then
        SetEditMode = True
    Else
        SetEditMode = False
    End If

End Sub

This workaround should allow you to navigate with the arrow keys inside the cells in the specified range

 

[Jaafar Tribak]

EDIT

Change the following SendKeys section in the TimerProc:

From this :

If Not bEditMode Then
    bEditMode = True
    SendKeys "{F2}"
    DoEvents
End If

To this :

If Not bEditMode Then
     bEditMode = True
     SendKeys "{F2}"
     DoEvents
     SendKeys "{F2}"
End If

This is to avoid turning the NumLock key off.

 

[zxyzgt]

@Jaafar, sorry for my late reply. I really appreciate your help. I will have a try for the code.​