VBA - Edit Mode & Go to Start of Cell without SendKeys

[Eskuppe]

In VBA, I would like to enter the Edit mode of a selected cell, and move the cursor to the start of the cell.

Via the keyboard, this is accomplished by F2 & Ctrl+Home, and I have been using this code:

SendKeys ("{F2}^{HOME}"), True

SendKeys is causing a NumLock toggle issue , and the advice is to avoid using SendKeys. However, an alternative is seldom suggested.

Can anyone please suggest a solution without SendKeys?
If not, do you know how to keep NumLock on, noting that putting SendKeys "{NUMLOCK}", True after the code just adds an extra toggle.

 

[JLGWhiz]

You can put the cursor in any range with VBA by the range.select method, example

Range("B10").Select

As far as edit mode, you just tell it what you want in the cell by enclosing the text in quotation marks, example.

Range("B10") = "Hello There"

and you don't have to select the cell to do that, the statement tells Excel what to write in the cell If you want to edit text that is already in the cell, you can just replace it with the correct text by over writing the incorrect text, example

Range("B10") = "Hello out there"

There are other ways to edit cell content, but that is enough tutoring for now.

 

[Eskuppe]

Thanks for your advice, JLGWhiz.

I know quite well how to change the value of a cell, that's not what I'm trying to achieve.

We have a comment history column where for each row, there is a number of comments within the same cell where the newest comment is at the top. For eg:

2/3 - Update 32.3
28/2 - Update 32.2
14/2 - Update 32.1

Rather than make users select the cell, double click, press Alt + Enter to create a new line in the cell, then go to the start of the cell, I have made a button that creates the space for the new comment.

I now only need to go into edit mode, and go to the start of the cell.

I know it's possible, because anything possible in Excel can be programmed in VBA. Just need to know how, please.

 

[Trebor76]

Hi Eskuppe,

Seems like SendKeys is the only option. Try the following which I have slightly adapted from Rick Rothstein's solution from here:

Option Explicit
Sub LeaveInEditMode()
    'https://www.mrexcel.com/forum/excel-questions/727916-there-way-vba-leave-cell-edit-mode.html
    Dim Char As Long
    Char = -1  ' 5 for fifth character and -1 to move in front of that character
    Range("A9").Select
    SendKeys "{F2}", True
    DoEvents
    SendKeys "{Left  " & Len(ActiveCell.Text) - Char & "}", True
    DoEvents
End Sub

Hi JLGWhiz:

Happy 10,000

Robert

 

[Eskuppe]

Thanks very much, Robert. DoEvents & separating the commands seem to have done the trick, however I have kept Ctrl+Home:

SendKeys "{F2}", True
DoEvents
SendKeys "^{HOME}", True
DoEvents

This really is a stupid problem to have to deal with, but this is MS after all .

Thanks again.
Destiny is all.