Hi there,
I wonder if it is possible to always keep one piece of text at the beginning
of a cell.

Lets say cells in range D5 to D2000 always need to contain the text
"Financial Review: " at the beginning then followed by the user's entry. If
the cell is empty nothing should be displayed.

Cells in range D5 to D2000 are to start with empty but will be populated
with any kind of text as we go along. The user might enter "Revenue to low
according to PL". After the user has entered "Revenue to low according to
PL" is it then possible to add "Financial Review: " with a macro. The Cell
should now display "Financial Review: Revenue to low according to PL".

Any help much appreciated.

--
Regards,

Martin

Hi Martin,

Try:

'=============>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim rCell As Range
Const sStr As String = "Financial Review: "

Set Rng = Me.Range("D52000") '<<==== CHANGE
Set Rng = Intersect(Rng, Target)

If Not Rng Is Nothing Then
On Error GoTo XIT
Application.EnableEvents = False
For Each rCell In Rng.Cells
With rCell
If Not IsEmpty(.Value) Then
.Value = sStr & Replace(.Value, sStr, _
vbNullString, 1, 1, vbTextCompare)
End If
End With
Next rCell

XIT:
Application.EnableEvents = True
End If
End Sub
'<<=============

This is worksheet event code and should be pasted into the worksheets's code
module (not a standard module and not the workbook's ThisWorkbook module):

Right-click the worksheet's tab
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.

---
Regards,
Norman


"Martin" <(E-Mail Removed)> wrote in message
news:50FE2A5A-0618-490F-A246-(E-Mail Removed)...
> Hi there,
> I wonder if it is possible to always keep one piece of text at the
> beginning
> of a cell.
>
> Lets say cells in range D5 to D2000 always need to contain the text
> "Financial Review: " at the beginning then followed by the user's entry.
> If
> the cell is empty nothing should be displayed.
>
> Cells in range D5 to D2000 are to start with empty but will be populated
> with any kind of text as we go along. The user might enter "Revenue to low
> according to PL". After the user has entered "Revenue to low according to
> PL" is it then possible to add "Financial Review: " with a macro. The Cell
> should now display "Financial Review: Revenue to low according to PL".
>
> Any help much appreciated.
>
> --
> Regards,
>
> Martin

Norman,

Thank you very very much. It is working like a dream.
--
Regards,

Martin


"Norman Jones" wrote:

> Hi Martin,
>
> Try:
>
> '=============>>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim Rng As Range
> Dim rCell As Range
> Const sStr As String = "Financial Review: "
>
> Set Rng = Me.Range("D52000") '<<==== CHANGE
> Set Rng = Intersect(Rng, Target)
>
> If Not Rng Is Nothing Then
> On Error GoTo XIT
> Application.EnableEvents = False
> For Each rCell In Rng.Cells
> With rCell
> If Not IsEmpty(.Value) Then
> .Value = sStr & Replace(.Value, sStr, _
> vbNullString, 1, 1, vbTextCompare)
> End If
> End With
> Next rCell
>
> XIT:
> Application.EnableEvents = True
> End If
> End Sub
> '<<=============
>
> This is worksheet event code and should be pasted into the worksheets's code
> module (not a standard module and not the workbook's ThisWorkbook module):
>
> Right-click the worksheet's tab
> Select 'View Code' from the menu and paste the code.
> Alt-F11 to return to Excel.
>
> ---
> Regards,
> Norman
>
>
> "Martin" <(E-Mail Removed)> wrote in message
> news:50FE2A5A-0618-490F-A246-(E-Mail Removed)...
> > Hi there,
> > I wonder if it is possible to always keep one piece of text at the
> > beginning
> > of a cell.
> >
> > Lets say cells in range D5 to D2000 always need to contain the text
> > "Financial Review: " at the beginning then followed by the user's entry.
> > If
> > the cell is empty nothing should be displayed.
> >
> > Cells in range D5 to D2000 are to start with empty but will be populated
> > with any kind of text as we go along. The user might enter "Revenue to low
> > according to PL". After the user has entered "Revenue to low according to
> > PL" is it then possible to add "Financial Review: " with a macro. The Cell
> > should now display "Financial Review: Revenue to low according to PL".
> >
> > Any help much appreciated.
> >
> > --
> > Regards,
> >
> > Martin
>
>
>