une


I have a macro that takes a long time to execute. While it is running I would like to put an object on screen to inform the user that the macro is running and they should just wait. Otherwise they may think something is wrong. It would also be good if this object had a button on it the user could click to end the macro if they run out of patience. I thought about a message box, but when it is displayed it halts execution until the user clicks it, then it dissapears anyway so this will not do the job. Is there another object I could place on screen that has say "display" and "close" functions that I could use as follows;

Sub theMacro()

SignalBox.display "Just wait, the macro is running"

'lots of code

'lots of code

SignalBox.close

UserForm1.show ' shows macro results

End Sub

Maybe another userform could do this, but it would have to be able to stop the macro via a command button (not sure if possible) and/or close itself.




Re: Message for display during macro execution

une


I tried the userform route again without any luck. I added a userform called UserForm2 and placed a captioned label and a command button on it. I then added the following statement at the start of the macro's Sub;

Sub theMacro()

UserForm2.Show

'lots of code

'lots of code

Unload UserForm2

UserForm1.Show

End Sub

I then coded the command button on UserForm2 as follows;

Private Sub CommandButton1_Click()
Unload UserForm2

' I tried to add something here that would also cause theMacro() to exit, but I could not get anything to work

' I tried Exit Sub with no luck

End Sub

I found this got me close to what I want to achieve. But the appearance of UserForm2 on screen during theMacro() execution causes execution to pause until the command button on UserForm2 is clicked. I just want UserForm2 to sit there and have theMacro() run behind it. Then I want UserForm2 to dissapear just prior to UserForm1.Show. Alternatively if the user clicks the command button on UserForm2, I want execution of theMacro() to halt completely and have all UserForms dissapear off the screen.






Re: Message for display during macro execution


Re: Message for display during macro execution

une

Thanks, I will have a look.