1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Stopping and resuming a macro

Discussion in 'Business Applications' started by PincivMa, Feb 15, 2005.

Thread Status:
Not open for further replies.
Advertisement
  1. PincivMa

    PincivMa Thread Starter

    Joined:
    Mar 13, 2004
    Messages:
    378
    Hi Again

    Is it possible to execute a macro (I usually execue it by clicking on a button), stop the macro at a specified spot, while the active sheet is still activated, do any action you want on the worksheet (For example, do a copy and paste, or whatever) and then re-click on the button to resume the macro from the speicified spot to the end?

    Mario
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Why not just break the macro into two seperate macros and just call the second macro with its own button click? The only other way I know how to stop the code and then make changes is to go into Debug mode by pressing CTRL + BREAK. After making your changes you can resume the code by setting focus back to the VB editor window and pressing F5. You could put a message box where you want the code to stop and then when it pops up you can enter Debug mode from there. Hope this helps a little.

    Rollin
     
  3. PincivMa

    PincivMa Thread Starter

    Joined:
    Mar 13, 2004
    Messages:
    378
    Hi Rolling_Again

    I thought of breaking the macro into 2. The reason why I did not go that route is that at present I have over 100 buttons that execute over 100 macros on one spreadsheet. If I break the macro into two, I would have over 200 buttons. I thought that if I could combine 2 macros into one with a break, since I want to do a copy paste, I could save myself time by not creating 100 other macro buttons.

    But if that is not possible, I guess I have to take the long route.

    Thanks,

    Mario
     
  4. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    You didn't say if it was WORD or EXCEL.
    If you are in EXCEL this might be of interest. Now this may be a bit hokey :eek: but I used it a while back for a spreadsheet where we wanted one macros - two operations - with a break in between.


    First the macro looks for a zero value in a cell - then executes the first part of our macro

    Range("A1").Select
    If ActiveCell = "0" Then

    Sheets("Sheet2").Select
    Range("B3").Select
    Application.Display.....
    ...blah blah blah

    ...Then, when it is finished with part one, it returns to that cell to change the value to (1)


    Sheets("Sheet1").Select
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("A2").Select

    You then do what it is you do and when you hit the MACRO button a second time - because it doesn't find a zero, it then does the second part of the macro.
    Else

    Sheets("Sheet3").Select
    Range("A5").Select ...
    ....blah blah blah


    and finishes by going back to change the cell value to zero again.

    Sheets("Sheet1").Select
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("A2").Select

    End If

    End Sub


    And of course the cell can be anywhere and hidden or white font or whatever so it doesn't appear.

    At the time I was in a rush (like it's ever any other way!) and it was the first thing out of my brain - but it got the job done. We could make our additions to the sheet and hit the button again and it did the second half of the operation.

    I am sure there are better ways of doing it but if nothing else this might give you some other ideas to pursue.

    - Castleheart :cool:
     
  5. PincivMa

    PincivMa Thread Starter

    Joined:
    Mar 13, 2004
    Messages:
    378
    Hi CastleHeart

    You are a geneous!! This is indeed a clever trick. I will certainly use it.

    Thank you. You saved me a ton of work.

    Mario
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/330854

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice