Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit a Cell
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 ?? -- Gary''s Student - gsnu200827 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit a Cell
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" wrote in message ... 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 ?? -- Gary''s Student - gsnu200827 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit a Cell
Very nice Don.
Thank you! -- Gary''s Student - gsnu200827 "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Gary''s Student" wrote in message ... 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 ?? -- Gary''s Student - gsnu200827 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit a Cell
Glad to help
-- Don Guillett Microsoft MVP Excel SalesAid Software "Gary''s Student" wrote in message ... Very nice Don. Thank you! -- Gary''s Student - gsnu200827 "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Gary''s Student" wrote in message ... 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 ?? -- Gary''s Student - gsnu200827 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit a Cell
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! -- Gary''s Student - gsnu200827 "Peter T" wrote: 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" wrote in message ... 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 ?? -- Gary''s Student - gsnu200827 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit a Cell
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 (MVP - Excel) "Gary''s Student" wrote in message ... Very nice Don. Thank you! -- Gary''s Student - gsnu200827 "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Gary''s Student" wrote in message ... 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 ?? -- Gary''s Student - gsnu200827 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit a Cell
Thanks Rick
-- Gary''s Student - gsnu200827 "Rick Rothstein" wrote: 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 (MVP - Excel) "Gary''s Student" wrote in message ... Very nice Don. Thank you! -- Gary''s Student - gsnu200827 "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Gary''s Student" wrote in message ... 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 ?? -- Gary''s Student - gsnu200827 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit a Cell
I guess you figured this out already...
Sub InsertText() N = 3 SendKeys "{F2}{HOME}{RIGHT " & N & "}" End Sub -- Rick (MVP - Excel) "Gary''s Student" wrote in message ... 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! -- Gary''s Student - gsnu200827 "Peter T" wrote: 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" wrote in message ... 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 ?? -- Gary''s Student - gsnu200827 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Attempted edit of protected cell jumps user to another cell | Excel Discussion (Misc queries) | |||
Edit entry in one cell dependant on another cell | Excel Programming | |||
Disable cell reference insertion with PageUp in cell edit mode. | Excel Discussion (Misc queries) | |||
Edit cell automatically takes you to cell | Excel Worksheet Functions | |||
Can you make a cell with a diagonal, so that you can edit the upper and lower part of the cell? | New Users to Excel |