CODEWITHSUNDEEP
vbaexcelsavebefore-save
Kuba
Kuba

Reputation: 291

Before save does not work with save as

I have made a macro that protects the sheet and changes sheet to sheet "A", then saves the file and after that comes back to the sheet I have started in.

Unfortunately, the Save as option does not work when my macro is in the workbook. Whenever I click save as and want to search for a place to save my file in, the macro starts running, and disables the action I tried to take.

This is the code I am writing about:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)  
    Cancel = True  
    Application.EnableCancelKey = XlEnableCancelKey.xlDisabled  
    Application.ScreenUpdating = False  
    Application.EnableEvents = False  
    Dim aktywny As Worksheet    
    Set aktywny = ActiveSheet   
For Each Sh In ThisWorkbook.Sheets  
    If InStr(Sh.Name, ".") <> 0 Then  
        If Sh.ProtectContents = False Then  
            Sh.Protect Password:="XXX", DrawingObjects:=True,   Contents:=True, Scenarios:=True _  
            , AllowFormattingCells:=True, AllowFormattingColumns:=True  
        End If  
    End If   
Next  
ThisWorkbook.Sheets("A").Activate  
Me.Save  
aktywny.Activate  
ThisWorkbook.Saved = True  
Cancel = True  
Application.EnableEvents = True  
Application.ScreenUpdating = True  
End Sub

Upvotes: 1

Views: 1360

Answers (1)

Hein Andre Gr&#248;nnestad
Hein Andre Gr&#248;nnestad

Reputation: 7025

Because of Cancel = True I guess.

You should probably use the ByVal SaveAsUI As Boolean flag to see if the user clicked "Save as".

If you don't want the macro to run at all on "Save as", you could put this in the start of the method:

If SaveAsUI Then
    Return
End If

Upvotes: 2

Related Questions