Trying to Print User-Selected Sheets from a button on one sheet.

[rattler418]

Hello, everyone.

I am new to this forum and vaguely familiar with VB (had an intro to it a while back), and I simply need to figure out why something isn't working like it should.

I have an Excel Workbook (2007) with approximately 20 sheets. I set it up so that it is navigable using buttons with macros behind them, and so that only one sheet is visible at a time -to move from one sheet to a different one, the user needs to go back to a main selection sheet (a "switchboard form", if you like). Each sheet has a button to print that sheet, and all of that works perfectly.

What I am trying to add is a button on the "MASTER" sheet that allows the user to select which sheet he/she wants to print without going to the sheet, and also allows them to print more than one sheet with a single action.

The best code I have found for this thus far was a piece that I am posting here. It purports to do exactly what I want, but hangs up just before the print. It is meant to create a dialog box on the fly with checkboxes representing the sheets in the workbook. The user selects one or more checkbox(es), then clicks "OK", which is supposed to print the selected sheets.

The error comes after the sheet(s) are selected, and "OK" is pressed.

Microsoft KB had a solution to this, but none of the workarounds (they list 4) seem to apply to what I am doing.

Unfortunately, I cannot see replies to this thread until I get home in the evenings (my network policy at work has blocked this site), and I will not be able to post the workbook because it contains Personally Identifiable Information (PII) on a number of people.

I can post the code that is giving me trouble, as it is already in the public domain and I will bold and CAP the line that is highlighted when I go into the debugger.

Any help on this is greatly appreciated.

The code follows:

Please Login or Register  to view this content.

---End of Code---

Thank you again for any help you can provide.

[arthurbr]

Also please read our rules concerning code tags. I will add them for you this time.

[Leith Ross]

Hello rattler418,

Welcome to the Forum!

It seems to me that is complicating a simple operation. You can add a UserForm to your VBA project with a ListBox and a CommandButton. The code below can be added to this UserForm.

Please Login or Register  to view this content.

Add the following code to the button on your on the "Master". This will display the user form. Change the name of the user form to match the name of your user form.

Please Login or Register  to view this content.

[snb]

Or you can simply use a validationlist; e.g. in cell C3

Please Login or Register  to view this content.

in the macromodule of worksheet 'Master':

Please Login or Register  to view this content.

[rattler418]

Thank you all for the correction and advice so far. I will try these out tomorrow at work and let you all know how it turns out.

Again, thank you.

rattler418