Edit a Cell

[Gary''s Student]

I have a cell that already contains some text. I need a macro that:

1. selects the cells
2. opens it for editting (like touching F2)
3. positions the editting cursor just after the third character in the cell

So, for example, if the cell contains:

Now is the time

and the user runs the macro, any text the user types next would be entered
just after the Now

My first attempt was:

Sub editt()
Range("B2").Select
Application.SendKeys ("{F2}")
For i = 1 To 100
Application.SendKeys ("{LEFT}")
Next
For i = 1 To 3
Application.SendKeys ("{RIGHT}")
Next
End Sub

This works, but only for machines that accept SendKeys.

I can't figure out how to enter edit mode without SendKeys ??

 

[Don Guillett]

Try it this way

Sub inserttext()
tti = InputBox("Text to insert")
at = "Now is the time"
With ActiveCell ' or range("b2")
x = InStr(.Value, at)
tr = Right(.Value, Len(.Value) - Len(at) - x + 1)
'MsgBox Left(.Value, x + Len(at)) & "" & tti & tr
..Value = Left(.Value, x + Len(at)) & "" & tti & tr
End With
End Sub

 

[Peter T]

Karl Peterson has provided an excellent replacement for SendKeys

http://vb.mvps.org/samples/project.asp?id=sendinput

Import the bas module MSendInput into your VBA project. This was written for
VB5/6 so some minor changes for VBA:

- add the following constant definitions at the top of the module
Const vbShiftMask = 1&
Const vbKeyScrollLock = 145&

- find and comment any lines starting Debug.Print

- remove
#If Not VB6 Then
Private Function Split etc

though if you need to cater for Excel97 you'll need to do something like
this
' Break into pieces, if possible.
#If VBA6 Then
pieces = Split(this, " ")
#Else
pieces = Split97(this, " ")
' Karl's VB5 function needs a little adaptation for Excel97
#End If

Looks like you want the cursor in after the 3rd character, I'd do it like
this

Sub test2()
Dim sKeys As String
Dim editPos As Long, i As Long
' Excel needs to be the active window
' so run this from alt-F8 or a button, or API activate

Range("B2").Select ' contains 3+ characters

sKeys = "{F2}{HOME}"
editPos = 3
For i = 1 To editPos
sKeys = sKeys & "{RIGHT}"
Next
' Application.SendKeys sKeys
' or in Vista / Win7
MySendKeys sKeys

End Sub

Regards,
Peter T

 

[Gary''s Student]

Very nice Don.
Thank you!

 

[Don Guillett]

Glad to help

 

[Gary''s Student]

THANKS!!

The reference you sugested is a TREASURE!

This will help me avoid problems in the future and fix a large pile of old
ones.

Thanks again!

 

[Rick Rothstein]

Here is another way to code Don's approach, in addition to which I have
modified to allow you to specify the character position to insert the text
at (see notes at after the code)...

Sub InsertText()
Text = InputBox("Text to insert and position (use comma delimiter)")
Comma = InStrRev(Text, ",")
With ActiveCell
AfterNthChar = Val(Mid(Text, Comma + 1))
If Comma > 0 Then Text = Left(Text, Comma - 1)
.Characters(AfterNthChar + 1, 0).Insert Text
.Value = .Value
End With
End Sub

You would answer the InputBox with the exact text you want to insert (add
any separating spaces you might want directly to the text), then type a
comma and a number... your text can have commas in it as the code will use
the whatever is after the *last* comma to retrieve the number. The number
you type after the last comma is the character position (of the text in the
active cell) *after* which you want the text (you answered the InputBox
with) to be inserted at. You can add spaces after the *last* comma (before
the number) if you want. So, if the cell contained "Now is the time" and you
answered the InputBox with "_definitely,6" (without the surrounding quote
marks) (also the underbar is a stand in character for a space which I used
just in case it fell at a word wrap position in you newsreader), then the
cell would read "Now is definitely the time" afterwards. If you omit the
comma/number, a zero is assumed (that is, the text will be placed in front
of the existing cell text). Also, I used the .Value=.Value statement in
order to force the Formula Bar to update to show the newly edited text.

 

[Gary''s Student]

Thanks Rick
--
Gary''s Student - gsnu200827

Here is another way to code Don's approach, in addition to which I have
modified to allow you to specify the character position to insert the text
at (see notes at after the code)...

Sub InsertText()
Text = InputBox("Text to insert and position (use comma delimiter)")
Comma = InStrRev(Text, ",")
With ActiveCell
AfterNthChar = Val(Mid(Text, Comma + 1))
If Comma > 0 Then Text = Left(Text, Comma - 1)
.Characters(AfterNthChar + 1, 0).Insert Text
.Value = .Value
End With
End Sub

You would answer the InputBox with the exact text you want to insert (add
any separating spaces you might want directly to the text), then type a
comma and a number... your text can have commas in it as the code will use
the whatever is after the *last* comma to retrieve the number. The number
you type after the last comma is the character position (of the text in the
active cell) *after* which you want the text (you answered the InputBox
with) to be inserted at. You can add spaces after the *last* comma (before
the number) if you want. So, if the cell contained "Now is the time" and you
answered the InputBox with "_definitely,6" (without the surrounding quote
marks) (also the underbar is a stand in character for a space which I used
just in case it fell at a word wrap position in you newsreader), then the
cell would read "Now is definitely the time" afterwards. If you omit the
comma/number, a zero is assumed (that is, the text will be placed in front
of the existing cell text). Also, I used the .Value=.Value statement in
order to force the Formula Bar to update to show the newly edited text.

 

[Rick Rothstein]

I guess you figured this out already...

Sub InsertText()
N = 3
SendKeys "{F2}{HOME}{RIGHT " & N & "}"
End Sub