hiding sheets but still letting macros run when printing hidden sheets

**stevesunfold** · 11-10-2008, 04:35 PM

hi guys
i have a workbook that has the following sheets

working sheet
job sheet
receipt of deposit letter
completion sheet
delivery note
delivery note (2)
odd
even
t&t
glass
ggf

i want to hide every sheet except the working sheet.
I have tried this but the macros bring up an error when i run the macro

my macros involve printing certain pages dependng on what button is pressed

i get an error whatever
how do i stop this

**royUK** · 11-10-2008, 04:40 PM

You need to unhide the sheet

Please Login or Register  to view this content.

**stevesunfold** · 11-10-2008, 04:53 PM

thanks for the reply roy
how ould i incorporate that code into this?

Please Login or Register  to view this content.

many thanks

**stevesunfold** · 11-10-2008, 05:57 PM

if anyone could help it would be appreciated

**Paul** · 11-10-2008, 06:16 PM

Steve, does this work for you?

Please Login or Register  to view this content.

**stevesunfold** · 11-10-2008, 06:26 PM

hi pjoaquin
thanks for the reply
it works except for the fact that if i select no it shows the delivery note tab but doesnt mae it the active sheet
so i guess i should leave that particualr sheet visible?

what it should do is take me to sheet "delivery note" cell b16
asking me to input what system im using

steve

**Paul** · 11-10-2008, 07:04 PM

I would think you could just add:

Please Login or Register  to view this content.

immediately after the 'Case vbNo' statement. This, of course, would leave it open after the user edited the cell, so you may as well leave it visible.

**stevesunfold** · 11-10-2008, 07:36 PM

hiya
im getting this really annoying pop up half way through the macro that says "all worksheets are empty" and then waits for you to click ok and then progresses is there anyway to stop this?

regards

**pr4t3ek** · 11-10-2008, 08:04 PM

you may need to restart your comp...

**Paul** · 11-10-2008, 10:35 PM

You can add this at the beginning:

Please Login or Register  to view this content.

and this at the end:

Please Login or Register  to view this content.

BUT- this message is probably just telling you that there's nothing to print on one or more of the worksheet(s) in question. You may want to step through the code line-by-line to see which sheets are causing this (if it matters to you).

**stevesunfold** · 11-11-2008, 03:31 AM

hiya

it still does it but thats not really a big worry
the problem is that it doesnt do the call SelectSheets part of the macro

**royUK** · 11-11-2008, 04:57 AM

What is SelectSheets?

**royUK** · 11-11-2008, 05:04 AM

This has tidied up the code, but you need to test it. I can't see why you are calling a macro called SElectSheets when the sheets are selected.

Please Login or Register  to view this content.

**stevesunfold** · 11-11-2008, 01:22 PM

hi roy
i get an instant error if i click no and it highlights this line

Please Login or Register  to view this content.

the call selectsheets

is a way of checking a box that requires me to select any of 3 boxes that i may need and it prints off the selected sheet(s)

**Paul** · 11-11-2008, 01:47 PM

Notice the capitalization of "Delivery note". Based on other code, my guess is your worksheet name is actually "delivery note".

**royUK** · 11-11-2008, 02:08 PM

If paul is right then try using Option Compare Text

Please Login or Register  to view this content.

**stevesunfold** · 11-11-2008, 02:09 PM

Hi pjoaquin
no the sheet is definetlly named "Delivery note"
as soon as i click the no button it brings up

run time error '1004'
select method of range class failed

and highlights the row previously noted

**royUK** · 11-11-2008, 02:23 PM

Maybe if you use GoTo

Please Login or Register  to view this content.

**stevesunfold** · 11-11-2008, 02:57 PM

hi roy
that works well and takes me to the delivery note tab but when i click back OR if i click yes i get this message

run time error 1004
select method of sheets class failed

**royUK** · 11-11-2008, 03:04 PM

It should select the sheets in the array & print them.
I don't think that you have said what SelectSheets does.

**stevesunfold** · 11-11-2008, 03:14 PM

select sheets brings up a pop up asking to select
odd
even
t&t

it has checkboxes that allows me to select any or all of them and then prints the checked sheets

and the error code highlights this line

Please Login or Register  to view this content.

select sheets is actually this code if it helps

Please Login or Register  to view this content.

**stevesunfold** · 11-12-2008, 01:28 PM

bump bump bump

**royUK** · 11-12-2008, 02:39 PM

I think that macro is causing the problem. It will certainly print sheets twice.

**stevesunfold** · 11-13-2008, 03:37 AM

no roy its not that

the code that you gave me

now doesnt bring up the pop up of selectsheets

**royUK** · 11-13-2008, 04:35 AM

I've just noticed that you are using a DialogSheet. Why, these were replaced by UserForms? I have posted a UserForm to select sheets to print if you serch the forum.

I think one of the main problems with this project is that you have so many different sources for the code some of which duplicate or conflict.

hiding sheets but still letting macros run when printing hidden sheets

LinkBack

Thread Tools

Rate This Thread

Display

hiding sheets but still letting macros run when printing hidden sheets

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions