Problem with Paste Picture Link

[CaptRon]

I am adding a feature to a current workbook that will allow the user to insert a scanned signature object into a merged cell then have that signature displayed an several sheets via a picture link.

Works great except for one thing. The printed output is distorted. By that I mean the print area is stretched too wide and the page appears reduced in size from top to bottom.

Delete the picture link and the sheet prints normally.

Why is this happening and how can I fix it?

Thanks,

Ron

[Bob Phillips]

Can you post the wb and a signature for us to see?

[CaptRon]

Here's the workbook. I removed all the password requirements to access sheets and VBE. If you get a dialog box asking for a password, just click OK and get past it.

You will see the issue I described on the sheets ACT-94 and ACT-33, but only in Excel 2003 or earlier. Doesn't alter the print output in Excel 2007 even though the thing was created in 2003. Quite a few of our offices still have Office 2003 so I need this to work in both versions.

I have tried pasting the signature in other workbooks and didn't get this odd behavior so I suspect it relates to something resident in this workbook.

Ron

[mdmackillop]

Hi Ron,
I'm running 2003 and don't see a problem. I tried pasting in a photo, sizing it to suit and it appears OK

[CaptRon]

Perhaps I should clarify, the object itself prints fine, the column widths become altered so the the page text doesn't completely print as it should even though I set page scaling to 1 x 1.

Ron

[GTO]

Greetings Ron,

No expertise, but here's confirmation and what I was able to observe...

In 2003, I get same issue. I concentrated just on ACT-94 sheet, but believe issue is the same. In print preview or when actually printing, stuff is cutoff on the right. I also noted that the statements such as in row 35 wrap funny.

As you reported, deleting the pic fixes.

I noted though that at least to me, the problem isn't a pic per se, but that the pic is using (you may have used the camera tool) a range from another sheet (Setup) for the pic displayed.

Not much time, but I seemed to be able to get it to not botch stuff up when either, (1) I inserted a picture from a file, or (2) if I used a range from the same sheet as the source (I stuck the "original" signature out to the right, well past the print area) then everything was hunky-dory.

Not much help of course, as your goal was to just have the user insert a signature one place; but hopefully someone may address better.

Mark

[CaptRon]

Mark,

You may have hit on something here. I will re-work the setup sheet to move the source field to the left and see if that resolves this issue.

I'll get back with details.

Thanks for all the replies. You folks are the greatest.

Ron

[CaptRon]

Mark,

I was able to duplicate your results, but as you said, the whole point is to place the signature object in one place and have it be reproduced elsewhere in the workbook.

I tried moving the source range on the setup sheet around and tried merged and single cells with the same sad result. The right side of the page is still cut off.

I hope someone knows of a solution. I am stumped.

Ron

[Aussiebear]

Ron, can you resize the pic when it is being inserted into the sheet? There would e a maximum value otherwise the last column gets cut off and printed on a second sheet

[CaptRon]

I've tried resizing the picture link, re-positioning it, to no avail. I don't get this behavior when inserting a picture, only when copying a picture link.

Ron

[GTO]

As you've only got one picture per sheet (and only on two of the 4-5 sheets by memory), would inserting the pictures via code be a thought?

Mark

[CaptRon]

Mark,

I don't know how to go about insertion by code. Got a suggestion? I'll give it a try.

Ron

[GTO]

Hi Ron,

Mark,

I don't know how to go about insertion by code. Got a suggestion? I'll give it a try.

Ron

This seems less than ideal, and at minimum, you might want to look at the format that it pastes in to see what looks/prints best. Here's a shot though:

Option Explicit
    
Sub UpdateSignatures()
    Application.ScreenUpdating = False
    Call Shape_Action(shtACT33Front, 1)
    Call Shape_Action(shtACT94, 1)
    Call Shape_Action(shtSETUP, 3)
    Call Shape_Action(shtACT33Front, 2, "J40")
    Call Shape_Action(shtACT94, 2, "D43")
    Application.ScreenUpdating = True
End Sub
    
Function Shape_Action(wks As Worksheet, Action As Long, Optional CellAddress As String)
Dim shp As Shape
    If Action = 1 Or Action = 3 Then
        For Each shp In wks.Shapes
            If shp.Type = msoPicture And Action = 1 Then
                shp.Delete
            ElseIf shp.Type = msoPicture And Action = 3 Then
                shp.CopyPicture xlScreen, xlPicture
                Exit For
            End If
        Next
    ElseIf Action = 2 Then
        wks.Paste wks.Range(CellAddress), False
    End If
End Function

See attached example. Since the sheets that the signature gets inserted onto are not getting added copies added (like ACT-34), I changed the codenames so that these could be used.

Hope its of some help,

Mark

[CaptRon]

I'll give it a go after lunch. Thanks.

Ron

[ZVI]

Hi Ron,

This is another solution:

Sub RefreshSignature()
  
  ' ACT-33 front
  With Sheet1.Shapes("Picture 1141").DrawingObject
    .Formula = "=SETUP!M288"
    .Formula = ""
  End With
  
  ' ACT-94
  With Sheet5.Shapes("Picture 308").DrawingObject
    .Formula = "=SETUP!M288"
    .Formula = ""
  End With
  
End Sub

Call RefreshSignature for example on deactivating of "SETUP" Sheet8 by such code in its VBA-module:

' Code of "SETUP" Sheet8
Private Sub Worksheet_Deactivate()
  RefreshSignature
End Sub

After refreshing of signature sheets are printable without cutting.

Regards,
Vladimir