Control Display Area

[PraxisPete]

Hi Everybody

I am using Excel 2002 and running a macro which draws shapes on a separate
worksheet within the same workbook. The Top left hand corner of my shape is
3000 points in from the left hand edge and 3000 points down from the top edge
of the drawing sheet. I need my macro to display the drawing sheet with the
top left corner of my shape in the top left corner of the window. Has anyone
got any ideas?

Many thanks in advance.

[Damien McBain]

"PraxisPete" <[email protected]> wrote in message
news:[email protected]...
> Hi Everybody
>
> I am using Excel 2002 and running a macro which draws shapes on a separate
> worksheet within the same workbook. The Top left hand corner of my shape
> is
> 3000 points in from the left hand edge and 3000 points down from the top
> edge
> of the drawing sheet. I need my macro to display the drawing sheet with
> the
> top left corner of my shape in the top left corner of the window. Has
> anyone
> got any ideas?

You could set the worksheet.activate event to:

sub worksheet_activate()
range("the range encompassing the cells under the drawing")
activewindow.zoom = true
end sub

[keepITcool]

you could simply use :
application.goto shp.topleftcell, true
but goto leaves traces.. so better to use scrollrow/scrollcolumn

I wouldt not use scrollintoview...
...as we'll hit on excel's confusing coordinate system...
activewindow.ScrollIntoView 3000*(96/72),3000*(96/72),1,1,true
(you need to use api's to correctly read the DPI here assumed to be 96)


Better to use ScrollRow/Column...
note Excel will scroll on a row/column basis,
so you may want to adjust your shape to exactly align
to its underlying cell..

With wks.Shapes.AddShape(msoShapeRectangle, 3000, 3000, 30, 30)
.Top = .TopLeftCell.Top
.Left = .TopLeftCell.Left
ActiveWindow.ScrollRow = .TopLeftCell.Row
ActiveWindow.ScrollColumn = .TopLeftCell.Column
End With


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


PraxisPete wrote :

> Hi Everybody
>
> I am using Excel 2002 and running a macro which draws shapes on a
> separate worksheet within the same workbook. The Top left hand
> corner of my shape is 3000 points in from the left hand edge and 3000
> points down from the top edge of the drawing sheet. I need my macro
> to display the drawing sheet with the top left corner of my shape in
> the top left corner of the window. Has anyone got any ideas?
>
> Many thanks in advance.