Time Delay?

  • I'm trying to write a macro that copies some cells into a specified place but then shows the user where it has pasted the cells just to make them happy about it.


    For this I need to know how to set a time delay in a macro so that I can make the macro show them the paste location and then return to my form 4 seconds later.


    Any ideas?

  • Re: Time Delay?


    One way to accomplish this is to add a message box that tells the user of what just happened. While the message box is present the code is paused. Once the message box is dismissed by the user (by them clicking the OK button on the message box) the code will continue to run.


    Insert the following after the line in the code that paste's the data.


    msgbox ("Look, this is where the data has been pasted", vbok + vbinformation, "Just letting you know")


    Hope this helps.


    Best regards,


    Phil

  • Re: Time Delay? (solved) but another issue


    Thanks guys, both methods work brilliantly. Now my only problem is trying to make the user go to where I need them to! At the moment, my code looks like this:

    Code
    Sub PAYMENT1()
        Range((Range("P12").Value)) = Range("G12").Value
        Range((Range("R12").Value)) = Range("H12").Value
        Range((Range("T12").Value)) = Range("I12").Value
    
    
        Application.Wait Now + TimeSerial(0, 0, 5)
        Sheets("PAYING IN").Select
        Range("G12").Select
    End Sub


    So it is copying 3 cells to a location that I specify, what I can't now do is to go to where I want to for 5 seconds then return.


    I have tried both of the following:

    Code
    Application.Goto Reference:=(Range("P12").Value)


    and

    Code
    Range((Range("P12").Value)).Select


    Any ideas?

  • Re: Time Delay? (solved) but another issue


    You can select a range, but not it's value


  • Re: Time Delay?


    The first three lines aren't the problem, they all use the cell value as a reference which works fine but I can't then go to a cell that I specify which is a bit annoying!

  • Re: Time Delay?


    What happens with your code is that, taking the first line as an example, it makes P12 = the value in G12.


    What my code does is make the value in G12 = the cell (e.g."PAYMENTRECORD!D78") contained within P12 change, not P12 itself.


    What I am trying to do now is to show the user which cell got changed (e.g."PAYMENTRECORD!D78").

  • Go to a specified cell? (in a macro)


    In cell P12 I have a cell reference that I have generated, e.g. "PAYMENTRECORD!D78". However, I want to go to this cell reference in a macro.


    I have tried both of the following:

    Code
    Application.Goto Reference:=(Range("P12").Value)


    and

    Code
    Range((Range("P12").Value)).Select


    Any ideas?

  • Re: Go to a specified cell? (in a macro)


    If you are trying to select Range P12 then try this

    Code
    Range("P12").select
  • Re: Go to a specified cell? (in a macro)


    Sorry about the 2 threads thing, I just wasn't getting the right answer in my previous thread until you came along. Thanks again, it's all working smoothly now.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!