1

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
Kuba
  • 231
  • 1
  • 4
  • 12

1 Answers1

2

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
Hein Andre Grønnestad
  • 6,438
  • 2
  • 28
  • 41
  • Still, whenever i click browse, to check for a place to save the file, the macro runs, even with your code instead of Cancel = true – Kuba Aug 24 '16 at 14:14
  • Ufortunately not. should i paste this code instead of Cancel = True ? It was the only change i've made in my code. It breaks at "return". – Kuba Aug 24 '16 at 15:12