+ Reply to Thread
Results 1 to 16 of 16

Applying Progress Indicator into non-integer VBA

  1. #1
    Registered User
    Join Date
    12-17-2012
    Location
    Poland
    MS-Off Ver
    2013
    Posts
    57

    Applying Progress Indicator into non-integer VBA

    Hello,

    I'm trying to add a simple progress indicator to my code. I'm using instructions from this website. The only difference is instead of activating it via a CommandButton, I add another Sub called ShowForm which I add to my main code.

    The problem is, the instruction in the link shows how to create progress indicator based on numbers. The example code creates numbers in cells and the level of completion of these numbers serves as information for the progress bar what's the progress in %. Here's how it looks like:

    Please Login or Register  to view this content.
    pctCompl is i which in this example are numbers.

    My code does not calculate numbers. It copies data from another sheet, reformats cells (removes unnecessary blanks, adds columns etc.) and saves the formatted data into a new workbook.

    What should I assign to pctCompl so that I'm able to show progress?

  2. #2
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Applying Progress Indicator into non-integer VBA

    You would have to just assign it a number up to 100 at relevant points in your code, so after copying a sheet that, that maybe 25% of the way through the procedure so assign it a number of 25

  3. #3
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Applying Progress Indicator into non-integer VBA

    May be something like this...

    Please Login or Register  to view this content.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  4. #4
    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
    44,091

    Re: Applying Progress Indicator into non-integer VBA

    The basic premise of that progress indicator is that you have x loops to go through and you have completed y of them. Hence you can represent y as a percentage of x.

    In your case, you have x steps to go through and, as you complete each one, you can represent that as a percentage. Say 1 of 10, 2 of 10, etc ... which would show as 10%, 20%, etc.


    Regards, TMS
    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


  5. #5
    Registered User
    Join Date
    12-17-2012
    Location
    Poland
    MS-Off Ver
    2013
    Posts
    57

    Re: Applying Progress Indicator into non-integer VBA

    Ok, so the loop is actually necessary in my code as well? I thought it only served as an example, but could be substituted with whatever I fancy.

  6. #6
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Applying Progress Indicator into non-integer VBA

    No, you don't need the loop

  7. #7
    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
    44,091

    Re: Applying Progress Indicator into non-integer VBA

    Something like:

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    12-17-2012
    Location
    Poland
    MS-Off Ver
    2013
    Posts
    57

    Re: Applying Progress Indicator into non-integer VBA

    Thanks TMS, your example clarifies it perfectly.

    But is there a way of automating it? Like, this example requires manual steps determination. Is there a way of converting this so that the % is determined automatically? So instead of 'Step1', 'Step2', 'Step3' etc., we get Starting point at the beginning of the code and Ending point at the end, and % being calculated dynamically as the macro progresses?

  9. #9
    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
    44,091

    Re: Applying Progress Indicator into non-integer VBA

    Hard to say without seeing your code, and probably a sample workbook.

    You have cited a generic example of displaying a progress bar which is intended to show how far it is through a repetitive function/task. The objective is to indicate to the user that the process is running and so they have an idea how far it has got. That would typically be useful if the process took minutes or hours, or maybe even just many seconds.

    It sounds as though you have some distinct stages to your process. What are they? How long do they take, typically? Do any of them use a loop over many iterations? You might be better updating the Status Bar with progress information: step 1 of 5 - copying data


    Regards, TMS

  10. #10
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Applying Progress Indicator into non-integer VBA

    I would like to share an approach which I seen in a web file few years back.

    The user kept 4 text boxes.
    On open user loads a userform with a message saying LOADING...

    Set the Timer for 30 Sec, for every 6 seconds the user used code to enter 25% and color the text box, so others thinks that the file is loading slowly... but actually the file is already loaded and waiting to complete the code, so for every 6 seconds the user adds new % and color each text boxes.

  11. #11
    Registered User
    Join Date
    12-17-2012
    Location
    Poland
    MS-Off Ver
    2013
    Posts
    57

    Re: Applying Progress Indicator into non-integer VBA

    The stages info is actually a very interesting suggestion, TMS. It has never occured to me before, but it actually really might look better! I mean, my code already does what it's supposed to do, so now the Progress Indicator is basically just a bonus, something to "pimp" the workbook.

    Thanks for all the hints, everyone.

    Solving this thread.

  12. #12
    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
    44,091

    Re: Applying Progress Indicator into non-integer VBA

    You're welcome.

  13. #13
    Registered User
    Join Date
    12-17-2012
    Location
    Poland
    MS-Off Ver
    2013
    Posts
    57

    Re: Applying Progress Indicator into non-integer VBA

    Umm, just one last thing, though.

    I used a simple code to call a user form with specific text, like below:

    Please Login or Register  to view this content.
    The problem is now that until I don't close the UserForm2, the macro is interrupted... How do I tell VBA to continue working and keep displaying UserForm2?

  14. #14
    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
    44,091

    Re: Applying Progress Indicator into non-integer VBA

    Don't use a Form. Display messages in the Status Bar.

    http://www.cpearson.com/excel/statusbar.htm

  15. #15
    Registered User
    Join Date
    12-17-2012
    Location
    Poland
    MS-Off Ver
    2013
    Posts
    57

    Re: Applying Progress Indicator into non-integer VBA

    Thanks again, TMS!

  16. #16
    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
    44,091

    Re: Applying Progress Indicator into non-integer VBA

    Again, you're welcome.

    I think using the Status Bar is neater and more discreet ... and less hassle.

    Thanks for the rep.

+ 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. Progress Indicator Bar
    By abousetta in forum The Water Cooler
    Replies: 17
    Last Post: 10-01-2012, 03:34 AM
  2. Progress Bar Indicator
    By coneill0422 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-06-2011, 07:18 PM
  3. Macro Progress Indicator
    By crs245 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-26-2009, 02:37 PM
  4. Progress Indicator
    By excel_lover in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-20-2009, 04:59 AM
  5. [SOLVED] Help with Progress indicator script
    By Les Stout in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-25-2005, 08:05 AM

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