AfterSave Event

[Eugene]

Hi!

Are there any ideas how to catch an event after workbook
has been saved?

Thanks in advance.

 

[Frank Kabel]

Hi
AFAIK there's no such event (only a Beforesave event exist)

 

[Eugene]

That's right. Workbook doesn't have standard AfterSave
event. Is it any way to do it? Is there anybody who solved
the problem like mine?

 

[Frank Kabel]

Hi
you may explain what you're trying to achieve with more detail. This
would make it easier to generate a workaround

 

[Eugene]

Goal: restrict data reading access to different group of
users.

Case description:
The document (Doc1.xls) contains worksheet: wshA.
Worksheet wshA has four columns: A, B, C, D.
The document simultaneously is edited by more then one
user.
Group of users (User1 and User2) insert/update/delete data
in the worksheet wshA and save their changes. As the
document is shared, actual data (both user's changes) is
available only after document has been saved. As soon as
document (Doc1.xls) has been saved, it creates "limited"
copy (Doc2.xls).
The document (Doc2.xls) contains worksheet: wshA.
Worksheet wshA has two columns: A, B.
Another group of users (User3 and User4) monitor latest
data. They have access to Doc2.xls only and can read
columns A and B data.

 

[Frank Kabel]

Hi
though the following is a little bit kludgy it should work (not tested
on shared files though). It uses the before_save event and a public
state variable. Put the following in your workbook module:

Public save_state
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim ret_msg
If save_state Then
'do nothing
Else
save_state = True
Me.Save
Cancel = True
MsgBox "and now the code to copy data starts"
End If
save_state = False
End Sub

--
the public variable is needed to prevent a looping in beforesave.
Insert your code instead of the messagebox

 

[Rob van Gelder]

I have a "Save Changes?" example on my website - it's a rough way of getting
the AfterSave event.

 

[Eugene]

Thanks a lot.

 

[Tom Ogilvy]

Why not just turn off events before you do the save, then turn them back on
after. Seems like that is what EnableEvents is provided for.

 

[Frank Kabel]

Hi Tom
lol
this solution was too simple for me to see. Thanks for that.
for the OP you may then use:



Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Application.enableevents = False
Me.Save
Application.enableevents = True
Cancel = True
MsgBox "and now the code to copy data starts"

End Sub