Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Object appears only on printout

Copper Contributor

This downward-pointing blue arrow, about 2 inches long and 1/8 inch wide appears only on the printout.

 

Arrow.png

 

I have been unable to prevent its appearance. I will be very grateful to learn how to remove it. Thanks!

12 Replies

@Yan_Woellhaf You can try to locate the shape using the Selection Pane. On the Ribbon, go to Home > Find & Select > Selection Pane... If the shape is present in the worksheet, it will be listed here. Click the applicable "Arrow: Down 1" shape to select it:

 

selection_pane.png

 

To hide the shape from the printout, go to the Shape Format tab on the Ribbon and click on the Size and Properties dialog box link:

 

size_properties.png

 

Expand the Properties section and uncheck the "Print object" check box.

 

format_shape.png

@djclements Thank you very much for taking the time create a very detailed response!

Unfortunately, my Selection pane is blank.

@Yan_Woellhaf That's an interesting predicament indeed! Is there any chance an image has been inserted into the header or footer? If you switch to page layout view (on the Ribbon, go to View > Page Layout), does the arrow appear? If you click into the header or footer section while in page layout view, do you see the code &[Picture]?

Very good suggestion, but the header and footer are blank.

@Yan_Woellhaf 

Could you make a sample workbook without sensitive data available via OneDrive, Google Driver or similar?

Regrettably, I cannot make a similar workbook available. However, I believe I am on to something.
An older version of the same file is 504 KB. The problem file is 1,845 KB. There is no increase in the amount of data, and no reason I can think of for the size increase. I suspect that since it's been in daily use for a year and a half and regularly saved, the size may increase with each save.

@Yan_Woellhaf My last suggestion would be to try the Document Inspector to check for Invisible Content (or any other options you might find applicable). Make a backup copy of your file first, though, in case anything goes wrong. Go to File > Info > Check for Issues > Inspect Document, then select any applicable options to Inspect.

Problem is half solved!
The problem Workbook helps the user create and print a daily schedule for teams 1, and 2.
The main sheet is a form that automatically fills in the header and helps the user enter the tasks for the day.
It's been in use five times a week for the past couple years, with no problems. Only very recently have the blue arrows appeared on the printout.
When I reviewed the VBA code, I realized I'd forgotten all about the dozen or so hidden sheets in the Workbook.
When the user clicks Print on the form, data from the input form is transferred to two separate hidden sheets, one for team 1 and one for team 2. Much of the information is the same, but the tasks are different.
When I looked at the hidden sheets I saw the arrow Shapes. I checked the Don't Print checkbox and, as expected, the arrows didn't print. I was also able to delete them and verified they weren't added when the Workbook was closed and repopened.
So the problem of the blue arrows appearing on the printouts is solved, but a HUGE question remains: How did these identical shapes get added to two different sheets in the exact same position?
Thanks to all for the very useful suggestions. I'm embarrassed to have forgotten about the obvious -- the hidden sheets that are printed.
Jan

@Yan_Woellhaf 

Congratulations on finding the cause of the arrows. We may never know how they ended up on the visible sheets...

best response confirmed by Yan_Woellhaf (Copper Contributor)
Solution

@Yan_Woellhaf Based on what you've described, the most logical explanation is the VBA code is using the Range.Copy method to transfer the data to the hidden sheets. For example:

 

Sub CopyPasteAll()
    Sheet1.Range("A1:C15").Copy Sheet2.Range("A1")
    Application.CutCopyMode = False
End Sub

 

When you copy/paste a range, it will include any shapes present in that range; furthermore, any subsequent copy/paste procedures will not overwrite shapes in the destination range (they will persist). At some point, a user inserted the blue arrow into their input sheet, then ran the VBA code, which transferred the arrows to the hidden sheets, and they've been there ever since.

 

Consider an alternative method for transferring the data. For example, use the Range.PasteSpecial method to paste values only:

 

Sub CopyPasteSpecial()
    Sheet1.Range("A1:C15").Copy
    Sheet2.Range("A1").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
End Sub

 

A more efficient method, though, would be to copy the source range to an array, then output the values to the destination range:

 

Sub CopyToFromArray()
    Dim arr As Variant
    arr = Sheet1.Range("A1:C15").Value
    Sheet2.Range("A1").Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
End Sub

 

Without seeing your code, this is just my "best guess". Hopefully it applies to your situation and can be adapted to meet your needs. If not, then please disregard. Cheers!

Many thanks, djclements!

I think your scenario is very likely. I did not know about the persistence of shapes and that explains a great deal!

Again, many thanks.

Jan
ok
1 best response

Accepted Solutions
best response confirmed by Yan_Woellhaf (Copper Contributor)
Solution

@Yan_Woellhaf Based on what you've described, the most logical explanation is the VBA code is using the Range.Copy method to transfer the data to the hidden sheets. For example:

 

Sub CopyPasteAll()
    Sheet1.Range("A1:C15").Copy Sheet2.Range("A1")
    Application.CutCopyMode = False
End Sub

 

When you copy/paste a range, it will include any shapes present in that range; furthermore, any subsequent copy/paste procedures will not overwrite shapes in the destination range (they will persist). At some point, a user inserted the blue arrow into their input sheet, then ran the VBA code, which transferred the arrows to the hidden sheets, and they've been there ever since.

 

Consider an alternative method for transferring the data. For example, use the Range.PasteSpecial method to paste values only:

 

Sub CopyPasteSpecial()
    Sheet1.Range("A1:C15").Copy
    Sheet2.Range("A1").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
End Sub

 

A more efficient method, though, would be to copy the source range to an array, then output the values to the destination range:

 

Sub CopyToFromArray()
    Dim arr As Variant
    arr = Sheet1.Range("A1:C15").Value
    Sheet2.Range("A1").Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
End Sub

 

Without seeing your code, this is just my "best guess". Hopefully it applies to your situation and can be adapted to meet your needs. If not, then please disregard. Cheers!

View solution in original post