Save Copies of Workbook with Before Save Event

[RyanH]

I am having problems saving a copy of an add-in workbook in 2003. If I open
the Add-In workbook and use the shortcut save button in VBE the 2 copies are
saved but it is not saved in its original folder I opened it up in. (Note:
I confirm if it was
saved by looking in each folder and viewing the Last Modified Date and
Time.) If I use the immediate window and type, ThisWorkbook.Save it does not
save
the copies, but saves in the original folder location. I am really scrathing
my head on this one! Any ideas?

Note: This add-in is used as a reference for another workbook. The add-in
and data workbook that references the add-in are located on a network server.
Does it matter if the add-in workbook is in use?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim strPrompt As String
Dim intButtons As Integer
Dim strTitle As String
Dim myUserName As String
Dim objNetwork As Object
Dim strBackUpPath As String

On Error GoTo ErrorHandler

With Me
' save a copy in public Power Vault folder
strBackUpPath = "\\Powervault\Global Schedule BU\"
.SaveCopyAs (strBackUpPath & .Name)

' get computer network user name
Set objNetwork = CreateObject("Wscript.network")
myUserName = objNetwork.UserName

' save a copy in Ryan's My Documents if ryanh is signed in
If myUserName = "ryanh" Then
strBackUpPath = "C:\Documents and Settings\ryanh\My
Documents\Ryan's BackUp Programs\Global Schedule BackUps\"
.SaveCopyAs (strBackUpPath & .Name)
End If
End With

Exit Sub

'---------------------
ErrorHandler:

' if error occurs notify user
If Err.Number > 0 Then
strPrompt = "The back up file for " & ThisWorkbook.Name & " may not
have been saved in '" & strBackUpPath & "'."
strPrompt = strPrompt & " Please make a note of this and notify
Ryan."
intButtons = vbExclamation
strTitle = "Problem"
MsgBox strPrompt, intButtons, strTitle
End If

End Sub

 

[RyanH]

For some reason when I add this little bit of code to the beginning of the
sub it saves the add-in workbook in its current location. But without this
little bit of code it does not save in its current location. Any ideas on
this? Is this possibly a Excel bug or am I just stupid?


' save workbook in current folder
Application.EnableEvents = False
ThisWorkbook.Save
Application.EnableEvents = True