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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

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!
 
Upvote 0

Norie

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

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
5,271
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
 
Upvote 0

Forum statistics

Threads
1,186,326
Messages
5,957,222
Members
438,294
Latest member
Soikeo79

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