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

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

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

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

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

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

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

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

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