Always keep first part of text in a cell

[Guest]

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.

 

[Norman Jones]

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.

 

[Guest]

Norman,

Thank you very very much. It is working like a dream.
--
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.