How do I turn a VBA script on and off with an entry in a cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am am trying to cause the below script to populate the date and time
fields when F8 is set to Y and not when it is set to N and to turn on and off
as that field changes without having to restart Excel each time. Any help
would be greatly appreciated.

Thank you.
Script

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column <> 6 Then Exit Sub
Set rng = Range("F10:F999")
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
Application.EnableEvents = False
With Target(8, 6)
If Target.Value = N Then Exit Sub
End With
With Target(1, -3)
..Value = Int(Now() + _
(Worksheets("15 minute").Range("F6").Value - _
Worksheets("15 minute").Range("F7").Value) / 24)
..NumberFormat = "mmm dd, yyyy"
End With
With Target(1, -2)
..Value = Now() + _
(Worksheets("15 minute").Range("F6").Value - _
Worksheets("15 minute").Range("F7").Value) / 24 - _
Int(Now() + (Worksheets("15 minute").Range("F6").Value - _
Worksheets("15 minute").Range("F7").Value) / 24)
..NumberFormat = "hh:mm:ss"
End With
Application.EnableEvents = True
End Sub
 
On the worksheet's module add code for the OnChange event. GEt the
address of the active cell. If the active cell is F8 then, read the
contents and adjust your other cells accordingly. (ie. if the value is
"Y" - fil/updatel the other cells,if "N" turn off the updating
script.

Hope this helps.
 
Hi, Thank you for the reply. I am very new at the whole scripting thing and
have no idea what the code would be. Any samples would be greatly appreciated.

Thank you.
 
Back
Top