ListBox to Print Range of Sheets

[Proj_Eng]

Hi Everyone,

I copied the following code from an example I found online. I modified it so that it would work in my workbook. I have the listbox in a userform, rather than in a sheet as in the example I took this from. I have a button on my primary (first) sheet which then calls a userform that contains the list box. Everything is working perfectly except the print preview. When I walk through the code I get a "Run-time error '13': type mismatch" error at the line indicated in my code, below. I'm not real familiar with VBA, so I'm finding examples on line and attempting to modify them in my sheet to get the outcome I desire. I have a basic understanding of this type of error after googling it, but don't know how to fix it. Any help is appreciated. I changed the With statement in the code which originally had "ActiveSheet.ListBoxSh" rather than "PrintBox.ListBoxSh". My userform is called PrintBox, so it seemed appropriate to make this change. I don't know if this is part of my problem or not.

Also, I can select the sheets I want once the userform pops up, but I then want to link the print/print preview to another button on the userform. I think this will require me to rewrite the "Sheets(SheetArray()).PrintPreview/PrintOut so that they are activated when I click the button(s) on the userform. How would I accomplish this?

Please Login or Register  to view this content.

[jolivanes]

You never tell us what you want to do.
Do you want to populate a ListBox on a UserForm with all the Sheet names of your workbook and when you select one of the sheets you want to preview the PrintRange?
Has the Print Range been set before?

[Proj_Eng]

Jolivanes,

Sorry, yes, I want to preview the selected sheets in my workbook and then print them by selecting buttons on my userform. Let me know if you need additional information. This is rather new to me.

[jolivanes]

Can you explain the logic of printpreview followed by printing.
If you're going to print for sure, why printpreview?

[jolivanes]

See attached.

You have a UserForm named UserForm1
Caption: Select Sheets to Preview or Print

On the UserForm you have
1) ListBox named lbSheets
2) CommandButton named cmb1
3) CommandButton named cmb2
4) CommandButton named cmb3

ListBox (lbSheets)
ListStyle is set to "1 - fmListStyleOption"
MultiSelect is set to "1 - fmMultiSelectMulti"

CommandButton (cmb1)
Name: cmb1
Caption: Print Preview

CommandButton (cmb2)
Name: cmb2
Caption: Print

CommandButton (cmb3)
Name: cmb3
Caption: Exit

Sheet1 has a Button (Form Control)

[Proj_Eng]

I want the option of previewing before I print so that I can review the report before printing.

[jolivanes]

So does the attachment in Post #5 do what you want?

[Proj_Eng]

I haven't had a chance to look at it. I will try to get to it, soon, and let you know.

[Proj_Eng]

Jolivanes,

This is awesome! This is just what I was after. Thanks for the help.

[Nikhil9999176167]

Hey, I read your post and this is awesome. Can you help me with a small tweak? I am novice to VBA and thought if I could get some help and it will be highly appreciated.

Per the same post, I was trying to create a replica but for a different reason.

Problem -
I have an excel file with maybe 5 interlinked sheets.
I want to replicate these sheets maybe 5 more times (30 sheets in total)
I was able to find a macro which replicates a single sheet multiple times but not all taken together. The issue with this approach is that the linking gets retained to the original tabs and not to the new tabs. But if someway I am able to replicate all the tabs at once, excel modifies the linking to newly created tabs. Per my best estimate, we will need to define the selected worksheets in an array but not too sure.

What I am looking for is that the macro gives me a list box similar to yours, allows me to select a few sheets (5 or lesser that I already have in the WB, gives me an option of how many times do I want to replicate and then replicates all sheets taken together at once and not individual sheets.

I have attached a sample copy for reference wherein in different modules you will find all the different codes that I found on the internet.

In the current version of file, you will find that sheets have been replicated, but sheet "B (2)" for example links back to sheet A, however when you select tabs A, B manually and then create copy of the tabs, you will find that the links are different as can be seen on tabs - "B (3)" and "C (3)" respectively which are the copies created manually.

Thanks a lot in advance for help.

Thanks and regards
Nikhil Kumar

[jolivanes]

@Nikhil9999176167
Please read the forum rules, that should say read them again. Rule #4 is of interest to you in this case. Don't just read that rule only. It doesn't hurt to know all of them.
In your new thread, refer to this thread if you think it helps.
However, without looking at your attachment, I would say that your explanation has to be a little more understandable for most of the people.
Good Luck