Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
print option
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
print option
This option is already there in the Print option provided by Excel.
See the 'Print Range' block... "jaz" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
print option
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" wrote: This option is already there in the Print option provided by Excel. See the 'Print Range' block... "jaz" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
print option
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 "jaz" wrote: 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" wrote: This option is already there in the Print option provided by Excel. See the 'Print Range' block... "jaz" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
print option
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 "Sheeloo" wrote: 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 "jaz" wrote: 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" wrote: This option is already there in the Print option provided by Excel. See the 'Print Range' block... "jaz" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
print option
or can i have a button on the sheet with print icon?
"Sheeloo" wrote: 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 "jaz" wrote: 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" wrote: This option is already there in the Print option provided by Excel. See the 'Print Range' block... "jaz" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
print option
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 "jez" wrote: or can i have a button on the sheet with print icon? "Sheeloo" wrote: 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 "jaz" wrote: 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" wrote: This option is already there in the Print option provided by Excel. See the 'Print Range' block... "jaz" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
print option
how about just using this:
application.Dialogs(xlDialogPrint).Show -- Gary Excel 2003 "jez" wrote in message ... or can i have a button on the sheet with print icon? "Sheeloo" wrote: 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 "jaz" wrote: 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" wrote: This option is already there in the Print option provided by Excel. See the 'Print Range' block... "jaz" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
print option
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" wrote: 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 "jez" wrote: or can i have a button on the sheet with print icon? "Sheeloo" wrote: 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 "jaz" wrote: 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" wrote: This option is already there in the Print option provided by Excel. See the 'Print Range' block... "jaz" wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
print option
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" wrote: 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 jez |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
print option
thank you very much for your help
have a good weekend jez "Sheeloo" wrote: 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" wrote: 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 jez |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
setting print ranges to print based on option | Excel Programming | |||
Y or N option to print 1 of 2 schedule | Excel Discussion (Misc queries) | |||
Print from Option Buttons | Excel Programming | |||
11 x 17 is not a print option for me | Excel Discussion (Misc queries) | |||
print option | Setting up and Configuration of Excel |