Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Changing Right Position of Chart
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 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Changing Right Position of Chart
You have specified .Height, .Width, and .Top for the chart objects, but not
..Left. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "AdmiralAJ" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Changing Right Position of Chart
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" wrote: 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 |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Changing Right Position of Chart
On Apr 11, 3:24 pm, "Jon Peltier"
wrote: You have specified .Height, .Width, and .Top for the chart objects, but not .Left. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutionshttp://PeltierTech.com _______ "AdmiralAJ" wrote in message oups.com... 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 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 |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Changing Right Position of Chart
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 Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "AdmiralAJ" wrote in message oups.com... On Apr 11, 3:24 pm, "Jon Peltier" wrote: You have specified .Height, .Width, and .Top for the chart objects, but not .Left. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutionshttp://PeltierTech.com _______ "AdmiralAJ" wrote in message oups.com... 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 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 |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
Changing Right Position of Chart
On Apr 13, 6:14 am, "Jon Peltier"
wrote: 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 Peltier, Microsoft Excel MVP Tutorials and Custom Solutionshttp://PeltierTech.com _______ "AdmiralAJ" wrote in message oups.com... On Apr 11, 3:24 pm, "Jon Peltier" wrote: You have specified .Height, .Width, and .Top for the chart objects, but not .Left. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutionshttp://PeltierTech.com _______ "AdmiralAJ" wrote in message groups.com... 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 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, 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Comment Box Position - Any solutions? | Excel Worksheet Functions | |||
CHANGING POSITION OF AUTOFILTER LIST | Excel Discussion (Misc queries) | |||
How to set the chart position exactly? | Charts and Charting in Excel | |||
Chart league position | Charts and Charting in Excel | |||
vlookup change column index position - without changing formulae | Excel Worksheet Functions |