NEED: excel workflow macro

[archaic0]

I am after some sort of macro that will help me track work flow between users with some specific caveats. I'm not sure that what I want to do can be done with normal track changes so I may be in need of a macro and since my macro skills are quite basic I'm after some help. Thanks in advance! I'm new to the board and my searches are not turning up usable code for my purpose so I'm hoping I can get the ear of an expert and I'm perfectly happy to compensate them for their time in some fashion. (newegg gift certificate?)

Anyway, what I have are several users who are editing a single Excel workbook. What happens now is person 1 edits the file, prints it, signs it with a pen, scans it to PDF, and sends that to the next person. Person 2 then scratches changes on it or just signs it if they agree and if there are changes then person 1 has to make them, re-print, re-sign, and re-scan. Once person 2 approves, then they sign it or if they need changes then the process starts over.

This can be done better.

My idea is for the Excel workbook to have some kind of macro signing process built-in where each user has the option of making changes or 'sealing' it with their signature. The method does not need to be court ready, just something that is easy to understand by us internally and good enough so that at a glance the final person can see that the previous 3 people approved this document in it's current form.

My idea right now is for a script to pull the current logged in user from system variables (we have lots of different users and we can't hard code the usernames). Two buttons are available to the user (Sign & Unlock). User 1 edits the file to their satisfaction, then they sign it. The signing button needs to lock the file down so nobody can change it (other than the next person signing it). User 2 then opens the file and if they need to make changes then they unlock the file with the button - which removes all signatures - then after they get done editing they sign it, which locks it again, and then the other people re-visit the workbook to re-sign it themselves or to make changes again.

The point is to allow multiple people to work on the same workbook and to put their 'stamp' on a version that they approve of. If anyone edits the file then all the stamps come off so everyone is forced to re-sign it.

If the sign macro needs a password to lock the file, that can be hard coded into the macro - we're not worried about hacking, the purpose here isn't to make it secure, just simply to replace the paper process with a digital one that makes the same sense to the users.

Again, thanks in advance for any input!

 

[Claymationator]

Maybe something like the code found here?

http://www.ozgrid.com/VBA/track-changes.htm

Hope that helps.

 

[archaic0]

That doesn't quite address my need. I'm not sure that we need to overcome any limits of a shared workbook, but rather we need something that is more like the paper workflow they do now.

Seeing WHAT what changed by WHO is not our primary concern. What we need to be able to do is to lock the workbook down with an approval of it's current state, and wipe all previous approvals if anyone unlocks it. And a single click method is needed to do those things since we're talking about typical users who will be using it once it's in place.

 

[archaic0]

Solved:

After trolling google and piecing together code from all over, I've managed to put this together working like we want. For anyone else who might have similar needs, here is what I did.

We have an excel sheet with three signature lines on it. So I made three command buttons (code at the end). The command buttons are hard coded to insert a signature .jpg into a particular area of the sheet.

The workflow is that a user starts with a blank template sheet, fills it in with numbers and then they sign it with the button next to their signature line. The next person then opens this file and reviews it. If they need to make changes, they have to click an edit command button (code at the end) and are then able to edit the file. Going into edit mode however erases all previous signatures so everyone needs to sign off on the sheet in it's final form.

The process here is that a loan officer is making a list of credit memos for all of their accounts and a manager needs to sign off of them as well as the president. So the loan officer makes their list and signs it, then the manager signs it, then the president signs it as a final review. At any point if changes need to be made, then all parties need to review the document and re-apply their signatures so that in the future it can be said that each person involved with that particular memo saw the right numbers.

The logic involves the first user adding data and then protecting the sheet. This is done by clicking one of the sign buttons. The sign button first checks to see if a signature file is available and if so, it adds the signature and then protects the sheet. Each sign button will unprotect the sheet just long enough to add a signature to the line it is intended for and then re-protect the file. Thus preventing any changes to the document other than adding the signature. I also added a function to make sure one person doesn't overwrite an existing signature.

The edit button simply removes all signatures and un-protects the file.

The purpose here is not to disallow changes as a security measure, but simply to make sure that all parties have seen and signed off on a current version of the data.

The code for inserting the images also makes the image fit the cell that it is to fill.


Sign buttons:

Sub signB17()
    If Not (Dir("U:\sig.jpg") > "") Then
         MsgBox ("No sig!")
         Exit Sub
    End If
    
    ActiveSheet.Unprotect Password:="testpass"
    Range("B17").Select

    Dim Sh As Shape
    With Worksheets("numbers")
       For Each Sh In .Shapes
           If Not Application.Intersect(Sh.TopLeftCell, .Range("B17")) Is Nothing Then
             MsgBox ("This signature has already been applied!")
             ActiveSheet.Protect Password:="testpass", DrawingObjects:=True, Contents:=True, Scenarios:=True
             ActiveWorkbook.save
             Exit Sub
           End If
        Next Sh
    End With
    
    ActiveSheet.Pictures.Insert("U:\sig.jpg").Select
    ActiveSheet.Shapes(ActiveSheet.Shapes.Count).Select
    With Selection
        .ShapeRange.LockAspectRatio = msoFalse
        .ShapeRange.Height = ActiveCell.RowHeight
        .ShapeRange.Width = ActiveCell.Width
        .Placement = xlMoveAndSize
    End With
    
    ActiveSheet.Protect Password:="testpass", DrawingObjects:=True, Contents:=True, Scenarios:=True
    ActiveWorkbook.save
End Sub

Edit button:

Sub unsign()
    ActiveSheet.Unprotect Password:="testpass"
    Dim Sh As Shape
    With Worksheets("numbers")
       For Each Sh In .Shapes
           If Not Application.Intersect(Sh.TopLeftCell, .Range("A17:H17")) Is Nothing Then
             If Sh.Type = msoPicture Then Sh.Delete
           End If
        Next Sh
    End With
End Sub