• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Print Macro for Selected Sheets from 200+ Worksheets

Scoop480

New Member
I posted this before but still have not received a solution. I am a VBA beginner and I'm not sure how to proceed. I have attempted to create a macro to "PrintSelected" sheets from over 200+ Active worksheets. I had a Macro to print based on checkboxes, but it wouldn't show all active worksheets since there are too many. I tried to use a List Box, but I'm not sure how to do it. I have created my list box, but I'm not sure of the code needed to trigger the List Box (SelectSheets) to pull the active sheets and print when selected. Can anyone help me with the code?


Thank you!
 

Hui

Excel Ninja
Staff member
Scoop480

I have made up a sample using a List Box on a Userform

The list box is scrollable and so will accommodate 200+ worksheets


The code loops through the worksheets

Adds the sheet names to the Listbox control

Gives control back to you

After you select the sheets on the userform, loops through the sheets again and prints those that are selected

I have also added the option to Print Preview without changing the code


Refer: https://www.dropbox.com/s/7wv0v7f07ahgqqb/Print_Using_ListBox.xlsm


Edit: Don't download this until you read my next post below, I have added some extra features, I think you'll like
 
Last edited:

Debraj

Excel Ninja
Hi Ian.. Marvelous..


Impressed only due to ListBox1.ListStyle = 1 and ListBox1.MultiSelect = 1..

Makes me fool, that How can someone add OptionBox/CheckBox in a ListBox.. :)

Learned a lot from YOU..


Regards,

Deb
 

Hui

Excel Ninja
Staff member
Scoop480


I have changed the list box to now have 3 Columns

These display the Page Name, Page Size and Page Orientation

The values are taken from each page

You can't change the values but it quickly highlights if a page is incorrectly setup


https://www.dropbox.com/s/7o0rwfws2c2m6v3/Print_Using_ListBox2.xlsm

Enjoy


ps: It also displays the Active Printer, which if it is wrong you can at least cancel the job

Also added the option to Reverse all Selections (Just in case)
 
Last edited:

Scoop480

New Member
Thank you Hui! This is absolutely perfect! However, there is only 1 change I would need. The default is to check all worksheets. Is there anyway to make the default unchecked for all worksheets? Again, I'm sure it's an easy fix, but I'm still too new to VBA for this one.
 

SirJB7

Excel Rōnin
Hi Scoop480!


I think Hui won't mind if I answer you. Sure he won't.


Go to the VBA editor (Alt-F11), in the left or upper left windows pane click on Forms under VBAProject(<this workbook name>), right button on UserForm1, now go to the right or big upper right pane, scroll down to the end of the code, locate the following code at the end of Sub UserForm1_Activate event, and change True to False of the .Selected(i) value, and then save the file:


-----

[pre]
Code:
'Enable all Listbox Selections
With ListBox1
For i = 0 To .ListCount - 1
.Selected(i) = True
Next
End With
[/pre]
-----


Regards!
 

SirJB7

Excel Rōnin
@Hui

Hi!

Nice, simple and useful user form. Borrowed to my personal workbook. Thanks.

Regards!
 

Hui

Excel Ninja
Staff member
Scoop480

The easier way is to just delete the whole section below from the end of the UserForm_Activate Sub

[pre]
Code:
'Enable all Listbox Selections
With ListBox1
For i = 0 To .ListCount - 1
.Selected(i) = True
Next
End With
[/pre]

The default behavior in the above file has been changed
 

JohnBi

New Member
Hallo,

I found this perfect samples.

Am I allowed to asked if there a possibility to print the selected worksheet as a single job with: page 1/5, 2/5, 3/5 and so on if I select a multi sheets to print.


Thanks and Regards

JohnBi
 

SirJB7

Excel Rōnin
@JohnBi

Hi!

Would you please start a new topic instead of writing on another user's one? It might be seen as hijacking. More indeed when it's such and old topic. If needed you could add a reference in your new one.

Perhaps you'd want to read the green sticky posts at this forums main page so as to know the guidelines that will lead to know how this community operates (introducing yourself, posting files, netiquette rules, and so on).

Regards!
 

JohnBi

New Member
Thank you for your help and both samples.

A lot of thing to learn at my side. A great help to my daily (boring) routine.


Kind Regards

JohnBi
 

jkwee

New Member
Hi Hui,


I'd like your help if possible with 2 main things:


- Hidden sheets: currently they are still showing as blanks in the list box, can we remove them from view?


- No of copies - ability to set quantity of copies


Are you able to help me with the revised code?


My email is jensen.kwee@gmail.com if you would like to email me...I can send you my workbook file too if I can email you?
 

SirJB7

Excel Rōnin
Hi, jkwee!


While you wait for Hui maybe you want to give a try to these tweaks:


1) For the hidden sheets, in the Sub UserForm_Activate() do this:


a) In the For...Next loop (add items to the listbox) change all the occurrences of:

-----

strArray(i, XXX) = YYY

-----

by:

-----

strArray(SheetCount, XXX) = YYY

-----


b) In the same subroutine after this:

-----

[pre]
Code:
'Load the Listbox
ListBox1.List = strArray
-----

add this:

-----

[code]With ListBox1
For i = .ListCount - 1 To 0 Step -1
If .List(i, 1) = "" Then .RemoveItem i Else Exit For
Next i
End With[/pre]
----


2) For the copies option, just add a control (text box, scroll bar, number control,...) to the userform and then in "Private Sub PrintButton1_Click()" change this:

-----

ActiveSheet.PrintOut 'Printout
-----

by this:

-----

ActiveSheet.PrintOut ,,<ControlAdded>.Value 'Printout[/code]

-----


Hope it helps and that Hui doesn't mind.


Regards!
 

jkwee

New Member
Hi sorry I'm not good with VBA, YET!


Could I email you my workbook and you can tweak the code and send the workbook back to me?
 

SirJB7

Excel Rōnin
Hi, jkwee!


If you want to post your email I'll get back to you, even since I did it before I don't post anymore my email address as I explained here why:

http://chandoo.org/forums/topic/locking-the-cell-automaticall-once-the-date-passed#post-21903


Otherwise you should consider uploading a sample file (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.


Or if you prefer wait and see if Hui uploads an update (but don't tell him that I suggested you so), or maybe I could upload a fixed version later.


Regards!

Regards!
 

jkwee

New Member
My email is in these posts: XXXXXXXXXXXXXXXXX


EDITED (SirJB7)


Email address removed regarding privacy, if you want to keep it posted just advise and I'll restore it.
 

Hui

Excel Ninja
Staff member
JKwee

I have made a few small changes to the userform interaction since yesterday to improve its use

Use same download link
 

SirJB7

Excel Rōnin
Hi, Hui!


Good job, I like it very much... thank you!


But it still lacks of the most useful feature, at least for me it's the most annoying thing about printing: wouldn't it be possible for you to fix it so as it monitors and autoloads sheets from the reams that are under the printer table when there are no more in the feeder?


:p


Regards!
 

jkwee

New Member
Hi guys,


Looks good...However when I drag the Print Window around on my screen, there is a massive lag/trail....are you able to fix this?


Hope you know what i mean....try dragging the Print Window around yourself...
 
Top