+ Reply to Thread
Results 1 to 19 of 19

how to make progress bar for an overall code

  1. #1
    Registered User
    Join Date
    10-11-2012
    Location
    denmark
    MS-Off Ver
    Excel 2007
    Posts
    27

    how to make progress bar for an overall code

    Hey

    I have made an overall code, that is calling other codes:

    Sub mOpdater_NA()

    Application.ScreenUpdating = False

    Call mCopyNA
    Call mHent_NaData
    Call mCopyDBNAdata
    Call mTime

    Range("AC2").Select

    Application.ScreenUpdating = True

    End Sub


    How can I make a progress bar that tells me how far my codes are?

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

    Re: how to make progress bar for an overall code

    You've got a couple of options.

    For a proper progress bar, you need a loop. You don't appear to have one, so you can either increment the bar by 25% after each sub, or if one takes longer than the others increment that sub by more

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

    Re: how to make progress bar for an overall code

    P.S A progress bar will slow your code down even further, so you're much better off spending your time speeding your other macros up rather than doing a progress bar

  4. #4
    Valued Forum Contributor Parth007's Avatar
    Join Date
    12-01-2014
    Location
    Banglore
    MS-Off Ver
    2010
    Posts
    879

    Re: how to make progress bar for an overall code

    Please use code tags while posting codes..... press # & then place your code withing..


    Please Login or Register  to view this content.
    Regards
    Parth

    I appreciate your feedback. Hit * if u Like.
    Rules - http://www.excelforum.com/forum-rule...rum-rules.html

  5. #5
    Valued Forum Contributor Parth007's Avatar
    Join Date
    12-01-2014
    Location
    Banglore
    MS-Off Ver
    2010
    Posts
    879

    Re: how to make progress bar for an overall code

    Try this..

    http://www.excel-easy.com/vba/exampl...indicator.html


    A sample code.. you just modify the inter code according to your code..

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    10-11-2012
    Location
    denmark
    MS-Off Ver
    Excel 2007
    Posts
    27

    Re: how to make progress bar for an overall code

    Hey Kyle123

    Thanks for your reply.

    The code take 13 sec to run and I don't have any loops.

    I will like to use the 25% increment bar, as you mentioned. Do you know how I can impliment it in my code?

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

    Re: how to make progress bar for an overall code

    Post your code, if it takes that long to run, chances are you're doing something really inefficient

  8. #8
    Valued Forum Contributor
    Join Date
    03-24-2014
    Location
    England
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    575

    Re: how to make progress bar for an overall code

    Create a user form with a label box that is almost the entire width of the userform, make it's background colour something that stands out. Take note of it's width in properties.

    In your code set the width of the label box to 1 and show the userform with the vbmodeless switch so your code doesn't pause.

    Please Login or Register  to view this content.
    If you have a loop in your macro you can use the following to cause the progress bar to smoothly extend across the userform:

    Please Login or Register  to view this content.
    where 500 is the max width of the labelbox.

    Without a loop you can still make the bar move:

    Please Login or Register  to view this content.

  9. #9
    Valued Forum Contributor
    Join Date
    03-24-2014
    Location
    England
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    575

    Re: how to make progress bar for an overall code

    And Kyle, mate.....with the greatest respect, if you don't know what the 4 macros being run actually do you simply can't make statements like 'if it takes 13 seconds to run you must be doing something really inefficient'.

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

    Re: how to make progress bar for an overall code

    I didn't say that though did I?

  11. #11
    Registered User
    Join Date
    10-11-2012
    Location
    denmark
    MS-Off Ver
    Excel 2007
    Posts
    27

    Re: how to make progress bar for an overall code

    I can't show the codes, sorry.

    BellyGas, should I at this in the end of my code?

    userform1.label1.width = 25
    userform1.repaint

    userform1.label1.width = 50
    userform1.repaint

    etc etc

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

    Re: how to make progress bar for an overall code

    No, put it between each of the subs that you call

  13. #13
    Valued Forum Contributor
    Join Date
    03-24-2014
    Location
    England
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    575

    Re: how to make progress bar for an overall code

    Quote Originally Posted by Kyle123 View Post
    if it takes that long to run, chances are you're doing something really inefficient
    Yeah ya did.

  14. #14
    Registered User
    Join Date
    10-11-2012
    Location
    denmark
    MS-Off Ver
    Excel 2007
    Posts
    27

    Re: how to make progress bar for an overall code

    Okay, thank you

    So they code will look like this ?

    Sub mOpdater_NA()

    Application.ScreenUpdating = False

    Userform1.show

    Call mCopyNA

    userform1.label1.width = 25
    userform1.repaint

    userform1.label1.width = 50
    userform1.repaint

    Call mHent_NaData

    userform1.label1.width = 25
    userform1.repaint

    userform1.label1.width = 50
    userform1.repaint

    Call mCopyDBNAdata

    ect.

    Call mTime

    ect.

    Range("AC2").Select

    Application.ScreenUpdating = True

    unload Userform1

    End Sub

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

    Re: how to make progress bar for an overall code

    That's not what you wrote is it? - There's a world of difference between:

    Quote Originally Posted by BellyGas View Post
    if it takes 13 seconds to run you must be doing something really inefficient
    and
    Quote Originally Posted by Kyle
    if it takes that long to run, chances are you're doing something really inefficient
    And with complete frankness, I can count on one hand the number of legitimate things one could do to make Excel take that long to run a macro without a loop, so chances are...

    Fix the problem, not sooth the symptoms

  16. #16
    Valued Forum Contributor
    Join Date
    03-24-2014
    Location
    England
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    575

    Re: how to make progress bar for an overall code

    Say, for example, one of those macros being run is to insert 250,000 rows of data into an Access DB over a network via SQL, row by row. Bet you a quid it takes longer than 13 seconds and looping won't improve it.

    If you don't know what those 4 macros do, you simply cannot make statements about it needing improvement.

    And Bech, no:

    Please Login or Register  to view this content.
    etc, etc. What you're doing is making the label box bigger after each macro runs and repainting the form. At the end put

    Please Login or Register  to view this content.
    to get rid of the status bar.

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

    Re: how to make progress bar for an overall code

    If you don't know what those 4 macros do, you simply cannot make statements about it needing improvement.
    And that's precisely the difference between what I said and what you read I said. They aren't the same thing.

  18. #18
    Valued Forum Contributor
    Join Date
    03-24-2014
    Location
    England
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    575

    Re: how to make progress bar for an overall code

    Tell ya what, you have a nice day and lets get back to solving this fellas problem.

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

    Re: how to make progress bar for an overall code

    Indeed, which is what I was offering to do

+ 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. [SOLVED] How to make vertical progress bar?
    By iqballud in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-05-2015, 07:29 PM
  2. Bar chart or bar graph for construction progress physical progress
    By pvsvprasadcivil in forum Excel Charting & Pivots
    Replies: 12
    Last Post: 01-08-2014, 02:40 AM
  3. Bar chart or bar graph for construction progress physical progress
    By pvsvprasadcivil in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-29-2013, 12:41 PM
  4. Replies: 1
    Last Post: 09-24-2013, 11:00 AM
  5. how can i increase the progress bar value based on the code?
    By anilg0001 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-27-2013, 02:40 AM
  6. VBA code to update popup progress bar
    By Dan Ahmadi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-10-2010, 01:18 PM
  7. adding a progress bar to vba code
    By losmi8 in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 02-21-2010, 03:00 AM
  8. Code not allowing to view progress
    By F6Hawk in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-29-2008, 07:55 AM

Tags for this Thread

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