Best way to do this? Expert advice needed...

mlopes1

Board Regular
Joined
Jul 14, 2002
Messages
114
I am trying to come up with the best way to do this and figured there was no better place to get some ideas than here.

I have a workbook with 15 or so worksheets. On the main sheet, where most data is inputed, I have a button that will run a print macro. I would like for the user to have a choice of which worksheets to print when they click the button. What would be the best way to do this? Can a form pop-up? I am not too familiar with the VBA behind it either, but am willing to try the best solution regardless of the the code involved. Thanks in advance,

Mark
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
Here is a watered-down version of what will work for you:

SheetToPrint = InputBox("What worksheet would you like to print?")
Sheets(SheetToPrint).Print

I would suggest using a ListBox or perhaps even a bunch of CommandButtons.

SheetToPrint = ListBox1.Value

Private Sub CommandButton1_Click()
Sheets("Whatever").Print
End Sub

Does this point you in the direction you wish to go?
 

kskinne

Well-known Member
Joined
Feb 17, 2002
Messages
1,267
Office Version
  1. 365
Platform
  1. Windows
The following code should work for you:

Application.ScreenUpdating = False
Worksheets("sheet2").Activate
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True
Worksheets("sheet1").Activate
Range("A1").Select
Application.ScreenUpdating = True

In this example, it selects sheet2, prints sheet2, then returns to sheet. You could do one of these macros for each of your sheets, then place buttons on your main page to assign a macro to each one, or a better way would be to have an input box prompt you to enter the name of the sheet you wish to print - but I am not exactly sure on how that would be done. I am sure someone else on this board can probably help you better that I can, but this should at least give you a start.

HTH
kevin
 

Jay Petrulis

MrExcel MVP
Joined
Mar 17, 2002
Messages
2,040
Hi,

I would create a UserForm that lists the sheets and allows you to select the ones you want to print.

Have a listbox on the form and do something like the following in the form's initialize event<pre>Private Sub UserForm_Initialize()
Dim ws As Worksheet

With Me.ListBox1
.ListStyle = fmListStyleOption
.MultiSelect = fmMultiSelectMulti
End With

For Each ws In ThisWorkbook.Sheets
Me.ListBox1.AddItem ws.Name
Next ws

End Sub</pre>

You can predetermine the listbox properties, so the above listbox settings may not be needed.

Also, you can preselect and/or exclude worksheets as well.

You can then cycle through the selected items to print.



_________________
Bye,
Jay
This message was edited by Jay Petrulis on 2002-08-29 09:42
 

gwkenny

Well-known Member
Joined
Aug 13, 2002
Messages
565
While my preference would be with these guys and do a UserForm with a listbox it may be easier just to list the sheet names on the first sheet (say Column A).

Then the next column (let's say Column B) is your print indicator column. User places an X next to the sheets he/she wants to print.

Thus, all you have to really code is reading if there is an X next to the sheet name. The code for printing you can get from the macro recorder.

g-
 

gwkenny

Well-known Member
Joined
Aug 13, 2002
Messages
565
While my preference would be with these guys and do a UserForm with a listbox it may be easier just to list the sheet names on the first sheet (say Column A).

Then the next column (let's say Column B) is your print indicator column. User places an X next to the sheets he/she wants to print.

Thus, all you have to really code is reading if there is an X next to the sheet name. The code for printing you can get from the macro recorder.

g-
 

Forum statistics

Threads
1,147,510
Messages
5,741,574
Members
423,668
Latest member
Audorin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top