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

F

ffzeus

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
 
D

Dweeber

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.
 
F

ffzeus

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top