Avoid using Select method with worksheets in VBA macro. Help please.

[Annie Bender]

Hi all

The application: VBA macro in which various Excel worksheets get printed, depending on the user's print selection via radio buttons.

The problem: As part of the macro, the print range has to be set, and the number of pages in the printed report echoed to the user, both of which depend on the relevant worksheet selected for printing. My coding for this is:

Print_Range = "A8:I" & ActiveCell.SpecialCells(xlLastCell).Row
dPages = ExecuteExcel4Macro("GET.DOCUMENT(50)")

where both Print_Range and dPages are declared variables, and both operate correctly. EXCEPT, that as the user selects successive sheets to print, neither the Print_Range nor the dPages variables change, unless for each radio button option, the relevant sheets("name").selected is included. That makes sense to me, but I don't really want the worksheets to be changing on the screen all the time as the user selects a different sheet to print. Is there a correct way to let the macro know which sheet to test for xlLastCell and ExecuteExcel4Macro() without that sheet displaying on the screen?

I hope I've explained this clearly. Thank you guys for bringing me along in VB.

Annie

[Annie Bender]

Hi, Is there no way to do this?

In my search I have come across the syntax:

Pages = ExecuteExcel4Macro("Get.document(50,""Sheet2"")")

for getting number of pages in Sheet2, even if Sheet2 is not active. But when I try this without Sheet 2 being active (.Activate or .Select), it returns an Error 2042, something about a "type mismatch". I don't know how to cure this, since no matter how I declare the Pages variable, I get the same error.

What am I doing wrong here? Thanks.

Annie