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" <(E-Mail Removed)> wrote in message
news:7281A7D4-9777-471F-AD93-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:7281A7D4-9777-471F-AD93-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news:7281A7D4-9777-471F-AD93-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:7281A7D4-9777-471F-AD93-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> news:7281A7D4-9777-471F-AD93-(E-Mail Removed)...
> > 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
> >
> >
>