other events whilst saving...


Well-known Member
May 1, 2002
Office Version
  1. 365
  1. Windows
Hi Folks

Does anyone know if it's possible to use "ActiveWorkbook.Save" then have a loop running until the workbook's finished saving?
I'm trying to design a "Saving indicator" - a bit like a progress bar but with dots appearing in a line.
The code below works well. It's called from a userform (userform4).
On the userform are 3 labels.
One ("mess") contains the caption - either "Please wait - saving workbook", or "Workbook saved."
The other 2 are laid one on top of the other. Both have the same background colour as the userform. The bottom one contains a row of dots. As the loop progresses it moves the top one ("waiting") to the right and slowly reveals the line of dots.
When the loop's been through 20 iterations it re-sets everything and starts again.
At the moment it does this five times (the outer loop) then stops.

Sub clrout()
Dim i As Integer, i2 As Integer
With UserForm4
    .mess.Caption = "Please wait - saving workbook"
    .mess.Width = 174
    .Label35.Left = .mess.Left + .mess.Width
    .waiting.Left = .Label35.Left
End With
wb_saving = True
i = 0
i2 = 0
Do Until i2 = 5
    Do Until i = 20
      Start = Timer
        Do Until Timer > Start + 0.15
         UserForm4.waiting.Left = UserForm4.waiting.Left + 5
       i = i + 1
    UserForm4.waiting.Left = UserForm4.Label35.Left
    i = 0
    i2 = i2 + 1
UserForm4.mess.Caption = "Workbook saved"
End Sub

What I'm trying to achieve is to start this loop when I start saving the workbook (it's taking about 10 seconds to save at the moment, but this will increase as it gets bigger) and exit it when the save has finished.
I've already declared a boolean variable at the top of a module, ready to act as a "switch" so that the loop can keep testing to see if the switch has changed, and therefore exit, but I can't see how I can run the loop as the workbook saves.

Any ideas?

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
............I've been digging around the WIN32 help file, and found this entry:

Asynchronous call

A function that allows the next instruction in the process to be executed as soon as the call is made, without waiting for the function to return. Most OLE functions and interface methods are synchronous; that is, the function must return before the next instruction is executed. OLE defines six asynchronous methods, five within the IAdviseSink interface - OnDataChange, OnViewChange, OnRename, OnSave, and OnClose - and one within the IAdviseSink2 interface - OnLinkSourceChange.

I looked up "IAdviseSink::OnSave", and got this:


Notifies all registered advisory sinks that the object has been saved.

Void OnSave();


Object handlers and link objects normally implement IAdviseSink::OnSave to receive notifications of when an object is saved to disk, either to its original storage (through a Save operation) or to new storage (through a Save As operation). Object Handlers and link objects register to be notified when an object is saved for the purpose of updating their caches, but then only if the advise flag passed during registration specifies ADVFCACHE_ONSAVE. Object handlers and link objects forward these notifications to their containers.

Looking at it, this would do the trick, but I havn't the foggiest where to put it, or how to invoke it.

Any help mucho appreciato.
Upvote 0
Hi Tom

Well, the full answer is that I'm trying to make it look like a stand-alone application as much as possible, whilst still using Excel / VBA.
It's all controlled through userforms, so right at the beginning I use application.hide with the workbook_open event, then at the end when the user quits, I use application.quit.
The answer to your question is, therefore, no - only a userform is visible, and this is the one I'm trying to run my "Saving indicator" on.
If you have an idea which might work, I could of course make the application visible again for the duration of the save, but this would to a certain extent defeat the object (pardon the pun!)

Either way, I'm interested in anything you have to offer, thanks.
Upvote 0
My conclusion is you cannot do this with code being hosted by the app that is currently in a "hung" state while saving. API or not, you will need to do this from another process. Another instance of Excel, for example.
Upvote 0

I had a feeling this might be the case.
I don't give up easily, but now that someone who "knows" has confirmed my doubts I'll give it up, and not waste any more time. (Shame, because my "Saving indicator" looked quite smart, I thought!)

One of the main reasons for wanting this was to inform the user that there was going to be a delay, and not to start wildly hitting keys, trying to re-start the project! I was using message boxes, but I had to input quite a bit of data myself last week, and the constant raft of message boxes appearing, with the associated "Ding!" drove me mad!
I'm starting work on a system of messaging with labels on the userform instead, changing the message before and after the save etc etc. which is simpler, and probably just as effective.........

As always, thanks for your input.

All the best
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back