Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Running a Macro from within another macro | Excel Discussion (Misc queries) | |||
Running a macro for each item in an Autofilter list | Excel Discussion (Misc queries) | |||
more than 3 conditions in conditional formatting - possible? | Excel Discussion (Misc queries) | |||
Shadows of the Pivottable Field List while macro is running | Excel Worksheet Functions | |||
How can I run a macro in the background whilst a UserForm is visib | Excel Discussion (Misc queries) |