I'm trying to determine if a ChartObject is within the Print_Area on a given
worksheet and have this code. I've noted in the code where execution ends.
I've done a "debug.print" and see that the topleftcell is in the Print_area,
but I'm not sure why it's quitting on me. Does anyone have any ideas?


Dim ChtObj As Excel.ChartObject
Dim myRange As Range

myChartInRange = False


On Error Resume Next
Set myRange = myWS.Range("Print_Area")
On Error GoTo 0
If myRange Is Nothing Then Exit Sub

For Each ChtObj In myWS.ChartObjects
myChartInRange = False
'Execution in this sub ends on the line below
If Not Intersect(ChtObj.TopLeftCell, myRange) Is Nothing Then
Debug.Print "Top Left in Range", myWS.Name, ChtObj.Name
Set myChtObj = ChtObj
myChartInRange = True
Exit For
End If
Next ChtObj
Debug.Print
--
HTH,
Barb Reinhardt

Barb -

I ran this fine in 2003 (SP3) and 2007 (SP1). I don't think I changed
anything meaningful.

I used this sub to test the function in 2003 and 2007. In all cases I got
True or False in the debug window and the beep at the end of the function. I
don't know why you had problems, unless it's related to some fixes made in
SP1.

Sub test()
Debug.Print myChartInRange
End Sub

This is the function. I don't think I changed anything meaningful from what
you posted.

Function myChartInRange()
Dim myWS As Worksheet
Dim ChtObj As Excel.ChartObject
Dim myChtObj As Excel.ChartObject
Dim myRange As Range

myChartInRange = False
Set myWS = ActiveSheet

On Error Resume Next
Set myRange = myWS.Range("Print_Area")
On Error GoTo 0
If myRange Is Nothing Then Exit Function

For Each ChtObj In myWS.ChartObjects
myChartInRange = False
'Execution in this sub ends on the line below
If Not Intersect(ChtObj.TopLeftCell, myRange) Is Nothing Then

Debug.Print "Top Left in Range", myWS.Name, ChtObj.Name
Set myChtObj = ChtObj
myChartInRange = True
Exit For

End If
Next ChtObj
Beep
End Function


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Barb Reinhardt" wrote in message
...
I'm trying to determine if a ChartObject is within the Print_Area on a
given
worksheet and have this code. I've noted in the code where execution
ends.
I've done a "debug.print" and see that the topleftcell is in the
Print_area,
but I'm not sure why it's quitting on me. Does anyone have any ideas?


Dim ChtObj As Excel.ChartObject
Dim myRange As Range

myChartInRange = False


On Error Resume Next
Set myRange = myWS.Range("Print_Area")
On Error GoTo 0
If myRange Is Nothing Then Exit Sub

For Each ChtObj In myWS.ChartObjects
myChartInRange = False
'Execution in this sub ends on the line below
If Not Intersect(ChtObj.TopLeftCell, myRange) Is Nothing Then
Debug.Print "Top Left in Range", myWS.Name, ChtObj.Name
Set myChtObj = ChtObj
myChartInRange = True
Exit For
End If
Next ChtObj
Debug.Print
--
HTH,
Barb Reinhardt

I'm testing it in 2003 right now and it gacks. I'll try your code and see if
it works.

Thanks,
Barb

"Jon Peltier" wrote:

Barb -

I ran this fine in 2003 (SP3) and 2007 (SP1). I don't think I changed
anything meaningful.

I used this sub to test the function in 2003 and 2007. In all cases I got
True or False in the debug window and the beep at the end of the function. I
don't know why you had problems, unless it's related to some fixes made in
SP1.

Sub test()
Debug.Print myChartInRange
End Sub

This is the function. I don't think I changed anything meaningful from what
you posted.

Function myChartInRange()
Dim myWS As Worksheet
Dim ChtObj As Excel.ChartObject
Dim myChtObj As Excel.ChartObject
Dim myRange As Range

myChartInRange = False
Set myWS = ActiveSheet

On Error Resume Next
Set myRange = myWS.Range("Print_Area")
On Error GoTo 0
If myRange Is Nothing Then Exit Function

For Each ChtObj In myWS.ChartObjects
myChartInRange = False
'Execution in this sub ends on the line below
If Not Intersect(ChtObj.TopLeftCell, myRange) Is Nothing Then

Debug.Print "Top Left in Range", myWS.Name, ChtObj.Name
Set myChtObj = ChtObj
myChartInRange = True
Exit For

End If
Next ChtObj
Beep
End Function


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Barb Reinhardt" wrote in message
...
I'm trying to determine if a ChartObject is within the Print_Area on a
given
worksheet and have this code. I've noted in the code where execution
ends.
I've done a "debug.print" and see that the topleftcell is in the
Print_area,
but I'm not sure why it's quitting on me. Does anyone have any ideas?


Dim ChtObj As Excel.ChartObject
Dim myRange As Range

myChartInRange = False


On Error Resume Next
Set myRange = myWS.Range("Print_Area")
On Error GoTo 0
If myRange Is Nothing Then Exit Sub

For Each ChtObj In myWS.ChartObjects
myChartInRange = False
'Execution in this sub ends on the line below
If Not Intersect(ChtObj.TopLeftCell, myRange) Is Nothing Then
Debug.Print "Top Left in Range", myWS.Name, ChtObj.Name
Set myChtObj = ChtObj
myChartInRange = True
Exit For
End If
Next ChtObj
Debug.Print
--
HTH,
Barb Reinhardt

Hi,

What exactly do you want the function to do?

Currently it checks all the chartobjects on a sheet to see if the
topleftcell they cover is within the print area.
As soon as a chart meets that requirement the loop terminates. This could
lead to incorrect results.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Barb Reinhardt" wrote in message
...
I'm trying to determine if a ChartObject is within the Print_Area on a
given
worksheet and have this code. I've noted in the code where execution
ends.
I've done a "debug.print" and see that the topleftcell is in the
Print_area,
but I'm not sure why it's quitting on me. Does anyone have any ideas?


Dim ChtObj As Excel.ChartObject
Dim myRange As Range

myChartInRange = False


On Error Resume Next
Set myRange = myWS.Range("Print_Area")
On Error GoTo 0
If myRange Is Nothing Then Exit Sub

For Each ChtObj In myWS.ChartObjects
myChartInRange = False
'Execution in this sub ends on the line below
If Not Intersect(ChtObj.TopLeftCell, myRange) Is Nothing Then
Debug.Print "Top Left in Range", myWS.Name, ChtObj.Name
Set myChtObj = ChtObj
myChartInRange = True
Exit For
End If
Next ChtObj
Debug.Print
--
HTH,
Barb Reinhardt

My problem was that I was doing this in PowerPOint and didn't use the
XLApp.Intersect function.

Thanks for your help.
Barb Reinhardt




"Jon Peltier" wrote:

Barb -

I ran this fine in 2003 (SP3) and 2007 (SP1). I don't think I changed
anything meaningful.

I used this sub to test the function in 2003 and 2007. In all cases I got
True or False in the debug window and the beep at the end of the function. I
don't know why you had problems, unless it's related to some fixes made in
SP1.

Sub test()
Debug.Print myChartInRange
End Sub

This is the function. I don't think I changed anything meaningful from what
you posted.

Function myChartInRange()
Dim myWS As Worksheet
Dim ChtObj As Excel.ChartObject
Dim myChtObj As Excel.ChartObject
Dim myRange As Range

myChartInRange = False
Set myWS = ActiveSheet

On Error Resume Next
Set myRange = myWS.Range("Print_Area")
On Error GoTo 0
If myRange Is Nothing Then Exit Function

For Each ChtObj In myWS.ChartObjects
myChartInRange = False
'Execution in this sub ends on the line below
If Not Intersect(ChtObj.TopLeftCell, myRange) Is Nothing Then

Debug.Print "Top Left in Range", myWS.Name, ChtObj.Name
Set myChtObj = ChtObj
myChartInRange = True
Exit For

End If
Next ChtObj
Beep
End Function


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Barb Reinhardt" wrote in message
...
I'm trying to determine if a ChartObject is within the Print_Area on a
given
worksheet and have this code. I've noted in the code where execution
ends.
I've done a "debug.print" and see that the topleftcell is in the
Print_area,
but I'm not sure why it's quitting on me. Does anyone have any ideas?


Dim ChtObj As Excel.ChartObject
Dim myRange As Range

myChartInRange = False


On Error Resume Next
Set myRange = myWS.Range("Print_Area")
On Error GoTo 0
If myRange Is Nothing Then Exit Sub

For Each ChtObj In myWS.ChartObjects
myChartInRange = False
'Execution in this sub ends on the line below
If Not Intersect(ChtObj.TopLeftCell, myRange) Is Nothing Then
Debug.Print "Top Left in Range", myWS.Name, ChtObj.Name
Set myChtObj = ChtObj
myChartInRange = True
Exit For
End If
Next ChtObj
Debug.Print
--
HTH,
Barb Reinhardt

Based on the way this is used, it's not a problem. I was trying to use an
Excel function within powerpoint and it kept kicking me out until I figured
that out.
Thanks,
Barb Reinhardt




"Andy Pope" wrote:

Hi,

What exactly do you want the function to do?

Currently it checks all the chartobjects on a sheet to see if the
topleftcell they cover is within the print area.
As soon as a chart meets that requirement the loop terminates. This could
lead to incorrect results.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Barb Reinhardt" wrote in message
...
I'm trying to determine if a ChartObject is within the Print_Area on a
given
worksheet and have this code. I've noted in the code where execution
ends.
I've done a "debug.print" and see that the topleftcell is in the
Print_area,
but I'm not sure why it's quitting on me. Does anyone have any ideas?


Dim ChtObj As Excel.ChartObject
Dim myRange As Range

myChartInRange = False


On Error Resume Next
Set myRange = myWS.Range("Print_Area")
On Error GoTo 0
If myRange Is Nothing Then Exit Sub

For Each ChtObj In myWS.ChartObjects
myChartInRange = False
'Execution in this sub ends on the line below
If Not Intersect(ChtObj.TopLeftCell, myRange) Is Nothing Then
Debug.Print "Top Left in Range", myWS.Name, ChtObj.Name
Set myChtObj = ChtObj
myChartInRange = True
Exit For
End If
Next ChtObj
Debug.Print
--
HTH,
Barb Reinhardt