Data input in cells

R

RichP

Hello everyone. We have the requirement to continually input text into a
cell, and have the ability to keep the existing data already there, and just
add to it. The problem is once I have data in one cell, leave, and come back
to add more data, it erases the previous information already within that
cell. I have tried the Merge cells function and other options within Excel.
The data we are inputting is more text than numbers. We are using Excel 2003.

Thanks, Rich
 
D

Don Guillett

here is one I have used for numbers modified for text.
right click sheet tab>view code>insert this>

Option Explicit
Dim oldvalue As String

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$A$5" Then
On Error GoTo fixit
Application.EnableEvents = False
'If Target.Value = 0 Then oldvalue = 0
'Target.Value = Target.Value & " " & oldvalue
oldvalue = Target.Value
fixit:
Application.EnableEvents = True
End If
End Sub
 
D

Don Guillett

Use this instead so you can start over in that cell

Option Explicit
Dim oldvalue As String

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$A$5" Then
On Error GoTo fixit
Application.EnableEvents = False
If Target = "" Or Target = " " Then oldvalue = ""
Target.Value = oldvalue & " " & Target.Value
oldvalue = Target.Value
fixit:
Application.EnableEvents = True
End If
End Sub
 
J

Jim May

Don:

Can you expand on what this macro is doing?
If i enter in cell A5 This <<and return>>
then again with A5 the active cell I enter "is a test"
(without the quotes).. I get is a test
From what's being asked it seems the what should
be displayed is This is a test..
Confused, (all too often)..
TIA,
 
J

Jim May

Yeah,
Got it;
Only small glitch.. Currently
the " " is creating leading spaces.
Even when A5 is clear the =len(a5) = 1
This is good stuff (you've done).
TIA,
Jim
 
D

Don Guillett

this should fix all. Use either the delete key or spacebar to start over.

Option Explicit
Dim oldvalue As String

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$A$5" Then
On Error GoTo fixit
Application.EnableEvents = False
If Target = "" Or Target = " " Then
oldvalue = ""
Else
Target.Value = Trim(oldvalue & " " & Target)
oldvalue = Target
End If
fixit:
Application.EnableEvents = True
End If
End Sub
 
J

Jim May

Great,
Thanks for your input.
Jim

Don Guillett said:
this should fix all. Use either the delete key or spacebar to start over.

Option Explicit
Dim oldvalue As String

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$A$5" Then
On Error GoTo fixit
Application.EnableEvents = False
If Target = "" Or Target = " " Then
oldvalue = ""
Else
Target.Value = Trim(oldvalue & " " & Target)
oldvalue = Target
End If
fixit:
Application.EnableEvents = True
End If
End Sub
Click to expand...
 
Top