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

zxyzgt

New Member
Joined
Nov 4, 2020
Messages
3
Office Version
  1. 365
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!
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,306
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Dec 5, 2002
Messages
8,306
Office Version
  1. 2016
Platform
  1. Windows
You could also try setting Application.EditDirectlyInCell to False
 

zxyzgt

New Member
Joined
Nov 4, 2020
Messages
3
Office Version
  1. 365
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

Well-known Member
Joined
Dec 5, 2002
Messages
8,306
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

1- In a new Standard Module:
VBA Code:
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:
VBA Code:
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

Well-known Member
Joined
Dec 5, 2002
Messages
8,306
Office Version
  1. 2016
Platform
  1. Windows
EDIT

Change the following SendKeys section in the TimerProc:

From this :
VBA Code:
If Not bEditMode Then
    bEditMode = True
    SendKeys "{F2}"
    DoEvents
End If

To this :
VBA Code:
If Not bEditMode Then
     bEditMode = True
     SendKeys "{F2}"
     DoEvents
     SendKeys "{F2}"
End If

This is to avoid turning the NumLock key off.
 

zxyzgt

New Member
Joined
Nov 4, 2020
Messages
3
Office Version
  1. 365

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

 

Forum statistics

Threads
1,141,124
Messages
5,704,436
Members
421,349
Latest member
Santhosh3188

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top