Solved Message to state that data is being processed

July 11, 2014 at 13:25:54
Specs: Windows 7
Hi Guys,
Second question of the day (for an Excel Workbook)

I have a Vba that is set in motion from a Control Button. There is quite a bit of processing taking place and it seems that when looking at the screen, nothing is happening. (I have 'Application.ScreenUpdate = False' in place)

What I would like is for a message to appear, and then disappear when the screen reverts to another worksheet, without the need to 'Ok' the message

Any ideas ?

Regards
Tony


See More: Message to state that data is being processed

Report •


#1
July 11, 2014 at 20:17:02
I'm making this up as I go along because I don't have access to Excel at the moment...probably won't have access all weekend. Anyway...

The following site contains a script that will automatically close a message box after a time period defined by the variable AckTime.

Perhaps you can use the Worksheet_Activate event to set the AckTime variable and pass it back to the script and have it automatically close the box.

Like I said, it's nothing that I can test or even suggest code for at this time. It does sound like an interesting project though. :-)

http://msdn.microsoft.com/en-us/lib...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 14, 2014 at 11:08:01
    Application.ScreenUpdating = False
'
    If MsgBox("HAVE YOU ENTERED THE DATES FOR YOUR NEW YEAR?", vbYesNo) = vbYes Then
      
   If MsgBox("YOU ARE ABOUT TO COMPLETE YOUR CURRENT YEAR. ARE YOU SURE?", vbYesNo) = vbYes Then

    If MsgBox("RUN 'COMPLETE YEAR' TO RESET THE DATA, AND ADD TO THE HISTORY", vbYesNo) = vbYes Then
    
    Dim AckTime As Integer, InfoBox As Object
    Set InfoBox = CreateObject("WScript.Shell")
    'Set the message box to close after 5 seconds
    AckTime = 5
    Select Case InfoBox.Popup("PLEASE WAIT WHILST YOUR DATA IS SAVED TO THE HISTORY. THIS MAY TAKE A FEW MOMENTS.", _
    AckTime, "", 0)
        Case 1, -1
            
    End Select

' copies year title to column P

Hi there DerbyDad03, hope you had a good weekend.
The above is working to a point, insofar as the MsgBox appears for 5 seconds, then disappears, and then the macro runs its course
(Please note that the macro is quite large and the above is the first few lines of coding)

From what you have mentioned, is it possible to have the MsgBox appear whilst the macro is RUNNING by using the Worksheet_Activate event to set the AckTime variable

If so, can I tap into that font of knowledge that you have and ask for some help with the coding

Regards
Tony


Report •

#3
July 14, 2014 at 11:55:58
✔ Best Answer
Maybe it would be easier to do something like this:

Either of these macros will put a Progress Bar on the bottom of the active sheet. In my case, I am simply counting from 1 to a big number...you would be doing whatever it is that you do while processing your data.

The first macro shows the progress after each loop of processing. The second shows the progress after a given step is performed. Perhaps one of these will work for you.

If you run either of this macros by itself, you'll see what I mean. Then perhaps you can adapt one of them to fit your needs.

This macro will update each time the code loops.

Sub UpdateProgressBarInsideLoop()
Dim x               As Integer
'Change this loop as needed.
  For x = 1 To 50    
     Application.StatusBar = _
        "Progress: " & x & " of 50: " & Format(x / 50, "0%")
'*** The stuff you do could go here...I just count. ***
       For y = 1 To 15000000: Next
  Next x
 Application.StatusBar = False
End Sub

With this macro, you would update your progress bar "manually" at whatever point you feel is appropriate.

Sub UpdateProgressBarAfterEachStep()
Dim myStep As Integer
'Show Progress bar
   myStep = 0
    Application.StatusBar = _
        "Progress: " & myStep & " of 4: " & Format(myStep / 4, "0%")
'Do something...I just count
 For y = 1 To 150000000: Next
   myStep = 1
    Application.StatusBar = _
        "Progress: " & myStep & " of 4: " & Format(myStep / 4, "0%")
'Do some more...I just count
 For y = 1 To 150000000: Next
   myStep = 2
    Application.StatusBar = _
        "Progress: " & myStep & " of 4: " & Format(myStep / 4, "0%")
'Do even more...I just count
 For y = 1 To 150000000: Next
   myStep = 3
    Application.StatusBar = _
        "Progress: " & myStep & " of 4: " & Format(myStep / 4, "0%")
'Do the last thing...I just count
 For y = 1 To 150000000: Next
   myStep = 4
    Application.StatusBar = _
        "Progress: " & myStep & " of 4: " & Format(myStep / 4, "0%")
'Counter to leave the progress bar visable for a short time
  For y = 1 To 200000000: Next
'Turn off Progress Bar
   Application.StatusBar = False
End Sub

I found the first macro (and some other ideas) at:

http://stackoverflow.com/questions/...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question