Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Progress Window (2000)

    Thanks to all for your help, notably, Legare and Andrew. I am two weeks ahead of schedule on this project thanks to you guys. I'm learning visual basic as I go along. At any rate,

    I'm at a stage where i need to generate some sheets that take a while. With screen updating off, it looks like the system has hung while it generates. I want to show a status/progress screen while the macro is operating. I've tried Showing a userform, but the macro does not process until the dialog box is closed.

    Also, using InputBox, how do I run a macro when "cancel" is clicked?

    Thanks again, all.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Progress Window (2000)

    You can try <A target="_blank" HREF=http://j-walk.com/ss/excel/tips/tip34.htm>j-walk.com</A>, where a method of implementing a progress bar is shown, and a sample workbook is available for download.

    There are 2 distinct implementations of an InputBox for Excel, the VBA variety and the Excel variety (Application.InputBox), which is more versatile. The former returns an empty string if Cancel is clicked (the same result as the OK button with no input). The latter variety returns 0 (false) which is also the value when the OK button is clicked without any input. So depending on what you are doing, you might be better using a UserForm.

    Andrew C

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

    Re: Progress Window (2000)

    One simple way to show status for a long operation is to use the Excel status bar. You can display a message in the status bar that shows what is happening and how it is progressing. Something like:

    <pre> Application.StatusBar = "Building sheet 1."
    </pre>


    Use something like this to run a macro when cancel is pressed on an input box:

    <pre>Dim vResult as variant
    vResult = InputBox ("Enter value")
    If vResult = False Then
    Call MyMacro
    End If
    </pre>

    Legare Coleman

  4. #4
    Star Lounger
    Join Date
    Aug 2001
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Progress Window (2000)

    That's it, Legare. I didn't know you you could use the return value of an InputBox as a string and a boolean at the same time. Hmm.

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

    Re: Progress Window (2000)

    That's why it has to be defined as a variant.
    Legare Coleman

  6. #6
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Progress Window (2000)

    I've got a few spreadsheet that do some complicated stuff and need some form or 'Don't worry, I'm still working' note to the user. You can create a progress form and display it modeless (or is it modal?). Use something like this ...

    frmProgress.Show 0

    and the code keeps progressing. Then in your code you can update the frmprogress with something like this ...

    frmProgress.lblProgress.Caption = "message"

    You might need a repaint commant too. You can also do this in excel 97 - there was an article in the knowledge base that showed how do do it but i've forgotten the number and cannot find it now <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>

    Cheers,

    Tim
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  7. #7
    Star Lounger
    Join Date
    Aug 2001
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Progress Window (2000)

    Tim

    The Show 0 thing works like a charm. Thanks for ALL your help. My supervisors are impressed that I started this project not knowing VB (only C++) and now it's looking pretty slick in about 2 weeks time. I owe it all to everybody here.

Posting Permissions

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