BeforeSave Event not firing

[cutestuff]

hi,

I have the following code in the Workbook Before Save function (in This Workbook). However, it does not seem to be executing.... ANyone know why? If I put it in a macro and run it, it works. But I need it to execute when a user saves the workbook (well, after they save).

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
On Error GoTo Err_WkBk

Cancel = True
Application.EnableEvents = False
ActiveWorkbook.Save
Application.EnableEvents = True

Dim tPath As String

tPath = "C:"

If ThisWorkbook.MultiUserEditing Then
Application.DisplayAlerts = False
fil = tPath & "\" & ThisWorkbook.Name
ThisWorkbook.SaveAs fil, , , , , , xlExclusive
Application.DisplayAlerts = True
Else
Application.DisplayAlerts = False
fil = tPath & "\" & ThisWorkbook.Name
ThisWorkbook.SaveAs fil, , , , , , xlShared
Application.DisplayAlerts = True
End If

Exit_Err_wkBk:
Exit Sub

Err_WkBk:
If Err.Number <> 2501 Then
MsgBox Err.Description
End If
Resume Exit_Err_wkBk
End Sub

Thanks so much,
J

 

[GlennUK]

You would probably be better off posting this query in the VBA forum forum707 .

That said, a quick query before going any further is, is there anything else about this workbook that we need to know? For example, it is Shared?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[cutestuff]

Yes, it is shared.
Does that affect it?
I thought that code can still run when workbook is shared, it's just not editable?

 

[GlennUK]

I can't remember if I've come across problems of events not firing in Shared workbooks ... there's some alarm bells going off in the back of my mind there, but can't quite recollect any specific problems. If I have time I'll do some google searching on the subject ( or you could ), this afternoon.

The first thing I'd check is whether EnableEvents is still True by the time you want to close the workbook. Can you check that?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[cutestuff]

I've actually taken a copy of the wkbk and taken it off sharing just to see - but it still won't fire... weird...
i do have code in Auto_Close that always executes (shared or not) so I thought it would be weird that BeforeSave won't...

 

[SkipVought]

Do you have EnableEvents assigned FALSE anywhere in your code?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cutestuff]

Yes, at the beginning (see my first post).
But I assign it to TRUE right after it saves.

 

[fumei]

But I assign it to TRUE right after it saves. "

But you doing that in the BEFORESave event. This is why it will work in a standalone procedure (a macro), but not in BeforeSave. Can you action a real save in the BEFORESave event?

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry