Excel VBA - Same sheet produces different Signature box locations on PDF for different people
Roughly half a year ago I had some VBA that prints an excel sheet to PDF then adds two signature and two form fields to the newly created PDF. It's worked without issue until two days ago. It hadn't been used for awhile so i'm not entirely sure what happened during this time.
From the same exact excel file, if I print to PDF - it positions the signature and form fields up perfectly. if anyone else prints to PDF it moves the signature and form fields way off the page entirely. Between myself and 3 other people testing it, we all have the following:
- Same Adobe DC Pro verison (including build number)
- Same page layout (landscape), same margins, etc (derived from excel sheet).
- Same preferences for documents, general, page display, units & guides, Measuring (2D, 3D, Geo)
- When right clicking on a signature box and selecting properties (position tab), we all match on position units and position.
I'm really lost on how the same exact excel file can put these items in different locations. Here is the code I use. An example of how far off it is is. From bottom (468) for the other people (1260) for me.
'add form fields to adobe sheet.
Dim PDDoc As Object
Dim AVDoc As Object
Dim JSO As Object
Dim formField As Object
Dim inputPDFfile As String, outputPDFfile As String
Dim coords() As Variant
Const thlft = 449 'left
Const thtop = 1360 'top 507
Const thrt = 793 'right
Const thebot = 1260 'bottom 468
inputPDFfile = Environ("USERPROFILE") & "\Downloads\" & descValueTitle & "_do_not_use.pdf"
outputPDFfile = Environ("USERPROFILE") & "\Downloads\" & descValueTitle & ".pdf"
Set PDDoc = CreateObject("AcroExch.PDDoc")
Set AVDoc = CreateObject("AcroExch.AVDoc")
If PDDoc.Open(inputPDFfile) Then
Set JSO = PDDoc.GetJSObject
'1st signature field
coords = Array(thlft, thtop, thrt, thebot)
Set formField = JSO.addField("authorize_signature", "signature", 0, coords) '0 = 1st page
formField.StrokeColor = JSO.Color.transparent 'StrokeColor sets the border and text colours of the field
'2nd signature field
coords = Array(thlft + 347, thtop, thrt + 335, thebot)
Set formField = JSO.addField("sca_signature", "signature", 0, coords) '0 = 1st page
formField.StrokeColor = JSO.Color.transparent 'StrokeColor sets the border and text colours of the field
'1st date field
coords = Array(thlft + 683, thtop - 68, thrt + 475, thebot)
Set formField = JSO.addField("approved_date", "text", 0, coords) '0 = 1st page
formField.textSize = 6
formField.textFont = "Arial"
formField.StrokeColor = JSO.Color.transparent 'StrokeColor sets the border and text colours of the field
'2nd date field
coords = Array(thlft + 822, thtop - 68, thrt + 640, thebot)
Set formField = JSO.addField("sca_date", "text", 0, coords) '0 = 1st page
formField.textSize = 6
formField.textFont = "Arial"
formField.StrokeColor = JSO.Color.transparent 'StrokeColor sets the border and text colours of the field