| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Viewer message during Macro run
This is probably easy for many of you but I just don't think I've ever done it. While a long macro is running how to I give the user a display message that says "Working..." and also suppress all the wild visible displays of what's going on? Thanks -- SWMagic ------------------------------------------------------------------------ SWMagic's Profile: http://www.excelforum.com/member.php...o&userid=26660 View this thread: http://www.excelforum.com/showthread...hreadid=399932 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Viewer message during Macro run
Application.ScreenUpdating = False
Application.StatusBar = "Working . . . " ' long code ' return status bar to excel. Application.StatusBar = False ' turn screen updating back on. Application.ScreenUpdating = True Maybe that will speed it up enough you don't need to display anything. -- Regards, Tom Ogilvy "SWMagic" wrote in message ... This is probably easy for many of you but I just don't think I've ever done it. While a long macro is running how to I give the user a display message that says "Working..." and also suppress all the wild visible displays of what's going on? Thanks -- SWMagic ------------------------------------------------------------------------ SWMagic's Profile: http://www.excelforum.com/member.php...o&userid=26660 View this thread: http://www.excelforum.com/showthread...hreadid=399932 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Viewer message during Macro run
Magic,
For progress messages, you can put them on the status bar, though they may not be noticed: Application.StatusBar = "Parsing input data" Be sure to reset it to Excel's control at the end with Application.StatusBar = "" Or you can use a modeless userform, and write to the controls in it. I think this is Excel 2000 and up. UserForm1.Show vbModeless At various points: UserForm1.Label1.Caption = "Parsing input data. Please wait" DoEvents UserForm1.Label1.Caption = "Doing other stuff. Hang on to your hat" DoEvents UserForm1.Label1.Caption = "Almost done." DoEvents -- Earl Kiosterud www.smokeylake.com "SWMagic" wrote in message ... This is probably easy for many of you but I just don't think I've ever done it. While a long macro is running how to I give the user a display message that says "Working..." and also suppress all the wild visible displays of what's going on? Thanks -- SWMagic ------------------------------------------------------------------------ SWMagic's Profile: http://www.excelforum.com/member.php...o&userid=26660 View this thread: http://www.excelforum.com/showthread...hreadid=399932 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Viewer message during Macro run
Magic,
I hit send before I answered your question about keeping the screen from flashing about while the macro runs. this will also improve the execution speed if you're changing a lot of cells. Application.Screenupdating = False It's not a bad idea to occasionally (maybe once/row, or whatever's suitable) to let it update the screen with whatever's currently on the sheet. It reassures the user that stuff is happening, and looks cool: Application.ScreenUpdating = True Application.ScreenUpdating = False -- Earl Kiosterud www.smokeylake.com "SWMagic" wrote in message ... This is probably easy for many of you but I just don't think I've ever done it. While a long macro is running how to I give the user a display message that says "Working..." and also suppress all the wild visible displays of what's going on? Thanks -- SWMagic ------------------------------------------------------------------------ SWMagic's Profile: http://www.excelforum.com/member.php...o&userid=26660 View this thread: http://www.excelforum.com/showthread...hreadid=399932 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Viewer message during Macro run
Good evening SWMagic To suppress all screen activity use this line as the first in your macro: Application.ScreenUpdating=False and to turn it on again use: Application.ScreenUpdating=True (although, technically you don't need to because screen updating will be restored at the end of a macro when control of excel is returned to the user). To display any kind of message, just design a userform (from the VBE Insert Userform) showing your message and to call it from within your macro use this instruction: UserForm1.Show and to remove it use: Unload UserForm1 HTH DominicB -- dominicb ------------------------------------------------------------------------ dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932 View this thread: http://www.excelforum.com/showthread...hreadid=399932 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Viewer message during Macro run
Userform1.show
shows a modal userform, like a message box. All code halts except for interaction with the userform. If you have xl2000 or later you can do Userform1.Show vbModeless which will allow your code to continue. -- Regards, Tom Ogilvy "dominicb" wrote in message ... Good evening SWMagic To suppress all screen activity use this line as the first in your macro: Application.ScreenUpdating=False and to turn it on again use: Application.ScreenUpdating=True (although, technically you don't need to because screen updating will be restored at the end of a macro when control of excel is returned to the user). To display any kind of message, just design a userform (from the VBE Insert Userform) showing your message and to call it from within your macro use this instruction: UserForm1.Show and to remove it use: Unload UserForm1 HTH DominicB -- dominicb ------------------------------------------------------------------------ dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932 View this thread: http://www.excelforum.com/showthread...hreadid=399932 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Viewer message during Macro run
Tom,
If the ShowModal property of the UserForm is set to False, vbModeless param in the Show method isn't necessary. Available in xl2002. Don't know about 2000. -- Earl Kiosterud www.smokeylake.com "Tom Ogilvy" wrote in message ... Userform1.show shows a modal userform, like a message box. All code halts except for interaction with the userform. If you have xl2000 or later you can do Userform1.Show vbModeless which will allow your code to continue. -- Regards, Tom Ogilvy "dominicb" wrote in message ... Good evening SWMagic To suppress all screen activity use this line as the first in your macro: Application.ScreenUpdating=False and to turn it on again use: Application.ScreenUpdating=True (although, technically you don't need to because screen updating will be restored at the end of a macro when control of excel is returned to the user). To display any kind of message, just design a userform (from the VBE Insert Userform) showing your message and to call it from within your macro use this instruction: UserForm1.Show and to remove it use: Unload UserForm1 HTH DominicB -- dominicb ------------------------------------------------------------------------ dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932 View this thread: http://www.excelforum.com/showthread...hreadid=399932 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Viewer message during Macro run
However, I don't believe that is the default. -- Regards, Tom Oglvy "Earl Kiosterud" wrote in message ... Tom, If the ShowModal property of the UserForm is set to False, vbModeless param in the Show method isn't necessary. Available in xl2002. Don't know about 2000. -- Earl Kiosterud www.smokeylake.com "Tom Ogilvy" wrote in message ... Userform1.show shows a modal userform, like a message box. All code halts except for interaction with the userform. If you have xl2000 or later you can do Userform1.Show vbModeless which will allow your code to continue. -- Regards, Tom Ogilvy "dominicb" wrote in message ... Good evening SWMagic To suppress all screen activity use this line as the first in your macro: Application.ScreenUpdating=False and to turn it on again use: Application.ScreenUpdating=True (although, technically you don't need to because screen updating will be restored at the end of a macro when control of excel is returned to the user). To display any kind of message, just design a userform (from the VBE Insert Userform) showing your message and to call it from within your macro use this instruction: UserForm1.Show and to remove it use: Unload UserForm1 HTH DominicB -- dominicb ------------------------------------------------------------------------ dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932 View this thread: http://www.excelforum.com/showthread...hreadid=399932 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Viewer message during Macro run
Tom,
You're right, it's not the default. The default is True. You have to change it. But at least it's persistent. You don't have to set it every time you open the file. :) -- Earl Kiosterud www.smokeylake.com "Tom Ogilvy" wrote in message ... However, I don't believe that is the default. -- Regards, Tom Oglvy "Earl Kiosterud" wrote in message ... Tom, If the ShowModal property of the UserForm is set to False, vbModeless param in the Show method isn't necessary. Available in xl2002. Don't know about 2000. -- Earl Kiosterud www.smokeylake.com "Tom Ogilvy" wrote in message ... Userform1.show shows a modal userform, like a message box. All code halts except for interaction with the userform. If you have xl2000 or later you can do Userform1.Show vbModeless which will allow your code to continue. -- Regards, Tom Ogilvy "dominicb" wrote in message ... Good evening SWMagic To suppress all screen activity use this line as the first in your macro: Application.ScreenUpdating=False and to turn it on again use: Application.ScreenUpdating=True (although, technically you don't need to because screen updating will be restored at the end of a macro when control of excel is returned to the user). To display any kind of message, just design a userform (from the VBE Insert Userform) showing your message and to call it from within your macro use this instruction: UserForm1.Show and to remove it use: Unload UserForm1 HTH DominicB -- dominicb ------------------------------------------------------------------------ dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932 View this thread: http://www.excelforum.com/showthread...hreadid=399932 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I change the Macro Security level in Excel 2003 viewer? | Excel Discussion (Misc queries) | |||
| How can I change the Macro Security level in Excel 2003 viewer? | Setting up and Configuration of Excel | |||
| How can I change the Macro Security level in Excel 2003 viewer? | New Users to Excel | |||
| A macro disabled message comes up but there is no macro. | Excel Discussion (Misc queries) | |||
| Macro Pop-Up Message | Excel Discussion (Misc queries) | |||
Hybrid Mode
