Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Always keep first part of text in a cell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Always keep first part of text in a cell
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("D5:D2000") '<<==== 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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Always keep first part of text in a cell
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("D5:D2000") '<<==== 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF cell contains part of text | Excel Worksheet Functions | |||
Get part of text in a Cell | Excel Discussion (Misc queries) | |||
find text in cell and replace it with part of the text in that ce. | Excel Discussion (Misc queries) | |||
USE PART OF TEXT FROM ONE CELL IN ANOTHER | Excel Discussion (Misc queries) | |||
Display contents of cell in another cell as part of text string? | New Users to Excel |