Code executing Twice each time it's called...

  • Try the attached version.


    I have changed the code so that the Check1 sub is called when cell C2 on Sheet1 changes.


    Code in Sheet1 Worksheet Object Module is

    Code
    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. If Not Intersect(Target, [c2]) Is Nothing Then check1
    3. End Sub


    I also redid your Check1 code, it is now

    Files

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hi Kj, Thanks for all your efforts with this, that check1 code looks very slick indeed.


    Unfortunately it isn't taking the schedule times into account in 'race1' (D1 to L1), as soon as the feed comes in no matter what time it is it fires up check 1 every second , the pasting is going in exactly the right places but the schedule times are in effect redundant.

  • So the feed is updating sheet1 every second?


    If so, then you need check1 to run when the time in C2 of the updated Sheet1 data matches a time in race1 D1:L1. Is that correct?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Yes that's correct sheet1 will be updating depending on the API setting which currently is set to 1 second.


    And yes spot on i only want to fire up check1 when C2 matches any of the schedule times in those cells on race 1 (D1:L1).That would be exactly what's required.


    I

  • OK try this version then.


    I have made the range D1:L1 a named range (named "Times"), and the Sheet1 Worksheet Object Module code is changed to

    Code
    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. If Not IsError(Application.Match([c2], [Times], 0)) Then check1
    3. End Sub

    Files

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • If you still need the Update button then you need to change the code assigned to that button to

    Code
    1. Private Sub CommandButton2_Click()
    2. If Not IsError(Application.Match([a21], [Times], 0)) Then check1
    3. End Sub

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • It's waiting for the schedule times and guess what Kj, it's firing the macro twice again! like it did in the initial code (your code is obviously immeasurably better).


    Is this stuff in 'thisworkbook' as Carim suggested earlier a potential problem?


    I'm not even sure if it's doing anything, I have totally reworked this book from what it used to be. (it was a book initially coded by associates of the ApI but it was awful for my needs.

  • Try changing the worksheet change code to

    Code
    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. If Not IsError(Application.Match([c2], [Times], 0)) Then
    3. Application.EnableEvents = 0
    4. check1
    5. Application.EnableEvents = 1
    6. End If
    7. End Sub

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Yes, you should move those 2 routines out of ThisWorkbook and into a standard module, as Carim showed in the file he attached.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Only pasting once for me! Make sure the Workshhet_Calculate code has been removed from the race1 Worksheet Object Module.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Can I please repost this book back to you because I have become totally and utterly lost with all the changes and folders and carim's version etc etc. I really have tried to follow but I'm not sure now what should be where, I'm so sorry.

  • I was obviously very close to the solution KJ as you reached it. From the double posting problem, i have tried to follow all the changes on here and also update your sheet with Carim's changes (to thisworkbook code) but am now actually managing to get error messages in check 1.


    Here is all the code I have in my book now, If you could have a look and see what is going wrong I would be most grateful.



    Sheet1


    HTML
    1. Option Explicit
    2. Private Sub Worksheet_Change(ByVal Target As Range)
    3. If Not IsError(Application.Match([c2], [Times], 0)) Then
    4. Application.EnableEvents = 0
    5. check1
    6. Application.EnableEvents = 1
    7. End If
    8. End Sub


    Sheet3(race1)



    Sheet5(tjo)



    Thisworkbook


    HTML
    1. Option Explicit
    2. 'This code should go in a regular module sheet, and won't work properly if installed anywhere else. _
    3. The next (Dim) statement must occur before any subs or functions.
  • I got rid of the many versions of this sheet and downloaded version 2.2 again which has the following...


    MODULE 1



    The accompanying WORKSHEET 1 code


    HTML
    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. If Not IsError(Application.Match([c2], [Times], 0)) Then
    3. Application.EnableEvents = 0
    4. check1
    5. Application.EnableEvents = 1
    6. End If
    7. End Sub



    The old problem is persisting when sheet 1 is live with the live time changing in cell C2, The check1 code will execute on time according to the schedule in 'race1' sheet the value in A20 will turn from 0 to 1 butthen when reverting from 1 to 0 after the first paste it quickly pastes again and I am left with this problem of 2 pastes rather than 1.


    If I disable the live feed into 'sheet1' and manually enter time values that will match the schedule times on 'race1' sheet the check 1 code will fire once and work perfectly every time with a single paste.



    KJ, I can only assume that if you have got this same code to paste once the problem is with the feed when it is live and changing. When I manually enter a time in C2 and it works fine and pastes once the 1 value in cell A20 on race1 sheet remains.Is the problem perhaps that when live and the time moves on past the schedule time again aftyer firing the macro, the change in cell A20 from 1 back down to zero is firing check one for a 2nd time.


    before trigger time A20 =0 when hits trigger time changes to 1 and fires check 1
    after trigger time A20 reverts back to 0 from 1 and fires for the 2nd time


    Amazing how something that seemed so simple is proving to be so awkward!


    I appreciate you have spent plenty of time on this already, If you think of anything new I would be most grateful, I will also be trying to see if I can find a similar problem on the net that was overcome.


    Many Thanks

  • A couple of changes you need to make.


    Change the Update button code to

    Code
    1. Private Sub CommandButton2_Click()
    2. If Not IsError(Application.Match([a21], [Times], 0)) Then check1
    3. End Sub


    and the start of the check1 code should be

    Code
    1. Option Explicit
    2. Dim NextTime As Double
    3. Sub check1()
    4. Dim x, lRow As Long


    Neither of those changes will stop check1 from running twice though!


    For me making any change to Sheet1 will, if the time in C2 is an exact match to a time in race1 D1:L1, fire check1 just once (as does clicking the Update button).


    If check1 runs twice when your Sheet1 gets automatically updated then I suspect that the fault lies in the code that does the automatic updating, I think it may be updating Sheet1 twice, or there is something in that code that checks the value of race1 A20. If so, then try commenting out the bit if code that checks A20.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.