PDA

View Full Version : Problem with Paste Picture Link



CaptRon
02-07-2010, 10:53 PM
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
02-08-2010, 02:25 AM
Can you post the wb and a signature for us to see?

CaptRon
02-08-2010, 11:46 AM
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
02-08-2010, 01:04 PM
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
02-08-2010, 07:02 PM
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
02-09-2010, 12:59 AM
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
02-09-2010, 10:49 AM
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
02-09-2010, 12:20 PM
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
02-09-2010, 07:19 PM
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
02-09-2010, 08:15 PM
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
02-09-2010, 08:25 PM
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
02-09-2010, 11:39 PM
Mark,

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

Ron

GTO
02-10-2010, 02:05 AM
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
02-10-2010, 11:23 AM
I'll give it a go after lunch. Thanks.

Ron

ZVI
02-10-2010, 08:45 PM
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