print option

[jaz]

i have a report sheet which includes 3 pages but not everybody needs 3 pages
to print
is there a code that once i click print then it would give me an option eg:

please select an option
1. for 1st page
2. 2nd page
3. 3rd page
4. all the pages

many thanks
jez

 

[Sheeloo]

This option is already there in the Print option provided by Excel.

See the 'Print Range' block...

 

[jaz]

sheeloo
i have people that dont know how to do that therefore i want it something
else more flashy a pop up basically, that makes it easier for them once they
click print object then they select 1,2,3, etc

 

[Sheeloo]

Try this macro
Sub printPages()
Dim response As Integer
Dim msg As String
msg = msg & "Please select an option: " & vbCrLf
msg = msg & " 1. for 1st page: " & vbCrLf
msg = msg & " 2. 2nd page: " & vbCrLf
msg = msg & " 3. 3rd page: " & vbCrLf
msg = msg & " 4. all the pages: " & vbCrLf

response = Application.InputBox(msg)
If (response <> 1 And response <> 2 And response <> 3 _
And response <> 4) Then
MsgBox "Wrong Choice!" & vbCrLf & "Try again."
Exit Sub
End If
If response = 4 Then
ActiveSheet.PrintOut 1, 3
Else
ActiveSheet.PrintOut response, response
End If
End Sub

 

[jez]

sheeloo
thank you very much for that and it does work fantastic but only if i go to
tools-macros-run macro
is there any other way to do it like when i click printer icon?
sorry am rubish with macros and thanks for taking the time to help
much appreciated
jez

 

[jez]

or can i have a button on the sheet with print icon?

 

[Sheeloo]

On the Control toolbar
Click on Command Button
Then draw one where you want
Right-Click and paste the code in the View Code window
In properties (Categorized view) click on ... next to picture and choose the
image you want
Click on Design button on the Control toolbar to exit design mode

 

[Gary Keramidas]

how about just using this:

application.Dialogs(xlDialogPrint).Show

 

[jez]

absolutely stunning, thanks for all the help honestly but i have one more
question
i have 5 different sheets in a workbook and they have different amount of
pages eg, report sheet 3 pages, accounts sheet 2 pages and counting sheet 8
pages
can i do it like select report option 1 and then print 1-3 (which takes you
to report sheet) or counting option 2 and then print all pages (which takes
you to counting sheet)
when you get a chance please reply
you are the hero so far
jez

 

[Sheeloo]

Here is the updated macro
Insert a sheet named Sheet1 (or you can name it whatever and change Sheet1
below to that name). You can try to hide that sheet too.
in Col A enter the sheet names you want the users to be able to print
in Col B enter the total pagenumbers for the sheet in corresponding cell in
Col A
Max 20 sheets
Sub printPages()
Dim i, j As Integer
Dim shName(20), nPages(20) As String
Dim response, response2 As Variant
msg = "Please select the sheet to print" & vbCrLf & vbCrLf
j = Sheets.Count - 1
For i = 1 To j
shName(i) = Sheets("Sheet1").Cells(i, 1).Value
nPages(i) = Sheets("Sheet1").Cells(i, 2).Value
msg = msg & i & " to print : " & shName(i) & vbCrLf
'MsgBox shname & " has " & nPages & " pages."
Next
response = InputBox(msg)

If response = "" Then
Exit Sub
Else
response = CInt(response)
End If

If response < (j + 1) Then
msg = "Please type the number of pages to print from: " & _
shName(response) & vbCrLf & vbCrLf
msg = msg & "Enter 0 to print all pages" & vbCrLf
For i = 1 To nPages(response)
msg = msg & "Enter " & i & " to print page number: " & i & vbCrLf
Next
response2 = InputBox(msg)
If response2 = "" Then
Exit Sub
Else
response2 = CInt(response2)
End If
Else
MsgBox "Wrong choice. Pl. choose again."
Exit Sub
End If
If response2 > nPages(response) Then
MsgBox "Wrong choice. Pl. choose again."
Exit Sub
End If

If response2 = 0 Then
ActiveSheet.PrintOut 1, nPages(response)
Else
ActiveSheet.PrintOut response2, response2
End If
End Sub

 

[jez]

thank you very much for your help
have a good weekend
jez