How to close the dialog with Excel VBA

[gopi09_m]

Hi,

I have a macro running for nearly one hour.And it has 15 modules.I want to prompt a dialog with a message "Macro is Running for Module1" and immediatley after finishing the execution of that module1 i want to close it automatically.And want to prompt another dailog with message "Macro is Running for Module2" and so on. like that for 15 modules...

Is that possible to do it ?

[Bob Phillips]

Sounds like you want a progress bar.

Robin Hammond's Progress Bar
http://www.enhanceddatasystems.com/E...rogressBar.htm

[Ken Puls]

Hey Bob, the link is down...

Personally, I prefer to use the StatusBar to display this kind of message, as I don't like the overhead of refreshing a userform. (I think people have a propensity to overdo userforms with too many refreshes, which slow your routine down.)

What I do instead is to change the StatusBar at key parts of my routine. The statusbar could also be overdone, and it is a little more subtle (you have to look down and left to see it, but it is a LOT less code to implement.

You can do this as follows:
[vba]Application.StatusBar = "Running Macro from Module 1"[/vba]

And don't forget to set it back at the end of all the routines:
[vba]Application.StatusBar = False[/vba]

I would also say, though, that I'd prefer to use something a little more descriptive than "Running Macro from Module 1"

HTH,

[Paul_Hossler]

I have something like what I think you're looking for in my collection of toolkit routines that I've collected over the years.

In the main module --

[vba]
Option Explicit
'pauses processing
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Public sProgressFormSub As String

'pass the amount of time (in milliseconds)
Sub TakeNap(timelength As Long)
Sleep (timelength)
End Sub

'these are the real subs that are reported on the progress screen
Sub One()
Call Sleep(2000)
End Sub
Sub Two()
Call Sleep(2000)
End Sub
Sub Three()
Call Sleep(2000)
End Sub
Sub Four()
Call Sleep(2000)
End Sub
Sub Five()
Call Sleep(2000)
End Sub

Sub TheOneIReallyWantToRun()
Call MessageRun("Now Running sub One", "One", "This is the first status message")
Call MessageRun("Now Running sub Two", "Two", "This is another")
Call MessageRun("Now Running sub Three", "Three", "This is another")
Call MessageRun("Now Running sub Four", "Four", "This is another")
Call MessageRun("Now Running sub Five", "Five", "This is the last status message")

End Sub
[/vba]


I have this in my toolkit module, but of course you could put it anywhere. This takes the text string to display and the sub to run as parameters from the code above.

[vba]
Option Explicit
Option Private Module

'call this to setup the message form
Sub MessageRun(Msg As String, SubToRun As String, Optional Title As String = "Status")
Dim bScreen As Boolean

Application.StatusBar = Title & " -- " & Msg
bScreen = Application.ScreenUpdating
Application.ScreenUpdating = False

sProgressFormSub = SubToRun

DoEvents
Load formMessage
With formMessage
.Caption = Title
.lblMessage.Caption = Msg
.Show
End With
Application.ScreenUpdating = bScreen
End Sub
[/vba]


Then in the UserForm's code window I have the code to actually run the module, and then unload the form when it completes.

[vba]
Private Sub UserForm_Activate()

Application.Cursor = xlWait

DoEvents

'this runs the sub that was passed in ProgressFormSetup as 'SubToRun'
Call Application.Run(sProgressFormSub)

'hide the user form and unload it after the sub completes
Me.Hide
Unload Me
Application.Cursor = xlDefault
Application.StatusBar = False

End Sub
[/vba]

A Workbook is worth 2^10 words

Paul

[Bob Phillips]

Hey Bob, the link is down...

It's back now.

Personally, I prefer to use the StatusBar to display this kind of message, as I don't like the overhead of refreshing a userform. (I think people have a propensity to overdo userforms with too many refreshes, which slow your routine down.)

IMO the whole point of a progress bar is to provide comfort to the user, let them know that things are happening and the app hasn't frozen out. For that, you need to be brash, brazen and in your face. The status bar is just too subtle in my view.

[Ken Puls]

IThe status bar is just too subtle in my view.

I train my users to look down and left. Of course, I have access to each of them and can do that. You raise a good point for deploying an app where you might not get the chance to discuss it. Progress bars are more "in your face" for sure.