Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Do...Loop... Lockout

    I have some do... loop... code in my project which elapses the time on a worksheet. The problem is, when this code is running, it doesn't allow for any other actions to be taken on the form. I.E. Unable to enter information within form, even stopping the do... loop... via command button is difficult. Any ideas?

    Thanks!
    Drk
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Do...Loop... Lockout

    Drk,

    I assume this relates to this thread, and in particular code posted by Rory. It can get a bit confusing for people to follow two open threads on the same subject- but I
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Do...Loop... Lockout

    I guess that's the drawback of having just bought a new PC! It worked fine on mine too!
    I like the tweak to slow the loop - should do the trick hopefully.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Do...Loop... Lockout

    Thanks gwhitfield,

    I attempted to tweak my code in the same manner, and am still unable to make any changes to my form. I'm not sure if this is a system limitation, but i'm going around it for now by not displaying the time as it elapses. I even tried increasing the refresh delay to "5", to no avail, it still 'locks' up the loop.

    Thanks again!
    Drk
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  5. #5
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Do...Loop... Lockout

    Drk,

    I sort of suspected it might, but was hoping it wouldn't. I had a look at the system resources when my loop was running, and it was 100%- back to zero when the loop stopped.

    The VB timer is nice. Maybe that could do something. I don't know where it is though- it would be nice if it's available in an ocx or dll which you could link into your VBA project.

    I had a look at "Application.OnTime" to initiate something, but couldn't get anything working. I don't know whether that might do soemthing without consuming resources.

    What sort of system are you running on?
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Do...Loop... Lockout

    According to our IT Dept, it's a Pentium II 450mhz with 64k of memory, standard workstation-type terminal.

    our network requires the loading of a lot of 'junk' to make it work, so this loop is really bogging down the already stressed resources.. this thread has to do with Excel Resources, sorry.. [img]/w3timages/icons/blush.gif[/img]
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Do...Loop... Lockout

    As I replied to this same question in the other thread, I think the only way to do what you want is to use the Ontime method. I don't know how Rory got this to work, but I think that any time VBA code is running, everything else on the same workbook should be locked out. Otherwise, you could have all kinds of wierd problems caused by the user and the code using/modifying the same thing at the same time. Again, I do not have any sample code to show how to do this. Basically, what you do is, in the routine where you start the timeer, you use Ontime to schedule another routine to run at the time when you want the elapsed time updated (one or two seconds from the current time). In this routine, you calculate the elapsed time and put it in the text box and then use Ontime to schedule the routine to run again when you want the elapsed time updated next. In the routine to stop the timer, you cancle the last Ontime event you scheduled.
    Legare Coleman

  8. #8
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Do...Loop... Lockout

    Legare,

    I tries playing around with the "Ontime" and could not get it working.

    Do you have a (simple) sample of code where you've got it working please?

    Thanks
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Do...Loop... Lockout

    Geoff: No, I don't currently have any sample code that I can just copy and paste, and I really don't have enough time available before the weekend to write and debug a sample. If you will take what you wrote and compare it withe the description I gave of the process in my last message, and if you still can't get it to work post it here and I will try to help you with it.
    Legare Coleman

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Do...Loop... Lockout

    Drk,
    Try this:
    In the form's code module, you need the following (assuming cmdRun is your command button and txtTimeElapsed is your text box):
    Public dblStarted As Double, dblStopped As Double, dblScheduled As Double
    Sub cmdRun_Click()
    Static blnStarted As Boolean
    If blnStarted = False Then
    dblScheduled = Now + TimeValue("00:00:01")
    RunOnTime
    dblStarted = Now
    With Me
    .cmdRun.Caption = "Stop"
    .txtTimeElapsed = Format(Now - dblStarted, "hh:mm:ss")
    End With
    Else
    dblStopped = Now
    With Me
    .cmdRun.Caption = "Start"
    .txtTimeElapsed = Format(dblStopped - dblStarted, "hh:mm:ss")
    End With
    Application.OnTime dblScheduled, "TimeUpdate", , False
    End If
    blnStarted = Not blnStarted
    End Sub
    Sub RunOnTime()
    Application.OnTime dblScheduled, "TimeUpdate"
    End Sub

    Then in a separate module add this (it should be possible to have it in the form's module but I haven't figured out how yet!):
    Public Sub TimeUpdate()
    With frmTimer
    .txtTimeElapsed = Format(Now - .dblStarted, "hh:mm:ss")
    .dblScheduled = .dblScheduled + TimeValue("00:00:01")
    .RunOnTime
    End With
    End Sub

    This works on my PC at work (500MHz PIII) so hopefully will work on yours.
    Let me know if it doesn't and I'll try again![img]/w3timages/icons/thinks.gif[/img]
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Do...Loop... Lockout

    Rory: That looks like the right idea!
    Legare Coleman

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Do...Loop... Lockout

    Legare,
    Good thinking on your part![img]/w3timages/icons/smile.gif[/img] Any idea why I couldn't get it to work with the UpdateTimer routine in the form's module? (I suspect it may have to do with what string is passed to the OnTime method as the macro name, but I could not get it to work)
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Do...Loop... Lockout

    Without testing, I am not sure why the routine would not work in the form code area (technically it is not a module). My guess would be that the only thing you can have there are event routines. I would guess further that when you call a subroutine, VBA will only look for it in a real module, not in the form code area. I normally put all of my code in modules and nothing more than a call in the event procedure.
    Legare Coleman

  14. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Do...Loop... Lockout

    Personally, I'd have said it was a class module (much like the ones associated with ThisWorkbook and each worksheet) - would you not agree?
    Either way, I can call the RunOnTime routine within it but I can't get the OnTime method to schedule the UpdateTimer routine, which confuses me. I guess I'll just have to ponder it some more...[img]/w3timages/icons/thinks.gif[/img]
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #15
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Do...Loop... Lockout

    Well, it looks like the Ontime method works from the form code area to cancel the last scheduled timer, so I don't know of any reason it should not be able to schedule one. Just moving the routine to a module should not change the context which it is running in, just where it is located. If the form code area is some kind of class module, then routines located there should run in the class mocule context, and so should any routine called by them. If you actually get to the routine whenk, but it just does not schedule the timer, I don't have a clue why.
    Legare Coleman

Page 1 of 3 123 LastLast

Posting Permissions

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