Solved: How to align selected embedded chart?

[njbarker]

Hi, all;

In Excel 2000, I have worksheets with several (1 to 10) embedded charts.

I seek a macro to move and resize (within the same worksheet) the one selected chart.

More specifically, I'd like to align:

- the left edge of the chart with the left edge of column P
- the right edge with the right edge of column W
- the top with the row which has the nearest "1" in column B
- the bottom with the row which has the nearest "29" in column B

Each worksheet has several 1's and 29's in column B, but only one set of 1 and 29 within 30 rows of the selected embedded chart.

This has had this VBA newbie headscratching and searching for hours...

Many thanks for your help!

Nicholas

[Andy Pope]

This should position the activechart according to your spec.
Assumes the 1 and 29 in column B is the only content in the cell.

[vba]
Sub PositionChart()

Dim lngTopRow As Long
Dim lngRow_1 As Long
Dim lngRow_29 As Long
Dim lngNearestRow As Long
Dim lngGap As Long
Dim rngFind As Range
Dim strFirstAddress As String

If Not ActiveChart Is Nothing Then
With ActiveChart.Parent
' position horizontally
.Left = Range("P:P").Left
.Width = Range("P:W").Width

' current row position of chart
lngTopRow = .TopLeftCell.Row
' find all row 1's and determine nearest
With Range("B:B")
Set rngFind = .Find("1", lookat:=xlWhole)
If Not rngFind Is Nothing Then
strFirstAddress = rngFind.Address
lngNearestRow = rngFind.Row
lngGap = Abs(lngTopRow - lngNearestRow)
Do
If Abs(lngTopRow - rngFind.Row) < lngGap Then
lngNearestRow = rngFind.Row
lngGap = Abs(lngTopRow - rngFind.Row)
End If
Set rngFind = .FindNext(rngFind)
Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstAddress
End If
End With
If lngNearestRow > 0 Then
' find nearest 29 from current 1
lngRow_1 = lngNearestRow
With Range("B:B")
Set rngFind = .Find("29", after:=.Cells(lngRow_1, 1), lookat:=xlWhole, searchdirection:=xlNext)
If Not rngFind Is Nothing Then
lngRow_29 = rngFind.Row
End If
End With

If lngRow_29 > 0 Then
.Top = Range("B" & lngRow_1).Top
.Height = Range("B" & lngRow_1 & ":B" & lngRow_29).Height
Else
' unable to find a 29
End If
Else
' unable to find a 1
End If
End With
End If

End Sub
[/vba]

[njbarker]

I've just tried your code, Andy, and it works like magic!

No matter which element of the embedded chart is selected, the chart gets moved and sized just as I'd wished.

Thanks for your time and code, and for raising my understanding of the Excel object model. This is just the snippet to allow me to experiment...

With ActiveChart.Parent
' position horizontally
.Left = Range("P:P").Left
.Width = Range("P:W").Width

Nicholas