Hi,
I am not familiar with VBA and am curious if there's a script that could do the following. I am trying to track multiple PDF documents that are embedded in a single excel spreadsheet. Four different people will need to digitally (certificate) sign each PDF document using a smart card reader. The signers vary from document to document. The signers will have access to the pdf documents but not the excel spreadsheet that the pdfs are embedded into. When a signer opens a pdf and signs it, I want the excel spreadsheet to recognize a signing event occurred and change the cell drop down list value to "yes". See attached spreadsheet example.
Looking at the example, let's say the "rater" (column B) opens "Pilot Shell 1.PDF" and signs it under section IV- "Rater Overall Assessment". When that happens, I'd like the spreadsheet value in B2 to change to "yes", the value in C2 to change to "Pending- Email Sent" and the value in D2 and E2 to change to "No”.
As an added bonus, once someone signs, I'd like an auto generated e-mail sent to the next signer that it's their turn to sign the document. This would be a cool bonus feature but if auto generating an email is too difficult, I can do without it.
I'm not sure if this is something VBA can do or not. I'm sure there is some commercial software out there that can easily do this. Unfortunately, I'm using a military .mil network and I can't add personal software. We have adobe dc and Microsoft office to work with.
Thanks for the help.
I am not familiar with VBA and am curious if there's a script that could do the following. I am trying to track multiple PDF documents that are embedded in a single excel spreadsheet. Four different people will need to digitally (certificate) sign each PDF document using a smart card reader. The signers vary from document to document. The signers will have access to the pdf documents but not the excel spreadsheet that the pdfs are embedded into. When a signer opens a pdf and signs it, I want the excel spreadsheet to recognize a signing event occurred and change the cell drop down list value to "yes". See attached spreadsheet example.
Looking at the example, let's say the "rater" (column B) opens "Pilot Shell 1.PDF" and signs it under section IV- "Rater Overall Assessment". When that happens, I'd like the spreadsheet value in B2 to change to "yes", the value in C2 to change to "Pending- Email Sent" and the value in D2 and E2 to change to "No”.
As an added bonus, once someone signs, I'd like an auto generated e-mail sent to the next signer that it's their turn to sign the document. This would be a cool bonus feature but if auto generating an email is too difficult, I can do without it.
I'm not sure if this is something VBA can do or not. I'm sure there is some commercial software out there that can easily do this. Unfortunately, I'm using a military .mil network and I can't add personal software. We have adobe dc and Microsoft office to work with.
Thanks for the help.
| OPR Tracker.xlsx | |||||||
|---|---|---|---|---|---|---|---|
| A | B | C | D | E | |||
| 1 | OPR Names | Rater Signed? | Additional Rater Signed? | Final Reviewer Signed? | Individual Signed? | ||
| 2 | PILOT SHELL 1 | Yes | Yes | Pending- Email Sent | No | ||
| 3 | PILOT SHELL 2 | Yes | Pending- Email Sent | No | No | ||
| 4 | PILOT SHELL 3 | Yes | Pending- Email Sent | No | No | ||
| 5 | PILOT SHELL 4 | No | No | No | No | ||
| 6 | |||||||
| 7 | |||||||
Sheet1 | |||||||
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| B2:E5 | List | =Sheet2!$A$1:$A$3 |
