Status bar (or similar) to appear whilst running a long macro
 

I have a macro which takes about one minute to run and switches around
between sheets etc. I was wondering if it is possible for a status bar or
status page to appear on the screen whilst the macro runs in the background.
It's just a visual thing but I think it would appear a lot more tidy for the
user.

Is this possible?

Status bar (or similar) to appear whilst running a long macro
 

Hi,

You can use Application.Status e.g.:

Application.Status = "Please wait .. working ... "


--
Best regards,
Peter Jaušovec
http://blog.jausovec.net
http://office.jausovec.net


"Ant" wrote:

I have a macro which takes about one minute to run and switches around
between sheets etc. I was wondering if it is possible for a status bar or
status page to appear on the screen whilst the macro runs in the background.
It's just a visual thing but I think it would appear a lot more tidy for the
user.

Is this possible?

Status bar (or similar) to appear whilst running a long macro
 

You could add:

option explicit
sub yoursub()
application.screenupdating = false
'your code
application.screenupdating = true
end sub

to stop the flickering.

And pepper this kind of thing where you want:

application.statusbar = "Please wait. I'm sorting data!"

and remember to reset the statusbar before your code ends:

application.statusbar = false



Ant wrote:

I have a macro which takes about one minute to run and switches around
between sheets etc. I was wondering if it is possible for a status bar or
status page to appear on the screen whilst the macro runs in the background.
It's just a visual thing but I think it would appear a lot more tidy for the
user.

Is this possible?

--

Dave Peterson

Status bar (or similar) to appear whilst running a long macro
 

Thanks Peter but I was actually wanting to know if there is something that
appears on screen that completely hides the macro that is working in the
background. Perhaps some code which keeps "Sheet1" only visible whilst the
macro is working on other sheets. It is so the users can not see the macro
physically working.

Does that make sense?

"Peter Jausovec" wrote:

Hi,

You can use Application.Status e.g.:

Application.Status = "Please wait .. working ... "


--
Best regards,
Peter Jaušovec
http://blog.jausovec.net
http://office.jausovec.net


"Ant" wrote:

I have a macro which takes about one minute to run and switches around
between sheets etc. I was wondering if it is possible for a status bar or
status page to appear on the screen whilst the macro runs in the background.
It's just a visual thing but I think it would appear a lot more tidy for the
user.

Is this possible?

Status bar (or similar) to appear whilst running a long macro
 

Dave, I don't know if your saw my reply to Peter (I don't think I explained
myself very well actually!). I was wanting to know if there is something
that
appears on screen that completely hides the macro that is working in the
background. Perhaps some code which keeps "Sheet1" only visible whilst the
macro is working on other sheets. It is so the users can not see the macro
physically working. In other words, I have a macro button on a front sheet
which than does a whole lot of work on various sheets within the file. It
would be nice if the front sheet remained the only visible sheet whilst the
macro performed it's operations in the background. Is this possible?


"Dave Peterson" wrote:

You could add:

option explicit
sub yoursub()
application.screenupdating = false
'your code
application.screenupdating = true
end sub

to stop the flickering.

And pepper this kind of thing where you want:

application.statusbar = "Please wait. I'm sorting data!"

and remember to reset the statusbar before your code ends:

application.statusbar = false



Ant wrote:

I have a macro which takes about one minute to run and switches around
between sheets etc. I was wondering if it is possible for a status bar or
status page to appear on the screen whilst the macro runs in the background.
It's just a visual thing but I think it would appear a lot more tidy for the
user.

Is this possible?

--

Dave Peterson

Status bar (or similar) to appear whilst running a long macro
 

Did you try the screenupdating stuff?

Ant wrote:

Dave, I don't know if your saw my reply to Peter (I don't think I explained
myself very well actually!). I was wanting to know if there is something
that
appears on screen that completely hides the macro that is working in the
background. Perhaps some code which keeps "Sheet1" only visible whilst the
macro is working on other sheets. It is so the users can not see the macro
physically working. In other words, I have a macro button on a front sheet
which than does a whole lot of work on various sheets within the file. It
would be nice if the front sheet remained the only visible sheet whilst the
macro performed it's operations in the background. Is this possible?

"Dave Peterson" wrote:

You could add:

option explicit
sub yoursub()
application.screenupdating = false
'your code
application.screenupdating = true
end sub

to stop the flickering.

And pepper this kind of thing where you want:

application.statusbar = "Please wait. I'm sorting data!"

and remember to reset the statusbar before your code ends:

application.statusbar = false



Ant wrote:

I have a macro which takes about one minute to run and switches around
between sheets etc. I was wondering if it is possible for a status bar or
status page to appear on the screen whilst the macro runs in the background.
It's just a visual thing but I think it would appear a lot more tidy for the
user.

Is this possible?

--

Dave Peterson


--

Dave Peterson

Status bar (or similar) to appear whilst running a long macro
 

Yes - I copied in...

application.screenupdating = false

....at the very beginning of the macro but after that it moved on and visibly
performed the tasks - flicking in and out of the different sheets. I am
hoping to keep the front sheet visible only whilst the macro runs.


"Dave Peterson" wrote:

Did you try the screenupdating stuff?

Ant wrote:

Dave, I don't know if your saw my reply to Peter (I don't think I explained
myself very well actually!). I was wanting to know if there is something
that
appears on screen that completely hides the macro that is working in the
background. Perhaps some code which keeps "Sheet1" only visible whilst the
macro is working on other sheets. It is so the users can not see the macro
physically working. In other words, I have a macro button on a front sheet
which than does a whole lot of work on various sheets within the file. It
would be nice if the front sheet remained the only visible sheet whilst the
macro performed it's operations in the background. Is this possible?

"Dave Peterson" wrote:

You could add:

option explicit
sub yoursub()
application.screenupdating = false
'your code
application.screenupdating = true
end sub

to stop the flickering.

And pepper this kind of thing where you want:

application.statusbar = "Please wait. I'm sorting data!"

and remember to reset the statusbar before your code ends:

application.statusbar = false



Ant wrote:

I have a macro which takes about one minute to run and switches around
between sheets etc. I was wondering if it is possible for a status bar or
status page to appear on the screen whilst the macro runs in the background.
It's just a visual thing but I think it would appear a lot more tidy for the
user.

Is this possible?

--

Dave Peterson


--

Dave Peterson

Status bar (or similar) to appear whilst running a long macro
 

You may want to try not selecting the worksheets at all in your code.

For the most part, you don't have to select something to work with it.

If you do want to keep the code, then you'll have to find the lines that turn
screenupdating back on and add another line to turn it off.

===
Instead of:

worksheets("sheet1").select
range("a1").select
selection.value = "hi there"
worksheets("sheet2").select
range("b99").select
selection.value = "bye there"

you could use:
worksheets("sheet1").range("a1").value = "hi there"
worksheets("sheet2").range("b99").value = "bye there"

No selection and no flickering.

Ant wrote:

Yes - I copied in...

application.screenupdating = false

...at the very beginning of the macro but after that it moved on and visibly
performed the tasks - flicking in and out of the different sheets. I am
hoping to keep the front sheet visible only whilst the macro runs.

"Dave Peterson" wrote:

Did you try the screenupdating stuff?

Ant wrote:

Dave, I don't know if your saw my reply to Peter (I don't think I explained
myself very well actually!). I was wanting to know if there is something
that
appears on screen that completely hides the macro that is working in the
background. Perhaps some code which keeps "Sheet1" only visible whilst the
macro is working on other sheets. It is so the users can not see the macro
physically working. In other words, I have a macro button on a front sheet
which than does a whole lot of work on various sheets within the file. It
would be nice if the front sheet remained the only visible sheet whilst the
macro performed it's operations in the background. Is this possible?

"Dave Peterson" wrote:

You could add:

option explicit
sub yoursub()
application.screenupdating = false
'your code
application.screenupdating = true
end sub

to stop the flickering.

And pepper this kind of thing where you want:

application.statusbar = "Please wait. I'm sorting data!"

and remember to reset the statusbar before your code ends:

application.statusbar = false



Ant wrote:

I have a macro which takes about one minute to run and switches around
between sheets etc. I was wondering if it is possible for a status bar or
status page to appear on the screen whilst the macro runs in the background.
It's just a visual thing but I think it would appear a lot more tidy for the
user.

Is this possible?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Status bar (or similar) to appear whilst running a long macro
 

Thanks Dave. I have now gone through and tidied up the code as suggested
which works well. Ta.

"Dave Peterson" wrote:

You may want to try not selecting the worksheets at all in your code.

For the most part, you don't have to select something to work with it.

If you do want to keep the code, then you'll have to find the lines that turn
screenupdating back on and add another line to turn it off.

===
Instead of:

worksheets("sheet1").select
range("a1").select
selection.value = "hi there"
worksheets("sheet2").select
range("b99").select
selection.value = "bye there"

you could use:
worksheets("sheet1").range("a1").value = "hi there"
worksheets("sheet2").range("b99").value = "bye there"

No selection and no flickering.

Ant wrote:

Yes - I copied in...

application.screenupdating = false

...at the very beginning of the macro but after that it moved on and visibly
performed the tasks - flicking in and out of the different sheets. I am
hoping to keep the front sheet visible only whilst the macro runs.

"Dave Peterson" wrote:

Did you try the screenupdating stuff?

Ant wrote:

Dave, I don't know if your saw my reply to Peter (I don't think I explained
myself very well actually!). I was wanting to know if there is something
that
appears on screen that completely hides the macro that is working in the
background. Perhaps some code which keeps "Sheet1" only visible whilst the
macro is working on other sheets. It is so the users can not see the macro
physically working. In other words, I have a macro button on a front sheet
which than does a whole lot of work on various sheets within the file. It
would be nice if the front sheet remained the only visible sheet whilst the
macro performed it's operations in the background. Is this possible?

"Dave Peterson" wrote:

You could add:

option explicit
sub yoursub()
application.screenupdating = false
'your code
application.screenupdating = true
end sub

to stop the flickering.

And pepper this kind of thing where you want:

application.statusbar = "Please wait. I'm sorting data!"

and remember to reset the statusbar before your code ends:

application.statusbar = false



Ant wrote:

I have a macro which takes about one minute to run and switches around
between sheets etc. I was wondering if it is possible for a status bar or
status page to appear on the screen whilst the macro runs in the background.
It's just a visual thing but I think it would appear a lot more tidy for the
user.

Is this possible?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson