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
|