Print preview makes shapes vanish

[Wongadob]

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

Please Login or Register  to view this content.

Any help would be much appreciated.

[Wongadob]

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.

[royUK]

Have you set the shapes not to print?

[snb]

To add to Roy's remark:

Please Login or Register  to view this content.

[romperstomper]

1. Do you have SP2 installed?
2. Have you tried a different printer?
3. Have you checked to see if the shapes have been set to 0 width and/or height?

[Wongadob]

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-

Please Login or Register  to view this content.

And after

Please Login or Register  to view this content.

@snb
ActiveWorkbook.DisplayDrawingObjects = xlAll

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

[Wongadob]

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.

[snb]

shape.type =1 stands for msoAutoShape

So that's more than just a rectangle.

[Wongadob]

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

[Wongadob]

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:-

Please Login or Register  to view this content.

Again any help would be useful

[Wongadob]

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]

As I mentioned earlier, you need to check their width and height - if either is 0, they would not be visible.

[snb]

You can 'refresh' the sheet using:

Please Login or Register  to view this content.

[Wongadob]

@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.

[Wongadob]

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!

[WasWodge]

Try
.ControlFormat.PrintObject = True
as part of the code for all objects but as Romperstomper implied earlier it is more likely to do with your default printer settings especially as it is working on other PC's. Does anyone share the same printer? If yes does it work on theirs?
I only suggest putting .ControlFormat.PrintObject = True in the code as despite what you say in post #11 in your last post they are showing as False, which suggests that it is going by the default printer settings

[Wongadob]

I think you have missed the point. I do not want the buttons displayed when printing. Only on the sheet. The print.object property only disabled them when printing which is correct. BTW the graph prints fine (which is classed as a shape, as does the company logo) it is only on return from printing that all shapes vanish. I did have some code which made all objects of type 1 (autoshapes, so only the buttons I have for macros) be invisible during printing. And then had the same code after printing re-enabling them.

As this was causing the problem I described, I asked for alternative solutions. It was then that I discovered the print.object property to disable during printing. So I decided to use that. so I removed all of my disabling and re-enabling code.

Print.object is supposed to only not show the shapes when printing, they should be visible at all other times. So as you say they are set to false which they should be, but they should still be visible on screen when returning from printing. They operate as if they are there. i.e. you can still click on where they are, but they are not visible, yet the visible flag is set to true. So they should be visible.

I will check out whether it has the same problem with others using the same printer. I also hope today that I should have some access to their IT guy to see if he can offer any reason as he has control over printer drivers, local installations etc.

Thanks, but please keep the ideas coming as this is really getting to me

[Wongadob]

I have still not found a solution to this! I am awaiting more info from the company, but if anybody else has any ideas in the meantime, they would be much apprectiated.

[Metal Head]

whoops wrong button

[jope]

I'm relatively new using Excel. Version 14.1.2.
I have a spread sheet composed with a top line of categories and the rest figures. There are no formulas except addition on the sheet. I highlighted and "set print area" but it only prints the top line of the spread sheet.
I cannot find a command to make it print what I have highlighted and designated as the print area.
Can anyone tell me how to solve this?
Thanks,
Jope

[arthurbr]

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.