Thread: Print preview makes shapes vanish

I have a very strange problem. I have a macro that does a print preview of various sheets. The problem is that when I run this macro all shapes on that sheet vanish and don't come back.

It does not occur on most pc's. I have tested on Windows 7 & Office 2007, Vista & Office 2007 and on many different PC's.

However on XP & Office 2007 the problem occurs. I am not sure if it is relevant, but I am using activeX 2.6 (for some forms work elsewhere, not sure if this has any relevance to Print Preview)

I though that there may be some security macro's running in personal.XLSB but that is not the case. It is just blank. Infact it did not exist until I created it with a blank macro.

I have added some code after the print preview to check for any shapes and make them visible but that does not seem to help either. Here is the code

 With ActiveSheet.PageSetup
        .LeftFooter = PrintedPageDescription
        .CenterFooter = Format(Now(), "dd mmm yyyy")
        .RightFooter = "Page &P"
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
     End With
  
    ActiveWindow.SelectedSheets.PrintPreview
     
    For Each sh In ActiveSheet.Shapes
        sh.Visible = True
    Next sh

Any help would be much appreciated.

Slight Update,

I wrote some test code to note down any shapes that existed after the print preview. Seems they are still there, but they are hidden. But WHY!!!!! grr even less hair left now.

Thanks for your replies.

Sadly I am working remotely and with a company whose IT department is not overly helpful so I cannot change printer and don't know if SP2 is installed. Do you know if some relevance to SP2. I would hope they did have SP2 installed as it fixed a lot of XP issues.

No I have not checked their size, only their X& Y positions which do not change (left,top) and the fact they are of type 1 (rectangles - I use for macros)

@Roy
I have this code surrounding the print.

Before-

   For Each sh In ActiveSheet.Shapes
        If sh.Type = 1 Then sh.Visible = False      'rectangles
    Next sh

And after

   For Each sh In ActiveSheet.Shapes
        If sh.Type = 1 Then sh.Visible = True      'rectangles
    Next sh

@snb
ActiveWorkbook.DisplayDrawingObjects = xlAll

is that a replacement. I guess that is to re-enable them to hide them do I used
xlHide?

Ah just remembered another point.

The reason I cycle through all shapes is that there are Graphs, which I believe are also classed as shapes when hiding. That is why I check their type to be 1, which I believe is rectangles.

Also it seems to do the same thing if they use print preview on the menubar(top of screen)

Yet if you start from scratch create a new sheet, add a shape and do print preview then quit Print Preview it does not loose the shape.

Just tried a different method by setting each buttons .printobject property to false. Fingers crossed and thanks for the ideas so far.

Well my latest version did not work either. Now I am not disabling any shapes at all and I just set the property of each shape (print object) to false manually in Excel. Works lovely on my machine (Windows7 with XP) still, but on the XP machine with Excel 2007 for some reason it hides all shapes when coming back from a print preview. There is nothing in my code now that does this!! My print routine now, in its entirity is:-

Sub printing()
    
    Dim PrintingArea As String
    Dim x As Integer
    
    Application.EnableEvents = False           
 
    If Left(PrintedPageDescription, 3) = "Rem" Then             'We need to create print area of Remedials page.
        x = 8
        While Cells(x, 1) <> ""
            x = x + 1
        Wend
        PrintingArea = "$A$1:$G$" + Trim(Str(x))
        ActiveSheet.PageSetup.PrintArea = PrintingArea
    End If
 
    With ActiveSheet.PageSetup
        .LeftFooter = PrintedPageDescription
        .CenterFooter = Format(Now(), "dd mmm yyyy")
        .RightFooter = "Page &P"
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
     End With
  
    ActiveSheet.PrintPreview EnableChanges:=False
    
    Application.EnableEvents = True          
End Sub

Again any help would be useful

Anybody got any ideas on this. The company I am doing it for has a big presentation today and I am purplexed. Seems the shapes still exist as you can still call the macro that the rectangles have assigned to them. But they are not showing. I have also tried to force them back into view again by re-setting the visible property to true. Completely baffled. How else can they be hidden?

Help would be very much appreciated.,

@RomperStomper - Yeah I wrote a macro to e-mail me all the data for the shapes and all seems to be in order! Which is why it is so odd. Shame you can't trap an event after printing.

@snb - I will give that a shot. Thanks.

Just got some more information back and the visibilty flag is set in all of the shapes yet they are still invisible. Their position and size are as they should be. Here is the output, yet this shape is not visible :-

Name: Rectangle 3
Type: 1
Xpos: 482.4
Ypos: 4.8
Height: 28.34646
Width: 55.19291
Visibility: -1
On Action: 'A - Compliance Tracker Master - E.xlsm'!Home
Placement: 1
Control Format - Print Object: False
-------------------------------------------------------------------------
Name: Rectangle 4
Type: 1
Xpos: 420.9
Ypos: 4.2
Height: 28.34646
Width: 55.19291
Visibility: -1
On Action: 'A - Compliance Tracker Master - E.xlsm'!printing
Placement: 1
Control Format - Print Object: False

snb - The refreshing didn't work either. But thinking about it nor did going to another page and back again, which I guess would have 'refreshed' them all anyway.

Does anybody know what 'Resource' shapes come under. i.e. is it possible their is a missing OCX file. Or if there is any dll or whatever used for print preview.

Also somebody else mentioned the Killbit issue, but that seems to stop them from appearing in the first place. These buttons appear and work fine flying all around the sheets. There are 20 sheets and navigation around the sheet is via macro buttons and all have a home to main menu. They all work and function fine until they call print preview. Then on return the shapes have vanished!