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

    Adding Status bar for Do.. Loop..

    Anyone know of a method to add a status bar to give the user progress on a do.. loop? the context is as follows:

    '*************CODE START**************
    Private Sub btnmaximize_click()
    Userform.Show
    Do
    Call userform.MoveNextButton_Click
    'find out how to add delay here to effect movenextclick.
    Loop While userform.counter.Value < Range("AC" & 1).Value
    End Sub

    ****************CODE END****************
    Now, the value of Range("AC" & 1) is a variable which increases as I 'Navigate' the form.

    Any help would be appreciated!!

    Thanks!
    <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
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Status bar for Do.. Loop..

    What about putting it in Excel's status bar (or am I missing something and you've already dismissed that). If I'm not missing something, the code is:

    Application.StatusBar="Text"

    To revert back to the normal values in the status bar, turn off your text with

    Application.StatusBar=False

    HTH

    Jon

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

    Re: Adding Status bar for Do.. Loop..

    Drk,

    Jon's answer has prompted me to ask where you wnat the status bar. Is in in the Excel status bar? Or is it on the userform somewhere?
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

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

    Re: Adding Status bar for Do.. Loop..

    VBA in MSAccess has a SysCmd method that can be used to display a progress bar in the status area. I do not think that Excel has this or a similar capability. If you are wanting to display a progress bar, then you will have to do it with your own code. Just define a string variable and initilize it to null. Then in the loop add the character you want to use for your progress meter to the end of the string and assign the string to the status bar. If the bar moves to rapidly, or gets too long, then use an If statement to only add a character and display every nth time through the loop.
    Legare Coleman

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

    Re: Adding Status bar for Do.. Loop..

    I had hoped to use an Excel Status bar. It all relates to this thread, which refers to a minimize button on a userform, I think the only way to do it, is to call 'btnmovenext_click' n times, n being the integer defined when the user clicks a 'minimize' button i've placed on the form. Because the form is being used for data entry, I have a variable 'i' which represents the current row. The row they left off on is captured in cell AC,1 on the worksheet, that number is the number of times 'btnmovenext_click' should be called. If there are 40 entries they left off on, I want them to move to the 40th row when they 'maximize' the form.

    Thanks!
    <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

  6. #6
    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: Adding Status bar for Do.. Loop..

    Could you add some code in the form's initialize event to set n to whatever value is in that cell?
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Adding Status bar for Do.. Loop..

    Sure,

    dim n as integer
    n = Range("AC" & 1).value

    what would that do? Another problem i'm having, the do.. loop.. will move VERY quickly, I need to delay it, any ideas?
    <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

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

    Re: Adding Status bar for Do.. Loop..

    OIC....

    Hmm.. check this out.. Because the entire 'navigation' portion is based on the value i, Example: Range("A" & i).value=textbox1.value, perhaps I can make i the value of cell AC,1. would that work!?? hmmm.....
    <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

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

    Re: Adding Status bar for Do.. Loop..

    Okay,
    when the form initializes, i am setting i=2, to start on the second row on the worksheet. Can anyone give me an idea for a condition I can specify? I'm thinking something like:

    Sub btnmini_click()
    dim n as integer
    n=2
    Range("AC" & 1).Value = counter.Value
    frmtrack.Hide
    Application.WindowState = xlMinimized
    end sub

    Sub btnmax_click()
    if n=2 then i = Range("AC" & i).value
    else
    i=2
    end sub

    but it's not working.. any ideas?
    <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

  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: Adding Status bar for Do.. Loop..

    Hi,
    n is declared within the first sub, so loses its value when that sub ends. Try declaring it publicly and see if that works.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Adding Status bar for Do.. Loop..

    Nope, no go, I even tried recording 'n' to a cell in the worksheet, and basing an 'if' statement on the result, to no avail...

    no matter where 'n' is placed, be it public or not, the value assigned is reset when the form is hidden.
    <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

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

    Re: Adding Status bar for Do.. Loop..

    Syntax, Syntax, Syntax... for the record, VBA doesn't like the syntax of:

    Range("AC", 1).value it has to be Range("AC" & 1).Value

    My problem is solved now, but in the interests of completing the thread, does anyone know how to add a status bar for a do.. Loop.. ?

    Thanks All!
    <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

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

    Re: Adding Status bar for Do.. Loop..

    What do you mean by "status bar"? If you mean a progress bar (a bar that increases in length as the progress of the loop progresses), then my message in this thread tells you how to do it. If you mean something else, what do you mean?
    Legare Coleman

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

    Re: Adding Status bar for Do.. Loop..

    Err.. Progress Bar.. and so it does!
    Thanks Legare!
    <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

Posting Permissions

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