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
|
Similar Threads
