how do you set up a progress bar when running a macro in excel?

I have written a macro which takes a minute or so to run. I want to set up a
progress bar so the user can see something happening and knows the program
hasn't crashed. How do I do this?

I was about to ask almost the same question (and this was the top post)

I want to display some type of message as the macro starts... then when its
done, have the message go away. something like "Please wait, Macro running".

The progress indicator would be nice to have as well...


"Neil Hopkinson" wrote:

> I have written a macro which takes a minute or so to run. I want to set up a
> progress bar so the user can see something happening and knows the program
> hasn't crashed. How do I do this?

statusbar="running"
code
statusbar=""

--
Don Guillett
SalesAid Software
[email protected]
"Neil Hopkinson" <Neil [email protected]> wrote in message
news:[email protected]...
> I have written a macro which takes a minute or so to run. I want to set up
a
> progress bar so the user can see something happening and knows the program
> hasn't crashed. How do I do this?

For a few different options see
http://www.mrexcel.com/board2/viewto...?p=88129#88129

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>, "=?
Utf-8?B?TmVpbCBIb3BraW5zb24=?=" <Neil
[email protected]> says...
> I have written a macro which takes a minute or so to run. I want to set up a
> progress bar so the user can see something happening and knows the program
> hasn't crashed. How do I do this?
>

Not Everyone may have the status bar visible, so you might want to try
putting some text in a cell and then removing it -
Sheet1.Range("A2").Value = "Calculating...."
Application.ScreenUpdating = False
Some code
Sheet1.Range("A2").Value = ""
Application.ScreenUpdating = True

Mike F
"Don Guillett" <[email protected]> wrote in message
news:[email protected]...
> statusbar="running"
> code
> statusbar=""
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Neil Hopkinson" <Neil [email protected]> wrote in
> message
> news:[email protected]...
>> I have written a macro which takes a minute or so to run. I want to set
>> up
> a
>> progress bar so the user can see something happening and knows the
>> program
>> hasn't crashed. How do I do this?
>
>

A lot safer than changing a cell's content would be to show the status
bar if appropriate.

Changing a cell requires (at a minimum) the following assumptions:

The cell is in the visible portion of a window.

That portion of the window is not obscured by another window

The cell is empty

Changing that cell will not trigger any other cascading changes

Changing the workbook's status to 'dirty' is acceptable

Neither the cell's row and column is hidden.

The cell's height and width are sufficiently large to show the progress
status

The cell's format (font size, color, pattern color, etc.) allow for a
visible progress status.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>,
[email protected] says...
> Not Everyone may have the status bar visible, so you might want to try
> putting some text in a cell and then removing it -
> Sheet1.Range("A2").Value = "Calculating...."
> Application.ScreenUpdating = False
> Some code
> Sheet1.Range("A2").Value = ""
> Application.ScreenUpdating = True
>
> Mike F
> "Don Guillett" <[email protected]> wrote in message
> news:[email protected]...
> > statusbar="running"
> > code
> > statusbar=""
> >
> > --
> > Don Guillett
> > SalesAid Software
> > [email protected]
> > "Neil Hopkinson" <Neil [email protected]> wrote in
> > message
> > news:[email protected]...
> >> I have written a macro which takes a minute or so to run. I want to set
> >> up
> > a
> >> progress bar so the user can see something happening and knows the
> >> program
> >> hasn't crashed. How do I do this?
> >
> >
>
>
>

Hi Neil,

a few more site that might be helpfull:
http://j-walk.com/ss/excel/tips/tip34.htm
http://www.*****-blog.com/archives/2.../progress-bar/

a nice example:
http://www.oaltd.co.uk/DLCount/DLCou...e=WksPrgrs.zip

hope u can use this!!

"Neil Hopkinson" <Neil [email protected]> wrote in message
news:[email protected]...
>I have written a macro which takes a minute or so to run. I want to set up
>a
> progress bar so the user can see something happening and knows the program
> hasn't crashed. How do I do this?

Thankyou to everyone for your help. Much Appreciated

"Neil Hopkinson" <Neil [email protected]> wrote in message
news:[email protected]...
> I have written a macro which takes a minute or so to run. I want to set up
a
> progress bar so the user can see something happening and knows the program
> hasn't crashed. How do I do this?

You have to use the CreateWindowEx API function if you want to create a real
progress bar. Here's an article that may already have been submitted
http://www.*****-blog.com/archives/2.../progress-bar/

/Fredrik