Deactivate "EDIT" after Digital Signature (or add Password)?

[aliend8k]

Hi forum. I posted a question the other day about a process to attempt to automatically lock a sheet after a digital signature is "removed", but still am unable to run a script that checks the form "change" status to automatically relock it. My form is locked, until the authorized signee runs the "signature" - in which it UNLOCKS the form to allow the digital signature to be added. The sheet will simply not allow the form to be locked - and signed - and I cannot find a good way to get this to work. Helpful tip was to add a Workbook_Open routine to make sure the form was locked on open, however, it runs into an error IF the form is already signed... it won't allow you to lock the signed form.
So my question is: Does anyone know if there is a way to SIMPLY deactivate or turn off the ability to REMOVE the digital signature "EDIT" that appears on the main menu which nullifies the signature (or at minimum - add a password to remove the signature)? If I was able to remove a general users ability to cancel the signature, the fact that the form is unlocked won't matter as much and only administrators could remove a signature if needed via password. I simply do not want a general user to gain access to the formulas and contents behind the locked cells (which are password protected when they get it to fill in the form), however, if they run the signature themselves, then cancel it - the form remains unlocked. Screen shot of the signed form - with the EDIT option that allows users to remove signature...

I tried to come up with a solution by checking the signature status (If ActiveWorkbook.Signatures.Count = 1 Then....); but I likely have the wrong code, in the wrong place to really get the Workbook_Change() to actually work (I'm not certain the ByVal Target is the right reference here), otherwise, it would run the ws.Protect to relock it on a change event...

thanks for any additional thoughts / input.

[SamT]

Link to other thread: Signature Block on Locked Form - Workaround

I would rethink the entire paradigm. Worksheets are hybrid Class Modules, the can have Public Properties and Private Variables. So can UserForms.

Create some StatusFlags in each sheet. Set each sheet's Visible = VeryHidden. Use the Form to show sheets as needed. Use Activate and Deactivate code in each sheet to create or destroy a new Signature block after checking the relevant status flags.

Sheets should hold their own Status Flags, The form should use sheet properties to read/write them. Sheets can set their own Flags if/as needed.

Protect the Entire Project to make it difficult for just any wannabee to see your code.

[aliend8k]

Thanks for taking the time to comment! I am by far not a VBA ninja hence the issue I've found myself in (I usually can forum/google just enough to get really stuck). I understand some of what you state but would not know how to begin coding at my level. I will let my team know and as is, they may have to live with what they got! WORKAROUND... we'll do our best to be cheap and hide fields on hidden sheets and let password protected sheets disable folks from viewing fields with proprietary data if the nosy bunch try to poke around!

Truly appreciate the info!

[SamT]

Status Flags are Boolean Variables with good names.

Private pIsAllowedRead As Boolean
Private pIsAllowedEdit As Boolean

'Read Only Properties
Public Property Get IsAllowedRead() AS Boolean
   IsAllowedRead = pIsAllowedRead
End Property

'Write Only Properties
Public Property Let IsAllowedRead(IsUserAllowedToSeeSheet As Boolean)
   pIsAllowedRead = IsUserAllowedToSeeSheet
End Property

When a Read Only and a Write Only Properties have the same name, that Property is Read/Write

Property Variables always start with a "p" for Property in my code. Others use "m" for Module.
Property Sub Names are what your code in other places use to get and set the Property Values
The Let Sub Parameter Name is what the coder sees as a tool tip when they write the Property name in his other code

Excel Objects like Worksheets and Charts have two names. The Tab name, or just "Name." is the one used in Formulas on the sheet.

=Sheet1!A1

VBA Code can use the Tab name like
Sheets("Sheet1").Range("A1")
Or, the Code can use the Sheet's CodeName like

Sheet1.Range("A1")

You can change the Sheet's CodeName in the VBA Project Explorer.

If your UserForm wants to access a Flag in Sheet1, it merely uses the CodeName and the Property Name

'Read the Property
Dim X as Boolean
X = Sheet1.IsAllowedRead

'Write or set the Property
Sheet1.IsAllowedRead = True
'or a condition
Sheet1.IsAllowedRead = IsGoodName * IsGoodPassWord
'Multiplying Booleans is the same as ANDing Them
'Adding Booleans is the same as ORing them.