trouble with ActiveWindow.SelectedSheets.PrintPreview

Hi folks. First time poster and relatively new to Excel VBA. Here's the situation...

I wrote a set of scripts in VBA that import a text file into excel, clean out any unneeded data from the import, move the data onto a series of worksheets, edit the data on those worksheets, sort it differently on each worksheet, and then, one by one, open the worksheet in print preview to allow the user to decide to print it subsequently moving to the next worksheet in the queue until all the newly created worksheets have been dealt with by the user. All of this worked like a charm pre 2007 (tested in Excel 2003 and Excel XP without error). When I switched to 2007 everything fell apart. Now, when I run the program it does all the data manipulation as before but when it brings up the printpreview window all the buttons on the ribbon are grayed out. If I click the page it toggles the zoom but the close button and the print button are both unavailable. The only way I can close the print preview window is by clicking the little x in the upper right hand corner. In case its helpful I've tried this with

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

and neither way worked. I'm getting desperate. Can anyone help me out with this? Thanks!

ArielZusya: I am having a problem identical to the one you had in Jan. I really would appreciate if you could share with me how you resolved your problem, or worked around it. Thanks!

Hi XML2005. Sorry to be the bearer of bad news (or at least not good news). Turns out this is a "feature" (bug) in 2007. Apparently it's a well documented issue and apparently MS isn't planning on fixing it. No idea why. Odd.

Anyhow... my work-around is having the system check the version of Excel the user is using. If the user is using 2007 it doesn't take the user to the print preview but instead it just pops up a dialog box that asks the user how many copies the user wants to print (0 or more than 0).

Assuming the system allows me to do it, I'll post a zip file which contains the tool I created and some sample data so you can play with it (the sample data is identical to the data that our computers spit out and that my tool manipulates but without any real or identifying information). Let me know if you have any questions about any of it. Good luck!

Ariel

Thanks for responding so quickly and in such great detail. Hopefully, the next version of Office will resolve this issue, as many people seem to need this feature. Thanks again.

Agreed. It seems like MS is talking out of both sides of their mouth on this... on the one hand they promote user manipulation of their interfaces (Office 2007 made integrating office components into applications easier than any previous version... at least that's what the evangelists said at the launch event here in Denver) but then when something as simple as this comes up they turn a blind eye. I'm sure there's a reason (or at least I hope there is) but I haven't found one yet. Anyhow... good luck with whatever it is you're working on.

I'm using Excel 2007, and this is exactly the same problem I'm experiencing with the lines of code for the print preview that you all are referencing above...but only when I run the file on other computers. It actually works on my desktop and on another laptop that I tried.

When you say it is well documented that there is no fix for the error, to what are you referring? Just curious. I'd like to research it a bit more. I don't want to give up on the print preview feature yet.

Initially, Screen Updating is off. But I set it to True just before hitting the PrintPreview statement. After PrintPreview has executed, I set it to False again. I've used a fix similar to Ariel's and have not (yet!) found a better resolution to this issue.

romperstomper...My apologies for never having responded. I thought the forum would send me an email for responses in the thread.

So yes, screenupdating was off prior to the printpreview code being activated. That may have been the problem...even though I've never had any trouble on my computer, just clients' computers unfortunately.

Anyway, I just turned screenupdating on and tested it on a computer I was having trouble with. The printpreview ribbon now appears just as it should.

I will be testing the code on a few other computers to see if it works across the board and post the results.

Thanks, romperstomper.