Stop pages showing

**stevesunfold** · 01-03-2009, 09:31 AM

hi guys
i have this code
that starts on a sheet called working sheet
i want it to stay on working sheet asit prints out the selected sheets below.
is there anyway to stop this from showing the sheets? ie stay on working sheet

Please Login or Register  to view this content.

**royUK** · 01-03-2009, 09:48 AM

Don't select the sheets

Please Login or Register  to view this content.

**Andy Pope** · 01-03-2009, 09:50 AM

try

Please Login or Register  to view this content.

**stevesunfold** · 01-03-2009, 09:55 AM

hi guys
and thanks
not quite sure how to use yours roy

andy
yours still goes through showing each sheet

**Andy Pope** · 01-03-2009, 10:03 AM

Add these to lines to stop screen updating.

Please Login or Register  to view this content.

**stevesunfold** · 01-03-2009, 10:04 AM

roy
i need 2 copies of Receipt of deposit letter
how do i change you code for that
as im guessing it ould only print one

**stevesunfold** · 01-03-2009, 10:09 AM

andy
that works
thanks

**stevesunfold** · 01-03-2009, 10:18 AM

im still having a little trouble as i have this code
and oonce it gets to call select sheets ( im guessing thats why )
it jumps to a sheet called ggf and i dont know why
i want it to work its way through the macro and stay on working sheet

print supply only macro

Please Login or Register  to view this content.

the call select sheets code looks like this

Please Login or Register  to view this content.

id appreciate any help
regards

**Andy Pope** · 01-03-2009, 10:26 AM

You are selecting sheets throughout the code.

Why not add a line that selects the 'Working sheet' just before the screenupdating is turned back on again.

**stevesunfold** · 01-03-2009, 10:33 AM

hi andy
sorry to be a pain
but im not sure what you mean
there is no select sheet ggf
so why is it jumping to that sheet?

**Andy Pope** · 01-03-2009, 10:41 AM

In the loop you set a reference for CurrentSheet. One of the names you test for is GGF. This would suggest that the sheet is processed at some point. If it happens to be the last sheet processed the object variable CurrentSheet will still have a reference to it. So when you use the code at the end to activate CurrentSheet the sheet GGF will be on top.

Instead explictly re activate the Working sheet.

Please Login or Register  to view this content.

**stevesunfold** · 01-03-2009, 10:49 AM

cheers andy
i sort of understand what you are saying now
but i have put your code into and its still going to the ggf page
my code is now

Please Login or Register  to view this content.

**Andy Pope** · 01-03-2009, 11:00 AM

You have placed the code in the wrong place.

Please Login or Register  to view this content.

If still not working can you post an example workbook. The important thing is the sheet names and their order.

**stevesunfold** · 01-03-2009, 11:05 AM

no andy its still not working
and the file is 4.5 mb so its rather huge

**Andy Pope** · 01-03-2009, 11:11 AM

As I said the important thing is the sheet names and their order. For debugging purposes each sheet only needs something in 1 cell so it will print.

**stevesunfold** · 01-03-2009, 12:00 PM

hi andy
ive attached it as virtually blank

if you use the print s/o button on the working sheet thats the one im working on

many thanks

**Andy Pope** · 01-03-2009, 12:31 PM

and the password ??

**Andy Pope** · 01-03-2009, 01:03 PM

The problem is you use the same variable to store the active sheet and each sheet within the loop, CurrentSheet.
And you activate the object just prior to displaying the Print dialog.
I have added code to re-activate the working sheet.

Please Login or Register  to view this content.

**stevesunfold** · 01-03-2009, 01:16 PM

andy
thank you
that works but it still does the same when i have to select the operating sheets for number of doors

**stevesunfold** · 01-04-2009, 02:45 AM

bump bump bump

**stevesunfold** · 01-04-2009, 03:35 AM

for anyone who may be kind enough to help me

im having trouble with a code that asks for operating sheets for number of doors

this pop up requires me to select from several different sheets
what i dont want the code to do is show he sheets its printing as it prints

i want it to stay on "working sheet"

if anyone could help id be very grateful

**broro183** · 01-04-2009, 04:27 AM

hi Steve,

This is untested &, because I have never used "dialogsheets" before, my suggestions may not work (aka "be way off the mark"!

). Anyway, it's worth a shot so here's a revised version of Andy's last posted code with a few changes (marked by hash symbols)...

Please Login or Register  to view this content.

When I googled to find out what Dialogsheets are I came across the below explanation

Microsoft Excel version 5.0 dialog sheets have been replaced by UserForms. It is recommended that you use forms rather than Microsoft Excel version 5.0 dialogs in your custom application. UserForms have more programmable features and enable you to use ActiveX controls (.ocx files). Although the use of UserForms is recommended over the use of Microsoft Excel version 5.0 dialog sheets, dialog sheets can still be used for backward compatibility with earlier versions of Microsoft Excel.

Source: http://support.microsoft.com/kb/154930
Excuse my ignorance here, but would with the above in mind it be better for you to use a userform rather than a dialogsheet?
Or do you need the compatability with older versions of Excel?

hth
Rob