| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Cell contents
Hi All,
Is it possible to change the cell value when there is any word exist in that cell? e.g. GPS; Ignition off TM; Ignition off ST; Ignition off Ignition off when there is Ignition off with anyother word the cell value change to Ignition off. Thanks & Regards Hassan |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Cell contents
Yes, with a change event macro. This is for cell B9, but can be modified for
any range: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Range("B9"), Target) Is Nothing Then Exit Sub s = "Ignition off" v = Target.Value If InStr(1, v, s) = 0 Then Exit Sub Application.EnableEvents = False Target.Value = s Application.EnableEvents = True End Sub Paste this in the worksheet code area, NOT a standard module. -- Gary''s Student - gsnu200746 "Hassan" wrote: Hi All, Is it possible to change the cell value when there is any word exist in that cell? e.g. GPS; Ignition off TM; Ignition off ST; Ignition off Ignition off when there is Ignition off with anyother word the cell value change to Ignition off. Thanks & Regards Hassan |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Cell contents
Hi Gary,
Is it possible to change in active sheet? Please write in standard module. "Gary''s Student" wrote: Yes, with a change event macro. This is for cell B9, but can be modified for any range: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Range("B9"), Target) Is Nothing Then Exit Sub s = "Ignition off" v = Target.Value If InStr(1, v, s) = 0 Then Exit Sub Application.EnableEvents = False Target.Value = s Application.EnableEvents = True End Sub Paste this in the worksheet code area, NOT a standard module. -- Gary''s Student - gsnu200746 "Hassan" wrote: Hi All, Is it possible to change the cell value when there is any word exist in that cell? e.g. GPS; Ignition off TM; Ignition off ST; Ignition off Ignition off when there is Ignition off with anyother word the cell value change to Ignition off. Thanks & Regards Hassan |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Cell contents
To make it work on the active sheet, the macro would have to be installed on
every sheet that the action is required. It is o.k. to make multiple copies of the code, one for each wroksheet. I see that it would be good to use a standard module but I don't know how to do it. -- Gary''s Student - gsnu200746 "Hassan" wrote: Hi Gary, Is it possible to change in active sheet? Please write in standard module. "Gary''s Student" wrote: Yes, with a change event macro. This is for cell B9, but can be modified for any range: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Range("B9"), Target) Is Nothing Then Exit Sub s = "Ignition off" v = Target.Value If InStr(1, v, s) = 0 Then Exit Sub Application.EnableEvents = False Target.Value = s Application.EnableEvents = True End Sub Paste this in the worksheet code area, NOT a standard module. -- Gary''s Student - gsnu200746 "Hassan" wrote: Hi All, Is it possible to change the cell value when there is any word exist in that cell? e.g. GPS; Ignition off TM; Ignition off ST; Ignition off Ignition off when there is Ignition off with anyother word the cell value change to Ignition off. Thanks & Regards Hassan |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Cell contents
Another option would be to make it into a workbook_sheetchange event.
This code is placed under the ThisWorkbook module (and the other worksheet events should be removed). Option Explicit Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim s As String Dim v As Variant If Intersect(Sh.Range("B9"), Target) Is Nothing Then Exit Sub s = "Ignition off" v = Target.Value If InStr(1, v, s, vbTextCompare) = 0 Then Exit Sub Application.EnableEvents = False Target.Value = s Application.EnableEvents = True End Sub If there are sheets that should be ignored, then that could be added to the code. Gary''s Student wrote: To make it work on the active sheet, the macro would have to be installed on every sheet that the action is required. It is o.k. to make multiple copies of the code, one for each wroksheet. I see that it would be good to use a standard module but I don't know how to do it. -- Gary''s Student - gsnu200746 "Hassan" wrote: Hi Gary, Is it possible to change in active sheet? Please write in standard module. "Gary''s Student" wrote: Yes, with a change event macro. This is for cell B9, but can be modified for any range: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Range("B9"), Target) Is Nothing Then Exit Sub s = "Ignition off" v = Target.Value If InStr(1, v, s) = 0 Then Exit Sub Application.EnableEvents = False Target.Value = s Application.EnableEvents = True End Sub Paste this in the worksheet code area, NOT a standard module. -- Gary''s Student - gsnu200746 "Hassan" wrote: Hi All, Is it possible to change the cell value when there is any word exist in that cell? e.g. GPS; Ignition off TM; Ignition off ST; Ignition off Ignition off when there is Ignition off with anyother word the cell value change to Ignition off. Thanks & Regards Hassan -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| change cell contents when pull down menu choices change | Excel Worksheet Functions | |||
| Change contents of a cell based on cell contents. | Excel Programming | |||
| Change contents of a cell with VBA | Excel Programming | |||
| change cell contents | Excel Programming | |||
| Please help! Macro to change cell contents based on cell to the left | Excel Programming | |||
Linear Mode
