How to Turn OFF a Worksheet_Change Event "after" a single event?
I'm a newbie to VBA so please be patient.
I have a live data feed in cell D18 and it's changing constantly though my aim is to capture the first time it populates(first Event) with a number each morning and copy it into cell F18(locked down)...the code below captures "every" change(event)in the cell though all I need is the first change then to exit the sub completely?
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("D18"), Target) Is Nothing Then
'MsgBox "D18 has changed"
Range("F18").Value = Target
End If
End Sub
There must be a simple answer to this?
worksheet change with if criteria vba
Hi there,
I am desperately trying to track changes in an excel WS between range (K5:BB1500). However, I only want each individual one of these cells to be formatted red if an entry is made or changed after the database lockdown date which is specified in column K in the same row.
In other words, format each changed or new cell entry if the date in column K is < Today()
Thanks
James
worksheet change with if criteria vba
Hi James,
Enter the following code into Worksheet_Change event:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("K5:BB1500")) Is Nothing Then ' Check if change was made in range K5:BB1500
Exit Sub ' If change is outside, exit procedure
Else ' If change was made in range K5:BB1500, then
If Date > Cells(Target.Row, 11) Then ' Check date K column in the same row and compare it to today's date
Target.Interior.ColorIndex = 3 ' if date in K column in the same row is before today, mark cell in red
End If
End If
End Sub
worksheet change with if criteria vba
Hi James,
Enter the following code into Worksheet_Change event:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("K5:BB1500")) Is Nothing Then ' Check if change was made in range K5:BB1500
Exit Sub ' If change is outside, exit procedure
Else ' If change was made in range K5:BB1500, then
If Date > Cells(Target.Row, 11) Then ' Check date K column in the same row and compare it to today's date
Target.Interior.ColorIndex = 3 ' if date in K column in the same row is before today, mark cell in red
End If
End If
End Sub
The following code will
The following code will perform a worksheetchange event only once. Note the UpDateFlag variable is global.
Option Explicit
Dim UpDateFlag As Boolean
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If UpDateFlag = True Then Exit Sub
' YOUR UPDATING CODE HERE
UpDateFlag = True
End Sub
The Following code will
Hi Andy,
I tried your code suggestion as per below:
Private Sub Worksheet_Change(ByVal Target As Range)
' Set initial Worksheet_Event Flag status
UpdateFlag = False
If UpdateFlag = True Then Exit Sub
If Not Intersect(Range("D18"), Target) Is Nothing Then
Range("F18").Value = Target
UpdateFlag = True
End If
End Sub
As suggested I added the option explicit & dimmed the UpDateFlag as Boolean in a separate module..
Unfortunately the cell continued to update each time after the first event.
The code won't work if I add an "and" condition to the intersect code row either.
I then tried the following suggestion from Nick with the dates:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WCDate As Date
If WCDate <> Date Then Exit Sub
If Not Intersect(Range("D18"), Target) Is Nothing Then
Range("F18").Value = Target
WCDate = DateAdd("d", 1, Date)
End If
End Sub
This code did not update at all...unfortunately.
So I'm stuck currently unless anyone can suggest a better method to capture the first price that arrives through the data feed cell D18 at a specified time each day?
fyi..."On.Time" method gives close but not exact results..hence I thought Worksheet_Change would be the best option to capture this price?
All suggestions welcome??
In your first sub you had
In your first sub you had :-
UpdateFlag = False
If UpdateFlag = True Then Exit Sub
.... Updateflag is ALWAYS false so it will always update
Not sure why the second is not working but
Copy and paste all of the following into a worksheet module :-
Option Explicit
Dim UpDateFlag As Boolean
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If UpDateFlag = True Then Exit Sub
If Not Intersect(Range("D18"), Target) Is Nothing Then
Range("F18").Value = Target 'Shouldn't this read Range("F18").Value = Target.value
UpdateFlag = True
End If
End Sub
2 things 1. If you want to
2 things
1. If you want to freeze at a particular interval, take a look here:
http://excelexperts.com/vba-tips-run-code-every-hour-minute-or-second
2. If you want to freeze the first update and only that, add the date to another cell, and check whether today's date is greater than the date in the cell