Changing Right Position of Chart

[AdmiralAJ]

I have a spreadsheet with 2 charts side by side. Sometimes code
executed from an input sheet will hide a column on this sheet. I have
the following code set to execute when this sheet is activated to
align both charts the only problem is the second chart (to the right
of the first chart is never aligned to the last column which would be
"L".

Any ideas on how to achieve this? My code follows:

Private Sub Worksheet_Activate()
Dim cho As ChartObject

Application.ScreenUpdating = False
Unprotect

For Each cho In ActiveSheet.ChartObjects
With cho
.Height = 192.75
.Top = 428.25
.Width = 400

With .Chart
.ChartArea.AutoScaleFont = False

' fix the title
If .HasTitle Then
With .ChartTitle.Font
.Size = 10
.Name = "Tahoma"
.FontStyle = "Bold"
End With
End If

' fix the legend
If .HasLegend Then
With .Legend.Font
.Size = 9
.Name = "Tahoma"
.FontStyle = "Regular"
End With
End If
End With
End With
Next

' Protect
Application.ScreenUpdating = True
End Sub

 

[Jon Peltier]

You have specified .Height, .Width, and .Top for the chart objects, but not
..Left.

- Jon

 

[Guest]

You could use something like this - aligns the left side of the chart with
column "L":

Sub AlignChartToColumn()
Dim cho As ChartObject
For Each cho In ActiveSheet.ChartObjects
cho.Left = Columns("L").Left
Next
End Sub

Assuming your chart were named "Cht1", to further align you could offset the
original position using something like the following:

ActiveSheet.Shapes("Cht1").Top = .Offset(2, 0).Top
ActiveSheet.Shapes("Cht1").Left = .Offset(3, 4).Left

John Mansfield

 

[AdmiralAJ]

You have specified .Height, .Width, and .Top for the chart objects, but not
.Left.

- Jon

Jon and John,

Thanks for your help, but I guess I was not very clear. I needed to
have the right edge of the chart aligned with the right edge of column
"L". I found a fix on the MS site and I thought I'd let you see it.

'this code helps position the right side of chart 2 to the right edge
of column L
Set myrange = Range("a1:l1") 'choose the column range of the
report to help determine its width
ChartObjects(2).Left = (myrange.Width - ChartObjects(2).Width)

Thanks again for your help.

AJ

 

[Jon Peltier]

Did you notice the solution you found uses .Left? To fix the horizontal
position you need to use .Left, even to align the right edge of an object.
All that you need to add for that is a little 7th grade algebra.

- Jon

 

[AdmiralAJ]

Did you notice the solution you found uses .Left? To fix the horizontal
position you need to use .Left, even to align the right edge of an object.
All that you need to add for that is a little 7th grade algebra.

- Jon

Jon,

You're right, it was my fault for not being clear about wanting to
align the right side of the chart. I only posted the solution to see
if you thought there was something better. You guys are great and
always helpful, sorry if you took my post any other way.

AJ