Error "Subscript out of Range" When Printing More than 7 Sheets to PDF

A form has been designed to dynamically generate checkboxes for all active, visible sheets in the workbook, enabling users to select specific sheets for PDF printing. However, when more than 7 sheets are selected, the process fails with a "Subscript out of Range" (Error 9).

Various attempts have been made to resolve this issue, but the solution for handling more than 7 sheets remains elusive. Assistance is needed to identify the necessary changes in the code to ensure it functions correctly with a larger selection of sheets.

The subroutine responsible for PDF generation can be found under "cmdOk_Click." The file with the module is attached for reference. Any guidance would be appreciated.

Thanks!

Please Login or Register  to view this content.

PDF_Book1.xlsm

When you have an error like this, disable
On Error
because it hides the error. When I remove it, it takes me to this line of code

Please Login or Register  to view this content.

When I dump the contents of the array, the first element is
SELECT ALL
which is not the name of an existing sheet. That causes the subscript error.

You are collecting sheet names based on the name of the checkboxes.

Please Login or Register  to view this content.

Your checkbox for SELECT ALL is named ChkAll but you are testing for chkAll. Text comparisons are case-sensitive.

Changing this line of code will resolve the problem.

Please Login or Register  to view this content.

By the way the best practice for design is to define your UserForm as a user interface layer, and then put your business logic in separate modules. This design puts all the logic in the UserForm.

That's a different problem than your initial report of a runtime error. I'll look into it.

You are batching sheets in groups of 10, not 7. I am able to print 10 sheets with no problem. The problem occurs with more than 10.

The problem is that you are using the same file name every time, so you are overwriting the file. The last batch is the last time it's written.

Please Login or Register  to view this content.

Also, you have this comment:

Please Login or Register  to view this content.

but this is not correct. I have not found any limit. I think your whole approach can be greatly simplified to print all selected sheets at once.

Try running this demo code, which shows that you can select all sheets in the file:

Please Login or Register  to view this content.

Originally Posted by pacosalasv

Test 4: If "Select All" is unchecked, and I select 11, it only prints sheet 11.

I explained this one already. The code prints all batches to the same file. Did you modify the code to print each batch to a different file?

I would totally rewrite your code but I don't have time to do that today. I can revisit tomorrow.

If you want to do away with checkboxes, put this code in a module and run it.

Please Login or Register  to view this content.

I would probably do this with a multiselect listbox instead of dynamically creating checkboxes or other controls. Will revisit when time permits, but I think it needs an overhaul.

Which post are you referring to Jeff?
The code in Post #8 creates a UserForm with a Label, Multiselect Listbox and 2 (two) command buttons, one each for selecting some visible sheets and one to save all visible sheets to pdf.
The Listbox is populated with visible sheets only. The first sheet is not included because I have it as a data sheet
It is just another possibility. Normally one would have have a UserForm with all the required controls instead of generating it with code.
It might be a problematic code for someone with less than 100 posts, if that indeed shows the experience, but like I said, something to play with.
Help is only a question away.

Originally Posted by jolivanes

Normally one would have have a UserForm with all the required controls instead of generating it with code.

That would be my personal preference. I am not a fan of dynamically creating controls when everything you need can be done statically, and I'm also not a fan of writing code to write code due to the added layer of difficulty in debugging.

OK. You convinced me.

See attached

Thank you for the update and the kind words.
Good luck