Document Approval: Add Certifcate & Update Status

[Carmi]

Hi All - New to VBA doing it mostly for fun (I really do have to get out more!!)


Sorry for the length of my post. I hope that it is descriptive enough to allow readers understand what I want to achieve but hopefully not too long to put readers to sleep :sleep2:


Word 2003 (SP2)
Win XP

I have built a 'Proposal Builder' which allows users within the company to input key criteria into a user form which then pulls together a number of template documents to create a base proposal. The document can then be configured for a particular client, approved by the appropriate manager before being converted to pdf and being sent.

I am looking to develop the tool past the initial creation of the base proposal and provide an 'Approval' process for sign off using the functionality associated with digital certificates.

What I would like to happen is that the document is completed and sent to the relevant manager for approval. Given no changes are required the manger ?Approves? the proposal by adding a self cert digital certificate. Thus as long as the document has the certificate we know it has been formally approved and can be sent.

I realise that there is already the functionality to add a certificate to document and then to have this certificate revoked if there are any changes made to the document. However, these options are somewhat buried and relatively hard for general users to find. Therefore I want to make it easier for people to use?. And then there is the whole fun thing about just learning how to do it. As such I have added the following code to create a custom toolbar which either displays an ?Approved? or ?Not Approved? button depending on the existence of an attached certificate.

The first problem: This works fine when first opening the document with the correct button being displayed. However, if I change the status of the document (i.e from Not Approved to Approved, or from Approved to Not Approved) the button doesn?t automatically update to reflect the correct approval status. I am not sure what the correct event is to update commandbar. Is there anyway to link into the event that causes the name to

The second problem: Trying to add the digital signature I have copied the code directly from the help file. Although this opens the ?Select Certificate? Dialog box it doesn?t add the actual cert. Again I am only using self cert - will this be the cause?



'***Stored in This Document

Option Explicit
Private Sub Document_Close()
'Delete the 'Approval Status'Command bar when the document
'closes so as not be seen by other word docs
On Error Resume Next
CommandBars("Approval Status").Delete

End Sub

Public Sub Document_Open()

'Initial Template Show Input Form
If ThisDocument.Name = "MC5049 - Proposal Builder.doc" Then
UserForm1.Show
Else

'Check to determine if any signature has been added
If ActiveDocument.Signatures.Count > 0 Then
Call approved
Else
Call notApproved
End If
'Need to change the aove code to run only when a certain certicate(s) are added.
'Can this be done on public Key?

End If
End Sub


'***Stored in Module approvalStatus

Option Explicit

Public Sub approved()
Dim myBar As CommandBar
Dim btnApproved As CommandBarButton

'Create command bar
Set myBar = Application.CommandBars.Add("Approval Status")
With myBar
.Position = msoBarFloating
.Left = 50
.Top = 200
.Visible = True
.Enabled = True
End With

'Create button
Set btnApproved = myBar.Controls.Add(Type:=msoControlButton)
With btnApproved
.Caption = "Approved"
.Style = msoButtonIconAndCaption
.FaceId = 1715
.Enabled = True
.Width = 135
.OnAction = "viewSignature"

End With
End Sub

Public Sub notApproved()

Dim myBar As CommandBar
Dim btnNotApproved As CommandBarButton

'Create commandbar
Set myBar = Application.CommandBars.Add("Approval Status")
With myBar
.Position = msoBarFloating
.Left = 50
.Top = 200
.Visible = True
.Enabled = True
End With

'Create Button
Set btnNotApproved = myBar.Controls.Add(Type:=msoControlButton)
With btnNotApproved
.Caption = "Not Approved"
.Style = msoButtonIconAndCaption
.FaceId = 1716
.Enabled = True
.Width = 135
.OnAction = "AddSignature"
End With

End Sub
Sub AddSignature()
'Add Signature. Currently does not work. The Signature is not added?
ActiveDocument.Signatures.Add
End Sub

Sub viewSignature()
Dim sig As Signature

For Each sig In ActiveDocument.Signatures
MsgBox "Approved By:" & " " & sig.signer & vbCr & vbCr _
& "Date Signed:" & " " & sig.SignDate
Next

End Sub


Carmi

Warning: Answering this thread may lead to many more stupid questions... but please answer :yes

[fumei]

A selfcert document ONLY works on the computer that ran selfcert. I mean, works, in that it is recognized as a certificate. So if this document is ever used on a different machine, selfcert is pointless.

[Carmi]

Hi Gerry - thanks for your reply.
However, I am not sure if I have understood your reply and if this is an issue with regards to word functionality or my proposed intention to use VBA to more easily add a digital signature and better display if the document has been signed.

Word Functionality
On checking the standard functionality of Word (i.e adding a selfcert certificate to a document through Tools>Options>Security>Digital Signature>Add) and then testing if this digital certificate is displayed on the document when opened by another user on a different machine to the one that created the digital certificate all seems to work (well as much as it shows the document has not be changed since adding the signature).

If I digitally sign (using selfcert) and save the document I can either email this document as an attachment (using outlook 2003 to attach the document rather than using the send to functionality associated with word) to another user on a different machine and when it is opened on their machine Word advises that the document is digitally signed.

I can also save the selfcert signed document to a Net Work drive and then ask someone else using a different machine to open the document from the network location. Again Word displays that this document has been digitally signed.

In both cases the signed document (opened on different machines from which the self cert was created) display the following to advise the document has been signed;

Title Bar: [Document Name] (Signed, not verified)
Status Bar: Red Ribbon. On double clicking the red ribbon the digital certificate is shown (Again this certificate is a selfCert created on a different machine than the one used to open the document).


Given the above I don’t totally agree with your statement that;


So if this document is ever used on a different machine, selfcert is pointless

Proposed VBA
Given the above is my intention to use VBA to allow users more easily add a Digital Signature and display it using a commandbar still possible?

Carmi

[fumei]

Oooops. I have only been thinking about selfcert in terms of macro usage. You have been talking about selfcert as a - which you DID say - as a signature...like a sort of real signature.

Yeah, it works on different machines. In that there is a "signature". RE: macros - there may be selfcert, but depending on the macro security, again, selfcert (re: macros) does not work on other machines.

OK, back to your real question, using the Commandbar. I don't see why not.

BTW: what you want to use for getting the signature is: Dim sig As Signature
Set sig = ActiveDocument.Signatures.Add
ActiveDocument.Signatures.Commit

Although frankly...I am not sure as to the value of this. This is so full of holes that I for one would never consider this kind of signing to be truly valid. At least not with a certificate that is NOT (and will not be) verified. But...whatever.

Some comments.

You are removing the Commandbar when Doc close. Is this because you are using Normal.dot for your stuff? If so...start using an explicit template for this. Then the commandbar will only be used for documents that...use it.

Again the logic in your Doc Open seems to indicate your code is in Normal.dot. It would be better if it was not there.

Your Commandbar logic seems OK. What is the problem???


I realise that there is already the functionality to add a certificate to document and then to have this certificate revoked if there are any changes made to the documentSAVING changes to the document displays a message stating saving will remove signatures. It is not revoked when changes are made, only saved.


displays an ‘Approved’ or ‘Not Approved’ button depending on the existence of an attached certificate.
This is not accurate. Your code works ONLY on whether the signature count is not zero. There is no logic on whether there is more than one, whether the name of the signature is this (or that). The only logic is if there is a non-zero count of signatures.Sub AddSignature()
Dim sig As Signature
Set sig = ActiveDocument.Signatures.Add
ActiveDocument.Signatures.Commit
Call approved
End Subshould add the signature, then change the button from not approved to approved.

Now...the other way... Are you wanting that as well? If it IS approved (and the button shows this), are you wanting logic to make it NOT approved? In which case, you will need to run a Signature.Delete procedure, then update the button.

[Carmi]

Hi Gerry,

Thanks for getting back to me and apologies in taking so long to reply. I have been trying to work on the tips you provided and have advanced this somewhat but I am hoping you can help me with a couple of more points. I have summarised the objective of this template below and attached a sample version of the code for people to look at. Please feel free to make any comments.

In general I want the document to work in the following way;
User opens the ?Proposal Builder? template (Works)
A user form is displayed asking the user to input required information (Works)
Client Name
Job Number
Scope of Works
Commercial Terms
On clicking the build button the code in the form executes pulling together the document sections as defined by the users input (Works).
Proposal Document is saved (Works)
Commandbar displaying Attachment of Digital Signature is shown (Not approve) ? Have sort of fudged this by running the ?notApproved? sub after the proposal has been created and saved.
User edits document as required and submits for approval (No code required)
On approval Digital certificate (created by self cert) added to document (Works).
Status of commandbar changes to ?Approved? if specific certificate present (Works)
If the document is saved after any subsequent changes are made to the document the certificate is dropped the commandbar automatically changes to ?Not Approved? (Not working ? not sure how or when to ?refresh? the document so the commandbar changes. Tried doing this when document was saved but couldn?t get anything to work )Points 5-9 relate specifically to the approval process. Given your lead I have made some headway but any further advice you could give would be appreciated.


Although frankly...I am not sure as to the value of this. This is so full of holes that I for one would never consider this kind of signing to be truly valid. At least not with a certificate that is NOT (and will not be) verified. But...whatever.
Completely agree - Definite holes in this approvals process, but nothing worse than what we already work with. Really using it as a learning exercise. Very new to all this so just trying to get my head around what can and can?t be done.


You are removing the Commandbar when Doc close. Is this because you are using Normal.dot for your stuff? If so...start using an explicit template for this. Then the commandbar will only be used for documents that...use it
None of this code was associated with Normal.dot. The original ?Proposal Builder? that this code relates to was a Read Only document with the code either stored in the ?ThisDocument? or modules.

The main reason for this setup because I didn?t know how to make the code work anyother way. However, given your advice I went searching on how to make this work using a template and found some code that now works.

As you commented this also ensures that the commandbar is only being displayed on the relevant proposal document and not others (as it was before). However, what I was finding was that multiple instances of the commandbar could be created and were remaining after the document was closed (View/Tool Bars) thus I have changed the code slightly to ensure that the commandbar temporary property is set to true. Although this ensures that the commandbar is removed when the document is closed it also has rasied a new issue in that I am asked if I want to save changes to the Template document every time I close. Is there any way I can by pass this request?

The change to the code I made is under the Module approvalStatus on file attached.


Now...the other way... Are you wanting that as well? If it IS approved (and the button shows this), are you wanting logic to make it NOT approved? In which case, you will need to run a Signature.Delete procedure, then update the button.
Exactly ? Although I am not sure if I would need to run the Signature.Delete as from what I can tell that the signature will be automatically removed if there are any saved changes. Thus only the button needs to be updated.

You have also mentioned in relation to signatures;


There is no logic on whether there is more than one, whether the name of the signature is this (or that)
I have changed the code slightly to provide for this logic (weakly).

When the user clicks on the unapproved button the add certificate dialog appears (as you provided). If the certificate name does not equate to SIP then a msgbox is shown advising the user that they don?t have the authority to approve document. This is very weak as user can use standard Tools/Options/Security approach to add any certificate they want?. But this is all I can work out to do. Any other suggestions are welcome.

Thanks Carmi