VBA to print variable number of copies AND different graphs

Jase L

New Member
Joined
Sep 16, 2006
Messages
13
I have been trying to get this to work for a while now yet have not been able to get the two to work together. Currently I have a drop down box to select number of copies (Between 1 and 20) and option buttons to select the graphs to print (the graphs are there own worksheets). I'm still learning the ropes with VBA, and would love some ideas on how to do this.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Jase L

New Member
Joined
Sep 16, 2006
Messages
13
Code:
Private Sub CancelButton_Click()
Unload UserForm2
End Sub

Private Sub OKButton_Click()
Dim Cell As Range
If All Then
    Sheets("Quality Graph").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Sheets("TCHr Graph").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Sheets("AHT Graph").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Sheets("PQ Graph").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Sheets("Trends Graph").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
If QualityGraph Then
    Sheets("Quality Graph").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
If TCHrGraph Then
    Sheets("TCHr Graph").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
If AHTGraph Then
    Sheets("AHT Graph").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
If PQGraph Then
    Sheets("PQ Graph").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
If PQTrendsGraph Then
    Sheets("Trends Graph").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If

Sheets("Generate").Select
    Range("F2").Select
End Sub
This is what I have so, however I can't manage to add the numbers of copies in.. any ideas at all would be appreciated!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Where do you need to add the number of copies?
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,966
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Code:
Private Sub CancelButton_Click()
Unload UserForm2
End Sub

Private Sub OKButton_Click()
Dim Cell As Range
If All Then
    Sheets("Quality Graph").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Sheets("TCHr Graph").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Sheets("AHT Graph").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Sheets("PQ Graph").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Sheets("Trends Graph").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
If QualityGraph Then
    Sheets("Quality Graph").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
If TCHrGraph Then
    Sheets("TCHr Graph").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
If AHTGraph Then
    Sheets("AHT Graph").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
If PQGraph Then
    Sheets("PQ Graph").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
If PQTrendsGraph Then
    Sheets("Trends Graph").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If

Sheets("Generate").Select
    Range("F2").Select
End Sub
This is what I have so, however I can't manage to add the numbers of copies in.. any ideas at all would be appreciated!

Suggestion 1:

Give the controls a prefix so it is obvious what they are. For example, your option buttons ought to have names like optAll and optQualityGraph. This makes it easier for someone else to understand the code, and for you to understand it next month when you want to fix it up.

Also, when using the value of the control, state it explicitly. I know Value is default for an option button, but this:

If optAll.Value Then

is more understandable than

if All then

Suggestion 2:

Don't select different objects and then work on the selected object. It's slower and causes lots of screen flicker. Change this:

Sheets("Quality Graph").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

to this:

Charts("Quality Graph").PrintOut Copies:=1, Collate:=True

3. What's the name of the dropdown with the quantity? Assuming it's cboPrintQuantity, then:

Charts("Quality Graph").PrintOut Copies:=cboPrintQuantity.Value, Collate:=True
 

Forum statistics

Threads
1,136,650
Messages
5,676,996
Members
419,667
Latest member
MegEri

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