VBA Event code

[Vigash]

Hi Team,

I am working on a worksheet event. My requirement is whenever new data is added to the table I am trying to call a macro.

(My table has been connected to Mysql database)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Records As Range
Set Records = Range("Stockout")
If Not Application.Intersect(Records, Range(Target.Address)) Is Nothing Then
Call Module3.lastrow_value
End If
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.ListObjects("StockOut").ListRows.Count) Is Nothing Then
Call Module3.lastrow_value
End If
End Sub

Kindly suggest which one is right to achieve my expectation.

Regards,
Vigash

 

[davesexcel]

Which one works for you?


It's possible worksheet_change event would not work in this scenario, how is the data added

 

[Vigash]

Which one works for you?


It's possible worksheet_change event would not work in this scenario, how is the data added

Data added to excel table from mysql data base table via odbc connection..

Kindly help me to find ways to trigger macro when new data added to my table...

 

[Vigash]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Records As Range
Set Records = Range("Stockout")
If Not Application.Intersect(Records, Range(Target.Address)) Is Nothing Then
Call Module3.lastrow_value
End If
End Sub

This code seems to be working for me... But the issue is there is difference in output when I manually run lastrow_value macro I am getting different output which is accurate but when automatically trigger same macro it's giving different.. So my understanding is whether there is issue with event itself.... Correct me if I am wrong

 

[davesexcel]

If the event is running the macro, then it is doing what it should.

Check out

how to use Application.EnableEvents = False?

Good days everyone, I know that if set Application.EnableEvents to false will boost up the speed of macro, but after a batch of googling, i still didn't get how to use it, can some one please explain to me? thank you so much.

www.mrexcel.com

 

[Joe4]

Is the data linked from SQL?
If so, I don't think that will trigger a Worksheet_Change event procedure to run.
You would need to use something like Worksheet_Calculate instead (just note that there is no "Target" date parameter with that event code - it cannot tell which range changed, only that there was some recalculation somewhere on the worksheet).