Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Printing Seperate Graphs
Hi All,
I have six worksheets that go together to make my workbook, Sheet 2 consists of 5 graphs, each one on a seperate page, the graph are populated by data on sheet 6. Sheet 1 is my main menu Sheets 3 - 5 will also be populated at a later date with graphs fro data on sheet 6. What I would like to do is write code for a command button that wil allow me to print out the graph that is on that particular page not th whole worksheet which is all I can do at the moment. Can somebody please help? Thanks Numa -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Printing Seperate Graphs
Dim grphs(1 to 5) as Range
Dim i as Long, rng as Range with worksheets("Sheet2") grphs(1) = .Range("A1:J12") grphs(2) = .Range("K1:T12" grphs(3) = .Range("A13:J24") grphs(4) = .Range("K13:T24") grphs(5) = .Range("A25:J36") End With for i = 1 to 5 if not intersect(ActiveCell,grphs(i)) is nothing then set rng = grphs(i) exit for end if Next rng.Printout -- Regards, Tom Ogilvy "Numan " wrote in message ... Hi All, I have six worksheets that go together to make my workbook, Sheet 2 consists of 5 graphs, each one on a seperate page, the graphs are populated by data on sheet 6. Sheet 1 is my main menu Sheets 3 - 5 will also be populated at a later date with graphs from data on sheet 6. What I would like to do is write code for a command button that will allow me to print out the graph that is on that particular page not the whole worksheet which is all I can do at the moment. Can somebody please help? Thanks Numan --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Printing Seperate Graphs
Thanks for the help Tom but it still seems not to work.
I keep getting the message: Run-time error 9 Sub script out of range This is how I have intergrated your code: Private Sub CommandButton8_Click() Dim grphs(1 To 5) As Range Dim i As Long, rng As Range With Worksheets("Sheet2") grphs(1) = .Range("A1:M31") grphs(2) = .Range("A32:M62") grphs(3) = .Range("A63:M93") grphs(4) = .Range("A94:M124") grphs(5) = .Range("A125:M155") End With For i = 1 To 5 If Not Intersect(ActiveCell, grphs(i)) Is Nothing Then Set rng = grphs(i) Exit For End If Next rng.PrintOut End Sub I have substituted the ranges that you gave as my graphs are slightl larger. Much thanks again if you can help further. Numa -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Printing Seperate Graphs
I put this code in the Sheet2 code module (where both the graphs and
commandbutton would be located). I did correct an error in assigning the range to the grphs(i) variable. Subscript out of range would usually occur because you don't have a sheet name Sheet2. Adjust Sheet2 to reflect the name of your sheet. Private Sub CommandButton8_Click() Dim grphs(1 To 5) As Range Dim i As Long, rng As Range With Worksheets("Sheet2") Set grphs(1) = .Range("A1:M31") Set grphs(2) = .Range("A32:M62") Set grphs(3) = .Range("A63:M93") Set grphs(4) = .Range("A94:M124") Set grphs(5) = .Range("A125:M155") End With For i = 1 To 5 If Not Intersect(ActiveCell, grphs(i)) Is Nothing Then Set rng = grphs(i) Exit For End If Next rng.PrintPreview End Sub The above worked fine for me (as described). -- Regards, Tom Ogilvy "Numan " wrote in message ... Thanks for the help Tom but it still seems not to work. I keep getting the message: Run-time error 9 Sub script out of range This is how I have intergrated your code: Private Sub CommandButton8_Click() Dim grphs(1 To 5) As Range Dim i As Long, rng As Range With Worksheets("Sheet2") grphs(1) = .Range("A1:M31") grphs(2) = .Range("A32:M62") grphs(3) = .Range("A63:M93") grphs(4) = .Range("A94:M124") grphs(5) = .Range("A125:M155") End With For i = 1 To 5 If Not Intersect(ActiveCell, grphs(i)) Is Nothing Then Set rng = grphs(i) Exit For End If Next rng.PrintOut End Sub I have substituted the ranges that you gave as my graphs are slightly larger. Much thanks again if you can help further. Numan --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Printing Seperate Graphs
Hi Tom,
Again thanks, I can get it to work with limited results, Every time I assisgn the code (with the new worksheet name - thanks for that) to one of the 5 print buttons (next to the five graphs) it always prints off the first graph only. E.G press print button 4 (for graph 4) it prints off graph 1. You mentioned the word module. I don't have any custom made modules, I just have my normal 6 woorksheets and This workbook. Again, any help would be greatly received. Numan --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Printing Seperate Graphs
What I would like to do is write code for a command button that will
allow me to print out the graph that is on that particular page not the whole worksheet which is all I can do at the moment. I interpreted this to mean you wanted one button that printed the approriate "pages/graphs" based on where the user had the active cell. If you want five buttons, then just put in simple code like Private Sub CommandButton1_Click() Worksheets("Sheet2").Range("A1:M31").Printout End Sub Private Sub CommandButton2_Click() Worksheets("Sheet2").Range("A32:M52").Printout End Sub repeat for the other 3 ranges. -- Regards, Tom Ogilvy "Numan " wrote in message ... Hi Tom, Again thanks, I can get it to work with limited results, Every time I assisgn the code (with the new worksheet name - thanks for that) to one of the 5 print buttons (next to the five graphs) it always prints off the first graph only. E.G press print button 4 (for graph 4) it prints off graph 1. You mentioned the word module. I don't have any custom made modules, I just have my normal 6 woorksheets and This workbook. Again, any help would be greatly received. Numan --- Message posted from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Printing Seperate Graphs
Hi Tom,
Many thanks for all the help that you have given, it runs like a dream now. Cheers Numan --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pictures and/or Graphs in Print Preview not Printing in Excel and | Excel Discussion (Misc queries) | |||
Printing Excel 2007 Graphs | Excel Discussion (Misc queries) | |||
Excel 2007 - Printing graphs with no data or graph lines | Charts and Charting in Excel | |||
Problems Printing Graphs in Excel | Excel Discussion (Misc queries) | |||
Excel has stopped showing graphs on seperate chart tabs | Charts and Charting in Excel |