Digital Signature in Excel

[matyl]

Dear all,

My boss asked me if it is possible to insert PDF style digital signatures into an Excel file.

She wanted each of those to represent "approval" of a request, for example:
expense reimbursement, etc. A PDF style digital signature takes the place of a person's
personal signature. I know that in Excel a digital signature has a different purpose - security,
which is quite different from PDF style digital signature. That's why I'm quite confused.

Is it possible to insert something like that into an Excel file?

Is it possible to do it via VBA?

Thank you very much in advance.

Matyl

 

[oldbrewer]

a photo of a signature could be placed in an excel worksheet, say you want to put it in cell A1, put deep protection on A1 so that only the signature owner can access A1.....

 

[Logit]

Option Explicit


Sub Picture()
Application.ScreenUpdating = False


Range("A6").Select 'This is where picture will be inserted
ActiveSheet.Pictures.Insert("C:\Users\My\Desktop\cute-red-kitten-forgot-something-r-default.jpg").Select
'Path to where pictures are stored - Edit as required


'''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This resizes the picture
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
With Selection
.Left = Range("A6").Left                '<--- change cell as required
.Top = Range("A6").Top                  '<--- change cell as required
.ShapeRange.LockAspectRatio = msoFalse
.ShapeRange.Height = 100#
.ShapeRange.Width = 100#
.ShapeRange.Rotation = 0#
End With


Application.ScreenUpdating = True


Exit Sub


ErrNoPhoto:
MsgBox "Unable to Find Photo" 'Shows message box if picture not found
Exit Sub




End Sub
Sub deleteImage()
    Dim Pict As Shape
    Dim Cel As Range
    Set Cel = Sheets("Sheet1").Range("A6")
    Dim Caddress As String
    Caddress = Cel.Address
    Application.ScreenUpdating = False
    For Each Pict In Sheets("Sheet1").Shapes 'Check for each picture in the range
        If Pict.Type = msoPicture Then
            If Pict.TopLeftCell.Address = Caddress Or Pict.BottomRightCell.Address = Caddress Then
                Pict.Delete
                Exit Sub
            Else:
                MsgBox "Doesn't exists a picture in the range"
                Exit Sub
            End If
        End If
    Next Pict
    Application.ScreenUpdating = True
End Sub

 

[matyl]

Option Explicit


Sub Picture()
Application.ScreenUpdating = False


Range("A6").Select 'This is where picture will be inserted
ActiveSheet.Pictures.Insert("C:\Users\My\Desktop\cute-red-kitten-forgot-something-r-default.jpg").Select
'Path to where pictures are stored - Edit as required


'''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This resizes the picture
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
With Selection
.Left = Range("A6").Left                '<--- change cell as required
.Top = Range("A6").Top                  '<--- change cell as required
.ShapeRange.LockAspectRatio = msoFalse
.ShapeRange.Height = 100#
.ShapeRange.Width = 100#
.ShapeRange.Rotation = 0#
End With


Application.ScreenUpdating = True


Exit Sub


ErrNoPhoto:
MsgBox "Unable to Find Photo" 'Shows message box if picture not found
Exit Sub




End Sub
Sub deleteImage()
    Dim Pict As Shape
    Dim Cel As Range
    Set Cel = Sheets("Sheet1").Range("A6")
    Dim Caddress As String
    Caddress = Cel.Address
    Application.ScreenUpdating = False
    For Each Pict In Sheets("Sheet1").Shapes 'Check for each picture in the range
        If Pict.Type = msoPicture Then
            If Pict.TopLeftCell.Address = Caddress Or Pict.BottomRightCell.Address = Caddress Then
                Pict.Delete
                Exit Sub
            Else:
                MsgBox "Doesn't exists a picture in the range"
                Exit Sub
            End If
        End If
    Next Pict
    Application.ScreenUpdating = True
End Sub

Is it that simple?
But wouldn't anybody be able to insert such picture?
What my boss wants is personal signature which is exclusive only to the signature issuer.

 

[oldbrewer]

have you tried my approach............?

 

[matyl]

have you tried my approach............?

It's a good idea and exactly what my boss is after, but please tell me how can it be achieved?

 

[oldbrewer]

make your spreadsheet as required, insert the photo of the signature into a deeply protected cell, convert to pdf format.

What are you unable to do ?

 

[Logit]

.
MATYL:

oldbrewer has a solid suggestion for effectively protecting the digital signature. However, you requested a solution that would remain within the Excel workbook, indicating you do not desire to
change the format of the worksheet.

There may be a means of protecting an image (of a signature) that has been inserted onto a sheet ... but I've not been able to locate one. Perhaps someone else can assist in that regard.

I can however suggest another method though that will hopefully fulfill your needs.

In the sheet level module you would paste this macro :

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("C10")                               '<-- edit signature location here
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        MsgBox "Altering the signature is not permitted. ", vbCritical, "Signature Edit Error "
    End If
End Sub

In the ThisWorkbook module you would paste this macro :

Option Explicit


Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Edit sheet name and range location as required below :
    Sheets("Sheet1").Range("C10").Value = "This is your signature"
End Sub

You could use a different FONT for the actual signature on the sheet ... something like a SCRIPT, or an ITALIC or something other than the Font used for the remainder of the sheet
thereby giving the "signature" the look of an actual signature.

The macros will insure the signature is never changed unless it is changed within the coding itself.

If your users are code savvy, you can password protect the VBE. Hopefully they are also not familiar with hacking Excel passwords.

Download example file : https://www.amazon.com/clouddrive/share/roXyRhC78iPrpnEqbJu6y4ZscL3vFggZX7EIZrAIOz9