Drawing on a Excel chart using the API

Has anyone managed to draw lines or rectangles on a Excel chart using API
calls.
I want to draw a "zoom" box on a chart. I have code to do this working on
a regular VB form, but Excel charts don't have a Device context which is
require for the API calls. I also tried embedding the chart in a Picturebox
and using it's Device context but that also doesn't work.

As a last resort I tried placing a picture box over the chart and then making
it transparent, but the code does nothing once the Picturebox is made transparent.

Anyone got any ideas or sucessfully done this.

BTW I'm using Excel 2000 but would prefer to get this working in Excel97
also.

Thanks

Michael

"Michael O'Reilly" <oreillymj@home.com> wrote in message
news:3bab72fa$1@news.devx.com...
>
> Has anyone managed to draw lines or rectangles on a Excel chart using API
> calls.
> I want to draw a "zoom" box on a chart. I have code to do this working on
> a regular VB form, but Excel charts don't have a Device context which is
> require for the API calls. I also tried embedding the chart in a Picturebox
> and using it's Device context but that also doesn't work.
>
> As a last resort I tried placing a picture box over the chart and then making
> it transparent, but the code does nothing once the Picturebox is made
transparent.

Do you want to actually modify the spreadsheet, or just give the user a "faked"
display of the spreadsheet with the drawings on top? If it's the first, then
you can use Excel Automation to add Shape objects. If it's the second, then I'm
pretty sure there's a way to manipulate window transparency such that certain
colors are opaque. However, I've never done this.

-- Matthew Solnit

"Michael O'Reilly" <oreillymj@home.com> wrote in message
news:3bab72fa$1@news.devx.com...
>
> Has anyone managed to draw lines or rectangles on a Excel chart using API
> calls.
> I want to draw a "zoom" box on a chart. I have code to do this working on
> a regular VB form, but Excel charts don't have a Device context which is
> require for the API calls. I also tried embedding the chart in a Picturebox
> and using it's Device context but that also doesn't work.
>
> As a last resort I tried placing a picture box over the chart and then making
> it transparent, but the code does nothing once the Picturebox is made
transparent.

Do you want to actually modify the spreadsheet, or just give the user a "faked"
display of the spreadsheet with the drawings on top? If it's the first, then
you can use Excel Automation to add Shape objects. If it's the second, then I'm
pretty sure there's a way to manipulate window transparency such that certain
colors are opaque. However, I've never done this.

-- Matthew Solnit

Matthew,

I want to provide the user with a way of zooming in on the data on a chart.
The user will drag out a rectangular box like in a paint application and
then zoom in on that part of chart. I don't want the chart to me modified.
It's just a selection tool. Then I'll need to get x and y coords for the
selected area and map that back to the underlying data values.

But first I need to get the actual rectangle drawn on the chart. It does
expose and Hwnd or Hdc.

Michael

"Matthew Solnit" <msolnit@nospam.yahoo-com> wrote:
>"Michael O'Reilly" <oreillymj@home.com> wrote in message
>news:3bab72fa$1@news.devx.com...
>>
>> Has anyone managed to draw lines or rectangles on a Excel chart using
API
>> calls.
>> I want to draw a "zoom" box on a chart. I have code to do this working
on
>> a regular VB form, but Excel charts don't have a Device context which
is
>> require for the API calls. I also tried embedding the chart in a Picturebox
>> and using it's Device context but that also doesn't work.
>>
>> As a last resort I tried placing a picture box over the chart and then
making
>> it transparent, but the code does nothing once the Picturebox is made
>transparent.
>
>Do you want to actually modify the spreadsheet, or just give the user a
"faked"
>display of the spreadsheet with the drawings on top? If it's the first,
then
>you can use Excel Automation to add Shape objects. If it's the second,
then I'm
>pretty sure there's a way to manipulate window transparency such that certain
>colors are opaque. However, I've never done this.
>
>-- Matthew Solnit
>
>

Matthew,

I want to provide the user with a way of zooming in on the data on a chart.
The user will drag out a rectangular box like in a paint application and
then zoom in on that part of chart. I don't want the chart to me modified.
It's just a selection tool. Then I'll need to get x and y coords for the
selected area and map that back to the underlying data values.

But first I need to get the actual rectangle drawn on the chart. It does
expose and Hwnd or Hdc.

Michael

"Matthew Solnit" <msolnit@nospam.yahoo-com> wrote:
>"Michael O'Reilly" <oreillymj@home.com> wrote in message
>news:3bab72fa$1@news.devx.com...
>>
>> Has anyone managed to draw lines or rectangles on a Excel chart using
API
>> calls.
>> I want to draw a "zoom" box on a chart. I have code to do this working
on
>> a regular VB form, but Excel charts don't have a Device context which
is
>> require for the API calls. I also tried embedding the chart in a Picturebox
>> and using it's Device context but that also doesn't work.
>>
>> As a last resort I tried placing a picture box over the chart and then
making
>> it transparent, but the code does nothing once the Picturebox is made
>transparent.
>
>Do you want to actually modify the spreadsheet, or just give the user a
"faked"
>display of the spreadsheet with the drawings on top? If it's the first,
then
>you can use Excel Automation to add Shape objects. If it's the second,
then I'm
>pretty sure there's a way to manipulate window transparency such that certain
>colors are opaque. However, I've never done this.
>
>-- Matthew Solnit
>
>

Not that it answers your question, but we have programmed exactly the same
using the TeeChart pro chart. It has built-in zooming
(http://www.teemach.com/products/products.htm). The synchornization between
chart and "grid data" was easy to do.

--
Van den Driessche Willy
For a work in progress :
http://users.skynet.be/wvdd2/index.html

Not that it answers your question, but we have programmed exactly the same
using the TeeChart pro chart. It has built-in zooming
(http://www.teemach.com/products/products.htm). The synchornization between
chart and "grid data" was easy to do.

--
Van den Driessche Willy
For a work in progress :
http://users.skynet.be/wvdd2/index.html

"Michael O'Reilly" <oreillymj@home.com> wrote in message
news:3bab9776$1@news.devx.com...
> I want to provide the user with a way of zooming in on the data on a chart.
> The user will drag out a rectangular box like in a paint application and
> then zoom in on that part of chart. I don't want the chart to me modified.
> It's just a selection tool. Then I'll need to get x and y coords for the
> selected area and map that back to the underlying data values.
>
> But first I need to get the actual rectangle drawn on the chart. It does
> expose and Hwnd or Hdc.

How are you containing the spreadsheet on your VB form? If you are using the
OLE control, it has an hWnd property.

-- Matthew Solnit

"Michael O'Reilly" <oreillymj@home.com> wrote in message
news:3bab9776$1@news.devx.com...
> I want to provide the user with a way of zooming in on the data on a chart.
> The user will drag out a rectangular box like in a paint application and
> then zoom in on that part of chart. I don't want the chart to me modified.
> It's just a selection tool. Then I'll need to get x and y coords for the
> selected area and map that back to the underlying data values.
>
> But first I need to get the actual rectangle drawn on the chart. It does
> expose and Hwnd or Hdc.

How are you containing the spreadsheet on your VB form? If you are using the
OLE control, it has an hWnd property.

-- Matthew Solnit

Matthew,

When I open VB, I don't see an OLE control for Excel charts. I have to embed
the Excel .exe to be able to draw an Excel graph in VB. Anyway the Excel
object library doesn't seem to contain a Hwnd property.

Are you talking about the MsGrph.ocx control which does have a Hwnd property.
I've tried drawing on that without any success as well.

Michael

"Matthew Solnit" <msolnit@nospam.yahoo-com> wrote:
>"Michael O'Reilly" <oreillymj@home.com> wrote in message
>news:3bab9776$1@news.devx.com...
>> I want to provide the user with a way of zooming in on the data on a chart.
>> The user will drag out a rectangular box like in a paint application and
>> then zoom in on that part of chart. I don't want the chart to me modified.
>> It's just a selection tool. Then I'll need to get x and y coords for the
>> selected area and map that back to the underlying data values.
>>
>> But first I need to get the actual rectangle drawn on the chart. It does
>> expose and Hwnd or Hdc.
>
>How are you containing the spreadsheet on your VB form? If you are using
the
>OLE control, it has an hWnd property.
>
>-- Matthew Solnit
>
>

Matthew,

When I open VB, I don't see an OLE control for Excel charts. I have to embed
the Excel .exe to be able to draw an Excel graph in VB. Anyway the Excel
object library doesn't seem to contain a Hwnd property.

Are you talking about the MsGrph.ocx control which does have a Hwnd property.
I've tried drawing on that without any success as well.

Michael

"Matthew Solnit" <msolnit@nospam.yahoo-com> wrote:
>"Michael O'Reilly" <oreillymj@home.com> wrote in message
>news:3bab9776$1@news.devx.com...
>> I want to provide the user with a way of zooming in on the data on a chart.
>> The user will drag out a rectangular box like in a paint application and
>> then zoom in on that part of chart. I don't want the chart to me modified.
>> It's just a selection tool. Then I'll need to get x and y coords for the
>> selected area and map that back to the underlying data values.
>>
>> But first I need to get the actual rectangle drawn on the chart. It does
>> expose and Hwnd or Hdc.
>
>How are you containing the spreadsheet on your VB form? If you are using
the
>OLE control, it has an hWnd property.
>
>-- Matthew Solnit
>
>

"Michael O'Reilly" <oreillymj@home.com> wrote in message
news:3baf6380$1@news.devx.com...
>
> When I open VB, I don't see an OLE control for Excel charts. I have to embed
> the Excel .exe to be able to draw an Excel graph in VB. Anyway the Excel
> object library doesn't seem to contain a Hwnd property.

Ah, the terminology is confusing. "OLE control" can be a generic term for
"ActiveX control", "OCX" etc. It is also one specific control, which has been
included with Visual Basic since VB3. It's an intrinsic control, meaning you
don't have to add it using the Components dialog.

If you start up VB and look in the toolbox, you'll see an icon that just says
"OLE". This is a control which can be used to host things like spreadsheets,
etc. If you use this control to embed the spreadsheet in your form, then you'll
have an hWnd property.

The thing is, I have no idea if you can use this hWnd to do the drawing you
want. But you can certainly try it out ;-)

-- Matthew Solnit

"Michael O'Reilly" <oreillymj@home.com> wrote in message
news:3baf6380$1@news.devx.com...
>
> When I open VB, I don't see an OLE control for Excel charts. I have to embed
> the Excel .exe to be able to draw an Excel graph in VB. Anyway the Excel
> object library doesn't seem to contain a Hwnd property.

Ah, the terminology is confusing. "OLE control" can be a generic term for
"ActiveX control", "OCX" etc. It is also one specific control, which has been
included with Visual Basic since VB3. It's an intrinsic control, meaning you
don't have to add it using the Components dialog.

If you start up VB and look in the toolbox, you'll see an icon that just says
"OLE". This is a control which can be used to host things like spreadsheets,
etc. If you use this control to embed the spreadsheet in your form, then you'll
have an hWnd property.

The thing is, I have no idea if you can use this hWnd to do the drawing you
want. But you can certainly try it out ;-)

-- Matthew Solnit

Matthew,

Oh' right, that what that OLE thingy is for. I've seen it sitting there but
never used it, so I completely overlook it.

I'll give it a try, but I did already use a PictureBox control as a container
object to get a Hwnd and that didn't do anything for me.

But thanks for your help

Michael.



"Matthew Solnit" <msolnit@nospam.yahoo-com> wrote:
>"Michael O'Reilly" <oreillymj@home.com> wrote in message
>news:3baf6380$1@news.devx.com...
>>
>> When I open VB, I don't see an OLE control for Excel charts. I have to
embed
>> the Excel .exe to be able to draw an Excel graph in VB. Anyway the Excel
>> object library doesn't seem to contain a Hwnd property.
>
>Ah, the terminology is confusing. "OLE control" can be a generic term for
>"ActiveX control", "OCX" etc. It is also one specific control, which has
been
>included with Visual Basic since VB3. It's an intrinsic control, meaning
you
>don't have to add it using the Components dialog.
>
>If you start up VB and look in the toolbox, you'll see an icon that just
says
>"OLE". This is a control which can be used to host things like spreadsheets,
>etc. If you use this control to embed the spreadsheet in your form, then
you'll
>have an hWnd property.
>
>The thing is, I have no idea if you can use this hWnd to do the drawing
you
>want. But you can certainly try it out ;-)
>
>-- Matthew Solnit
>
>

Matthew,

Oh' right, that what that OLE thingy is for. I've seen it sitting there but
never used it, so I completely overlook it.

I'll give it a try, but I did already use a PictureBox control as a container
object to get a Hwnd and that didn't do anything for me.

But thanks for your help

Michael.



"Matthew Solnit" <msolnit@nospam.yahoo-com> wrote:
>"Michael O'Reilly" <oreillymj@home.com> wrote in message
>news:3baf6380$1@news.devx.com...
>>
>> When I open VB, I don't see an OLE control for Excel charts. I have to
embed
>> the Excel .exe to be able to draw an Excel graph in VB. Anyway the Excel
>> object library doesn't seem to contain a Hwnd property.
>
>Ah, the terminology is confusing. "OLE control" can be a generic term for
>"ActiveX control", "OCX" etc. It is also one specific control, which has
been
>included with Visual Basic since VB3. It's an intrinsic control, meaning
you
>don't have to add it using the Components dialog.
>
>If you start up VB and look in the toolbox, you'll see an icon that just
says
>"OLE". This is a control which can be used to host things like spreadsheets,
>etc. If you use this control to embed the spreadsheet in your form, then
you'll
>have an hWnd property.
>
>The thing is, I have no idea if you can use this hWnd to do the drawing
you
>want. But you can certainly try it out ;-)
>
>-- Matthew Solnit
>
>