Signature Block on Locked Form - Workaround

Hello. I've been trying to find a good way to write some code to get around an issue I've come across. I will briefly explain what works below and why it is - and hope someone can help me generate the right code to get around the issue.
I have a spreadsheet that requires a signature to render the sheet locked and approved. The sheet itself needs to be locked (protected) to avoid formulas and proprietary company data from being observed by all users - with exception of the approving personnel. They have a button to toggle some cells to view finance data, and then the signature button would hide that data and allow them to sign the sheet. The following code allowed the one button to hide the data if displayed in M63:M64 - before signing. However, If the users cancel the signature - the form remains unlocked and the fields / formulas can then be observed. I am trying to figure out how to best LOCK the form, but allow the signature block to be "signed". I can't seem to get both to work... If a user cancels the signature - I don't want people to have an unlocked spreadsheet.. (i know there is some redundant lines in this but i've been hacking away at it for too long (pwd vs password, etc...)

private Sub CommandButton1_Click()
Dim pwd As Variant
Dim ws As Worksheet
Set ws = ActiveSheet
pwd = "123"
ActiveSheet.Unprotect password:="123" !This unlocked the sheet to allow the next two lines to simply hide the data based on format style (works)
Range("M63:M64").Select
Range("M63:M64").NumberFormat = ";;;"
Range("G77").Select !This set the location for the signature block to show up
'ActiveSheet.Protect password:=pwd !This was an attempt to LOCK the form (which does work)
'ws.Range("G77:J83").Locked = True !This was an attempt to UNLOCK the area where the signature block shows up below
ActiveWorkbook.Signatures.AddSignatureLine.Sign _ !This line works to bring up a fully "sign-able" signature
"{00000000-0000-0000-0000-000000000000}"
End Sub

The rub: If the sheet is locked, then I get an error message at the line to bring up the signature block (even if i try to unlock the region in G77). If I move the signature block line BEFORE the lock line - i also get an error message at the line where it tries to lock the form up.

I tried to set up a DO or a FOR loop to iterate through setting the FORMAT of the SHAPE object created since each time you use the file, the signature block generates a new "n" for the picture (per the following lines). I tried this technique to simply have the item selected, then set the shape to FALSE lock - but that didn't work either (first trying to get the code to set up for "Picture " & n based on the unknown n... Then actually get it to set the selection to false...

ActiveSheet.Shapes("Picture 16").Select
Selection.Locked = False
End Sub



If I leave a pre-fabricated signature block on the screen, lock everything except for the picture - I was trying to at least hide it behinds something upon selecting the sign button would reveal it - but that too has been a problem as the signature block seems to ALWAYS be on top of everything - no matter what I try...

I know I wrote a bunch - but any assistance would be greatly appreciated! Thank you for taking the time to read to this point!!!
~D8K

Welcome to the forum!

First off, I normally Protect all sheets in ThisWorboook object's Open event. If you use the option, UserInterFaceOnly:=True, then code can make any change.

For the range locked, you set it to True but it should be False if you want to unlock a range.

In the ThisWorkbook's Close and maybe the Save event too, set the locks to suit.

Quote:

---

Originally Posted by Kenneth Hobs

Welcome to the forum!

First off, I normally Protect all sheets in ThisWorboook object's Open event. If you use the option, UserInterFaceOnly:=True, then code can make any change.

For the range locked, you set it to True but it should be False if you want to unlock a range.

In the ThisWorkbook's Close and maybe the Save event too, set the locks to suit.

---

Kenneth,

Thanks for the pointers - I did mistype the Range Lock but only when i copied/pasted here. That technique was an attempt to have those cells where the signature block appears to be unlocked but it didn't work - therefore I muted out the line.

I like the work-around and will try an open event / save upon update option. I hope that just forces the entire thing to be protected - it just seems to get hung up with the locking that occurs when a signature block gets added. That will be the midnight oil for me!

Cheers-

e.g. In ThisWorkbook object:

Code:

---

Private Sub Workbook_Open() 
  Dim ws As Worksheet
  For Each ws In Worksheets
    ' UserInterfaceOnly:=True allows code to change data.
    ws.Protect "ken", UserInterfaceOnly:=True, DrawingObjects:=True, _
      Contents:=True, Scenarios:=True
  Next ws
End Sub

---

Quote:

---

Originally Posted by Kenneth Hobs

e.g. In ThisWorkbook object:

Code:

---

Private Sub Workbook_Open() 
  Dim ws As Worksheet
  For Each ws In Worksheets
    ' UserInterfaceOnly:=True allows code to change data.
    ws.Protect "ken", UserInterfaceOnly:=True, DrawingObjects:=True, _
      Contents:=True, Scenarios:=True
  Next ws
End Sub

---

---

Ken - perfect code to get the workbook locked upon opening. aside from PW change - it works. If you don't mind, please take a look at the two attachments - I've highlighted areas that I was referencing in the first initial question - and the subsequent code that changes. What I tried to simply add was the last line of the commandbutton() execution - which places the final signature block - allows approval and locks HOWEVER - because I have to have the entire thing unlocked to remove the cells in M63 from the locked final version - the entire form remains unlocked - and now I get an error code trying to simply LOCK the form AFTER it is marked final from the signature (highlight in the middle random cell J58 for simple demonstration (normally all of this is locked). I've pinned the BANNER bar and as you can see - the PROTECT SHEET option is grayed out - and upon hovering - it does say that the form is marked final. The last piece to this puzzle is bypassing the "marked as final" restriction and being able to lock cells AFTER while keeping the signature block in place/signed...

Cheers!Attachment 20757Attachment 20756

I can't see, is the password the same in both locations?

Yes, sorry - thought they were attachments to click to open...

- the open worksheet PW is set simply as 321 (for now...). the command button that both TOGGLES and Insert Signature also has the PW for activatesheet.unprotect and protect as "321" the only OTHER password which is different is simply the one that allows the vendor to toggle the actual fields where the formatting changes (to view pricing details). The code for the two buttons on the LEFT screen shown below... The last line - Activesheet.protect "321" is highlighted in the yellow above - when the code breaks and won't continue with a RUNTIME 1004 error.


Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Worksheets
' UserInterfaceOnly:=True allows code to change data.
ws.Protect "321", UserInterfaceOnly:=True, DrawingObjects:=True, _
Contents:=True, Scenarios:=True
Next ws
End Sub


Private Sub CommandButton1_Click()
'Dim pwd As Variant
'Dim ws As Worksheet
'Set ws = ActiveSheet
'pwd = "123"

ActiveSheet.Unprotect "321"
Range("M63:M64").Select
Range("M63:M64").NumberFormat = ";;;"
Range("G77").Select
'ActiveSheet.Protect password:=pwd
'ws.Range("G77:J83").Locked = True
'.ActiveWorkbook.Signatures.ShowSignaturesPane = False
ActiveWorkbook.Signatures.AddSignatureLine.Sign _
"{00000000-0000-0000-0000-000000000000}"
ActiveSheet.Protect "321"

I should also now mention - that once the SIGNATURE is in place - closing the file and reopening (which should run the OPEN code to lock the cells) throws another RUn-time 1004 error "Method of 'Protect' of object '_worksheet' failed. This is the kind of fail I get when i try to modify the file once the signature block is in place...Attachment 20759