Printable View
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 ?
:bug:
Sounds like you want a progress bar.
Robin Hammond's Progress Bar
http://www.enhanceddatasystems.com/E...rogressBar.htm
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,
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
It's back now.Quote:
Originally Posted by Ken Puls
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.Quote:
Originally Posted by Ken Puls
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.Quote:
Originally Posted by xld
:)