VBA print preview hangs Excel

P

Paul Pedersen

I'm using Excel 2003 SP2.

I have a non-modal form that has among other things, a button that when
clicked has this code:

Worksheets(<sheet name>).PrintPreview

When I click on this button, the print preview appears as expected, but
Excel hangs completely. Any click on the Excel window or the VB editor
window or the form results in just a beep. It almost acts as if there were a
modal window open, but there is none. I can no longer alt-tab to either
Excel or the VB editor. I have to kill it with the Task Manager.

What am I doing wrong?
 
C

Chip Pearson

Paul,

You MUST Hide your form prior to calling PrintPreview and then Show the form
after PrintPreview is closed. PrintPreview acts modally, so your code will
pause execution until Preview is closed. E.g.,

Private Sub btnPrintPreview_Click()
Me.Hide
Worksheets(1).PrintPreview
Me.Show
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
 
P

Paul Pedersen

I tried "me.hide" before, but it hid the whole worksheet! Not good.

I also tried <formname>.visible = False, but that was disallowed.

But I tried me.hide again, and this time it worked. Don't ask me why,
because I thought I did it that way last time.

But whatever, now it's ok. Thanks for the help.
 
C

Chip Pearson

I tried "me.hide" before, but it hid the whole worksheet! Not good.

If that is the case, then the code wasn't in the object module I assumed it
was. I assumed that you were calling PrintPreview directly from within a
UserForm module. In that case, "Me" refers to the UserForm object. If you
have the code in another object module, such as a worksheet module, the "Me"
reference will refer to that sheet. "Me" always refers to the instance of
the class in which it is used. Thus, if your code is in a sheet module,
you'd need to use "<form-instance-name>.Hide" where <form-instance-name> is
the name of your userform, or, perhaps, an instance of the form, if you have
a variable declared As UserFormName.

Hide the UserForm and PrintPreview will work as you would expect.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email on the web site)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top