Excel VBA - Printing Seperate Graphs

[Numan]

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

 

[Tom Ogilvy]

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

 

[Numan]

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

 

[Tom Ogilvy]

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).

 

[Numan]

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

 

[Tom Ogilvy]

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.

 

[Numan]

Hi Tom,

Many thanks for all the help that you have given, it runs like a dream
now.

Cheers

Numan