MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

PRogress bar in Excel

  Asked By: Holly    Date: Oct 11    Category: MS Office    Views: 1377

i have a macro in my excel file.when i run this macro it takes much
time to process data as near 5000 rows are being processed.

can i display progress bar while this macro is in running mode? I want
that the progress bar should display while this macro is in running mode.

Following is sample structure of my code:

sub processData()

rowmax = activesheet.usedrange.rows.count

for i = 1 to rowmax
' my functionality
end sub

what can i do to display progress bar and where i put progress bar
code in my code?



4 Answers Found

Answer #1    Answered By: Balbir Kaur     Answered On: Oct 11

In the Files Area of this group is a progress  bar DEMO Workbook. It
is in the folder named "Code Sample Files". It should have sufficient
comments to adapt to your use.

Answer #2    Answered By: Rene Sullivan     Answered On: Oct 11

Although this is not a solution for your Progress Bar question:
when I am up against a large data  processing 'job', I will typically turn off
the screen updating & auto calculation options.

sub processData( )

Application.ScreenUpdating = False
Application.Calculation = xlManual

rowmax = activesheet. usedrange. rows.count

for i = 1 to rowmax
' my functionality

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

end sub

Answer #3    Answered By: Milton Robinson     Answered On: Oct 11

I've used, after his suggested adaptations, Steve's version of the
progress bar, which he mentioned and it worked great for me.
Thought I'd take this opportunity to thank him for it.

Answer #4    Answered By: Vinit Online     Answered On: Oct 11

I'm pleased it works well for you.
I got the original code from the web, but the author, as well as
several others, insisted you needed to duplicate the code in every sub
needing it. That sounded short-sighted to me, so I figured out a way
to need only one instance.

Didn't find what you were looking for? Find more on PRogress bar in Excel Or get search suggestion and latest updates.