Workbook_beforesave

Question

I have the following code:

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

Myname = Application.ActiveWorkbook.Name

Mypath = "J:\Apg\Proposals\Resource Input"

 a = MsgBox("Do you really want to save the workbook?", vbYesNo)

 If a = vbNo Then Cancel = True

 If a = vbYes Then Application.ActiveWorkbook.SaveAs (Mypath & Myname)

End Sub

When I save the workbook, nothing happens.

?????

Answer 1

Try this:

Option Explicit

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

    Dim Myname As String, Mypath As String

    Dim a As Long

    On Error Goto ErrorHandler

    Application.EnableEvents = False

    Myname = Application.ActiveWorkbook.Name

    Mypath = "J:\Apg\Proposals\Resource Input"

    a = MsgBox("Do you really want to save the workbook?", vbYesNo)

    Cancel = True

     If a = vbYes Then

        Application.DisplayAlerts = False

        Application.ActiveWorkbook.SaveAs (Mypath & Myname)

        Application.DisplayAlerts = True

    End If

ErrorHandler:

    Application.EnableEvents = True

End Sub

Your "Application.ActiveWorkbook.SaveAs (Mypath & Myname)" causes the BeforeSave event to fire, whichis why you see it twice.  Disable events with Application.EnableEvents = False at the start and re-enable at the end.

Hope that helps.

Cheers

Rich

Answer 2

This is  a data input fileI customize and send to each user.  I want it saved in a particular place, if the user saves it to the wrong folder,I won;t see it and data is lost.

Answer 3

You say >>"When I save the workbook, nothing happens."

Where did you place that macro. It goes in the Workbook module, not worksheet.

Cimjet

Answer 4

Nothing happens...................as in no message appears?

Where have you placed the event code?

It must be placed into Thisworkbook module, not into a general module.

Gord

Answer 5

Mmm, not sure, but at a quick glance, don't you need to set Cancel = True every time, otherwise it will save twice:

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

Myname = Application.ActiveWorkbook.Name

Mypath = "J:\Apg\Proposals\Resource Input"

 a = MsgBox("Do you really want to save the workbook?", vbYesNo)

Cancel = True

 If a = vbYes Then Application.ActiveWorkbook.SaveAs (Mypath & Myname)

End Sub

Exactly what is it that you want this macro to do?

Cheers

Rich