VBA code to make sheet uneditable after I save

[reasonancefa]

Good day all

I'm working on an excel spreadsheet containing certificates that can not ever have the same cert number and can not be editable after I save it.
The only thing I can be allowed to do is insert an overlaying text saying canceled if I make a mistake and I saved it

I already sorted out the certificates number automatically counting up every time I open the workbook, And I will be able to insert a button to overlay the cancel text.

Only thing I can not sort out and need help with is making the workbook not editable after I save, I cannot use the protect worksheet option for the reason I can always insert the password and edit it again.

If it is possible to code VBA to make workbook not editable after I save but still be able to insert overlaying text saying canceled,

Thank you in advance for assistance

[6StringJazzer]

Protection would be the way to do it. There is no other method (that I know of) that would prevent edits but still allow you to overlay one particular type of text. You could write VBA code that would prevent any attempt to make a change, but that requires the user to allow VBA to run. It is easier to disable VBA than it is to type a password.

It's not clear why you don't like the password-protect option. Are you the only person using this? If so, then surely you can trust yourself not to change something that you don't want changed. If not, then surely you can keep the password a secret.

[reasonancefa]

Protection would be the way to do it. There is no other method (that I know of) that would prevent edits but still allow you to overlay one particular type of text. You could write VBA code that would prevent any attempt to make a change, but that requires the user to allow VBA to run. It is easier to disable VBA than it is to type a password.

It's not clear why you don't like the password-protect option. Are you the only person using this? If so, then surely you can trust yourself not to change something that you don't want changed. If not, then surely you can keep the password a secret.

The workbook would be used by someone else and I was trying to avoid going over to him and type in the password every time he needs to cancel a certificate.
But I do see your point in if he wants to change things he can just disable VBA, Witch will make the protect workbook option mutch more safer.

So I think i'm going with you on this and protect workbook with a password only I know, I'll just have to work out something else for the cancellation of a certificate

Thank you for your input

[6StringJazzer]

You can do the cancel of a certificate using VBA. You could put a Cancel button, or perhaps identify a cell for double-click, that would bring up a "Confirm Cancellation" dialog box, unprotect the sheet, make whatever change is needed to cancel the certificate, then re-protect the sheet. What did you have in mind to begin with?

[mdmackillop]

This should protect any sheet where C1 contains data and unlocks D1 where "Cancelled" could be noted. The code will create a random 16 character password which you cannot recover.

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim sh As Worksheet, x As Boolean, p As String, i&
For i = 1 To 16
    p = p & Chr(Application.RandBetween(33, 255))
Next i
For Each sh In Worksheets
x = Not (sh.ProtectContents)
If sh.Range("C1") <> "" And x Then
    sh.Range("D1").Locked = False
    sh.Protect Password:=p
End If
Next
End Sub