Print preview / full screen view problem

[Neil Goldwasser]

Dear all

Since I work with a lot of learners with reading/visual e.g. tracking
difficulties, I have customised my interactive workbook with VBA so that the
view changes to "full screen" upon opening it, in order to reduce the "visual
noise" of the ribbon etc (which they can't use due to protecting the
worksheet). However, one of the functions is that there are buttons which run
macros to generate a paper-based worksheet and then open the print preview...
and when the view is still "full screen", you can no longer click on the
buttons to then print it!

Does anyone know of some code that would ensure a "normal" view when the
print preview is opened, but that would make the worksheet return to "full
screen" view when the page is printed, or "close print preview" is pressed?

I have thought about keeping just that worksheet on "normal" view (using the
worksheet activate events), but I'd still rather that the visual noise is not
there at all, even on that one sheet. I also tried adding code so that when
the button is pressed, the view returns to "normal" just before opening the
print preview. However, after printing or closing the print preview, that
worksheet is then left on "normal" view.

I also want the code to open the print preview as opposed to telling it to
print instead, in case they want to print on a printer other than their
default printer, or change print settings etc...

Any ideas would be much appreciated.

Many thanks, Neil

PS - The file returns to "normal" view upon closing it, so I'm not leaving
the learners' Excel stuck on full screen view! (some of them would not know
how to change it back)

 

[JW]

Dear all

Since I work with a lot of learners with reading/visual e.g. tracking
difficulties, I have customised my interactive workbook with VBA so that the
view changes to "full screen" upon opening it, in order to reduce the "visual
noise" of the ribbon etc (which they can't use due to protecting the
worksheet). However, one of the functions is that there are buttons which run
macros to generate a paper-based worksheet and then open the print preview....
and when the view is still "full screen", you can no longer click on the
buttons to then print it!

Does anyone know of some code that would ensure a "normal" view when the
print preview is opened, but that would make the worksheet return to "full
screen" view when the page is printed, or "close print preview" is pressed?

I have thought about keeping just that worksheet on "normal" view (using the
worksheet activate events), but I'd still rather that the visual noise is not
there at all, even on that one sheet. I also tried adding code so that when
the button is pressed, the view returns to "normal" just before opening the
print preview. However, after printing or closing the print preview, that
worksheet is then left on "normal" view.

I also want the code to open the print preview as opposed to telling it to
print instead, in case they want to print on a printer other than their
default printer, or change print settings etc...

Any ideas would be much appreciated.

Many thanks, Neil

PS - The file returns to "normal" view upon closing it, so I'm not leaving
the learners' Excel stuck on full screen view! (some of them would not know
how to change it back)

I don't have Excel2007, so I'm not sure how the Full Screen view works
in it, but have you tried incorporating a Do Events Statement in the
button that displays the Print Preview? Something like this should
work. Of course, the code may be a little different for 2007.

Sub Example()
Application.DisplayFullScreen = False
ActiveWindow.SelectedSheets.PrintPreview
DoEvents
Application.DisplayFullScreen = True
End Sub

 

[Neil Goldwasser]

Many thanks for your help JW! The code worked a treat!

However, after reading your comments about the differences between Excel
2003 & 2007, I did try running it in Excel 2003, and unfortunately ran into
an issue. Although the full screen view in 2007 still keeps the worksheet
tabs on display, it does NOT in 2003, meaning that the user loses the ability
to navigate the workbook, which I feel is more important than the visual
noise at the top. So I think, unless anyone knows of a way to keep the
worksheet tabs on display whilst Excel 2003 is in full screen mode, I may
have to abandon the idea of automatically switching the view to full screen.

Either that, or I may add a simple button to switch between views, so that
each individual worksheet can be viewed full screen in Excel 2003, but so
that this can be switched off whilst the user changes worksheet.

If I wanted to keep the print preview code in, I would definitely want it to
go to full screen view before the print preview, but to switch back to
whatever it was before afterwards i.e. not necessarily switching back to full
screen. For example, if the user had chosen a "normal" view prior to clicking
the button, it would want it to return to this instead.

Is there a way to alter the code so that Excel remembers what view it was in
before the button was pressed, and changes back to that afterwards?

Again, many thanks for your help, Neil