+ Reply to Thread
Results 1 to 12 of 12

Stop timer, Do While Loop from executing.

  1. #1
    Registered User
    Join Date
    07-31-2016
    Location
    Holland
    MS-Off Ver
    2019
    Posts
    74

    Stop timer, Do While Loop from executing.

    Hello, I have the following code to start a timer in a userform. All is working except for commandbutton1, build in to stop the timer to execute the closing of the sheet. Is there a way to stop, exit the do while loop by pressing commandbutton1 ?

    Please Login or Register  to view this content.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    49,445

    Re: Stop timer, Do While Loop from executing.

    You have a couple of references to Time. Should they be Timer? Pretty sure Time is the System time.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    07-31-2016
    Location
    Holland
    MS-Off Ver
    2019
    Posts
    74

    Re: Stop timer, Do While Loop from executing.

    The code in the workbook is
    Please Login or Register  to view this content.
    the code in the module is.
    Please Login or Register  to view this content.
    Used to close the workbook if not used. when the workbook is not used for 5 minutes the userform1 will show with a countdown of 20 seconds. After 20 seconds the workbook will close. by clicking commandbutton1 in userform1 I want to be able to stop the 20 countdown, loop so the workbook will not be close by the macro.
    Last edited by Wijnand1; 02-26-2017 at 06:23 AM.

  4. #4
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: Stop timer, Do While Loop from executing.

    I haven't tested your basic SetTimer & StopTimer procedures so am assuming they work as expected, have also ignored the potential conflict in the UserForm Activate event (see comments).

    The following might work but note the userform MUST have it's ShowModal property set to False for this to function correctly.

    THe entire code for the userform:
    Please Login or Register  to view this content.
    Last edited by cytop; 02-26-2017 at 07:26 AM.

  5. #5
    Registered User
    Join Date
    07-31-2016
    Location
    Holland
    MS-Off Ver
    2019
    Posts
    74

    Re: Stop timer, Do While Loop from executing.

    The code is not working, when pressing commandbutton1 there is no action, when the counter is down to "0" the workbook closes. But commandbutton1 does not cancel the loop.

  6. #6
    Registered User
    Join Date
    07-31-2016
    Location
    Holland
    MS-Off Ver
    2019
    Posts
    74

    Re: Stop timer, Do While Loop from executing.

    Thinking now, maybe it isn't even possible to cancel, exit this loop? And maybe I need another approach?

  7. #7
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: Stop timer, Do While Loop from executing.

    maybe it isn't even possible to cancel,
    OK. But why would anyone would go to the bother of writing code and testing it? It's disappointing you will simply discount an effort to help. Incidentally, it does work.

  8. #8
    Registered User
    Join Date
    07-31-2016
    Location
    Holland
    MS-Off Ver
    2019
    Posts
    74

    Re: Stop timer, Do While Loop from executing.

    Think a had a fault I the userform, so it think it is working. Only when I use commandbutton1 VBA gives a fault in
    Please Login or Register  to view this content.
    in UserForm1Show.

    Object variable or With block variable not set (Error 91)
    Last edited by Wijnand1; 02-26-2017 at 08:56 AM.

  9. #9
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: Stop timer, Do While Loop from executing.

    Because of the circumstances described in the UserForm_Initialize event in post #4. As I mentioned, "haven't tested your basic procedures so am assuming they work as expected".

    This attached works (or works as I understand what you wanted to do).
    Attached Files Attached Files
    Last edited by cytop; 02-26-2017 at 01:16 PM.

  10. #10
    Registered User
    Join Date
    07-31-2016
    Location
    Holland
    MS-Off Ver
    2019
    Posts
    74

    Re: Stop timer, Do While Loop from executing.

    Many Thanks for your time to make this excel. When i open the workbook i get a Run-time error 1004 Method 'OnTime' of object'_Application' failed

  11. #11
    Registered User
    Join Date
    07-31-2016
    Location
    Holland
    MS-Off Ver
    2019
    Posts
    74

    Re: Stop timer, Do While Loop from executing.

    1.jpg

    This is the basic Idea.
    Last edited by Wijnand1; 02-27-2017 at 03:17 PM.

  12. #12
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: Stop timer, Do While Loop from executing.

    Sorry, but it runs perfectly fine for me. Waits for 30 seconds of inactivity, then displays a userform with a button to cancel the countdown. If the button is not clicked within 20 seconds the workbook is unloaded.

    However, I notice your Office version is 2011. I don't have a Mac (and refuse to buy any Apple product) so I cannot test this in your environment - but, for what it's worth, it works on Windows.
    Last edited by cytop; 02-28-2017 at 03:56 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. start stop timer
    By melody10 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-16-2017, 08:52 AM
  2. [SOLVED] VBA countdown timer help, need it to stop.
    By crowmagnus in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-10-2017, 11:27 AM
  3. [SOLVED] How do i stop a timer
    By ANDREAAS in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-12-2016, 01:12 PM
  4. Stop timer
    By aprildu in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-23-2016, 05:36 PM
  5. Start/Stop timer
    By JonathanB2 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-10-2014, 07:00 AM
  6. [SOLVED] Timer non-stop to run?
    By alee001 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-30-2013, 03:46 PM
  7. Replies: 1
    Last Post: 12-12-2012, 08:46 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1