Toggle protection

Hi
I am looking for a vba toggle interface protection with "password". With condition that upon closing the sheet will be auto protected (if not protected before closing).
Thank you
Regards,
Ash

Just put this in the workbook before close event.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheet1.Protect Password:="1234"
End Sub

It will protect before closing .change the sheet and password as per your need

If you want to "toggle" between protected and not protected then:

Sub ToggleProtect()
    If Sheets("Sheet1").ProtectContents = True Then
        Sheets("Sheet1").Unprotect Password = "PASSWORD"    
    Else
        Sheets("Sheet1").Protect Password = "PASSWORD"
    End If
End Sub

And if you want to protect the sheet when you go to a different worksheet then:
This goes in the sheet's module

Private Sub Worksheet_Deactivate()
ActiveSheet.Protect Password = "PASSWORD"
End Sub

And (for the sake of completeness) the code provided in post#2 above should be placed in the ThisWorkbook module

or even

Sub ToggleProtect()
    If Sheets("Sheet1").ProtectContents = False Then Sheets("Sheet1").Protect Password = "PASSWORD"
 
End Sub

If that is what you want try this:

Sub ToggleProtect()
    If Sheets("Sheet1").ProtectContents = True Then
 
    Else
        Sheets("Sheet1").Protect Password = "PASSWORD"
    End If
End Sub

This toggles "protect" to ON if OFF and
to OFF if ON, but only if correct password entered

Sub ToggleProtect()
    Dim Pword As String
     If Sheets("Sheet1").ProtectContents = True Then
        Pword = InputBox("Enter a password")
        Sheets("Sheet1").Unprotect Password = Pword
        
      Else
        Sheets("Sheet1").Protect Password = "PASSWORD"
    End If

End Sub

But I cannot run VBA script when protected

What you need to do is to unprotect your sheet first, then run your code, then protect it again - like this:

Sub DoSomething()

   Sheets("Sheet1").Unprotect Password = "PASSWORD"    

'HERE you put your code that does something
   
   Sheets("Sheet1").Protect Password = "PASSWORD"

End If

This must be placed in the ThisWorkbook module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  If Sheets("Sheet1").ProtectContents = False Then Sheets("Sheet1").Protect Password = "PASSWORD"
End Sub

Without seeing what the code does, it's difficult to advise. But if you want to protect your sheet, you have little choice.

Does the user need to see the sheet?
Instead of protecting the sheet you could hide it

And there is one bit of code that stops user from unhiding the sheet without VBA

Sheets("Sheet1").Visible = xlSheetVeryHidden

To unhide it via VBA

Sheets("Sheet1").Visible = True

If you hide a sheet, you will not need to unhide it to run VBA - UNLESS your code is selecting the sheet or cells in the sheet - so you may need to amend your code avoid that.

Glad it worked for you
Please mark thread as SOLVED (thread tools at top of thread)