Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for named shape in chart
Hi,
If I have understood it correctly, there is no way to control the placement of the Axis title object in a chart or control the text line breaks in it through VB. I'm building a code to apply user defined settings on charts and the code adds a textbox in stead of using the Axis title so the placement can be controlled and the user can control the line breaks. The problem is: I the user applies one user defined chart and then change to another, the text box is added again. I'm sure there is a way to check if a named text box exist in a chart, and add it if it doesn't. This is the current code: Sub Column() If Not ActiveChart Is Nothing Then With ActiveChart.Parent .Height = 252.75 .Width = 342.75 End With ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:= _ "Column" ActiveChart.Legend.Left = 0 ActiveChart.Legend.Top = 250 ActiveChart.PlotArea.Left = 0 ActiveChart.PlotArea.Top = 25 ActiveChart.PlotArea.Height = 205 ActiveChart.PlotArea.Width = 340 ActiveChart.Shapes.AddTextbox(msoTextOrientationHo rizontal, 0, 2, 0, 0).Select Selection.Characters.Text = "Y-axis title" With Selection.Font .Name = "Arial" .FontStyle = "Normal" .Size = 10 .ColorIndex = xlAutomatic .Background = xlTransparent End With With Selection .AutoScaleFont = False .HorizontalAlignment = xlLeft .VerticalAlignment = xlCenter .ReadingOrder = xlContext .Orientation = xlHorizontal .AutoSize = True .Placement = xlMove .PrintObject = True .Name = "Y-axis title" End With Else MsgBox "You have to select a chart before performing this action.", _ vbExclamation, "No chart selected." End If End Sub Any help would be great! -- Fredrik E. Nilsen |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for named shape in chart
On Mon, 12 Feb 2007 02:22:32 +0100, Fredrik E. Nilsen
wrote: I have been able to modify my code a bit to this: Sub Column() Dim shp1 As Shape Dim shp2 As Shape If Not ActiveChart Is Nothing Then With ActiveChart.Parent .Height = 252.75 .Width = 342.75 End With ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:= _ "Column" ActiveChart.Legend.Left = 0 ActiveChart.Legend.Top = 250 ActiveChart.PlotArea.Left = 0 ActiveChart.PlotArea.Top = 25 ActiveChart.PlotArea.Height = 205 ActiveChart.PlotArea.Width = 340 On Error Resume Next Set shp1 = ActiveChart.Shapes("Y-axis title") Set shp2 = ActiveChart.Shapes("X-axis title") If shp1 Is Nothing Then ActiveChart.Shapes.AddTextbox(msoTextOrientationHo rizontal, 0, 2, 0, 0).Select Selection.Characters.Text = "Y-axis title" With Selection.Font .Name = "Arial" .FontStyle = "Normal" .Size = 10 .ColorIndex = xlAutomatic .Background = xlTransparent End With With Selection .AutoScaleFont = False .HorizontalAlignment = xlLeft .VerticalAlignment = xlCenter .ReadingOrder = xlContext .Orientation = xlHorizontal .AutoSize = True .Placement = xlMove .PrintObject = True .Name = "Y-axis title" End With Else ActiveChart.Shapes("X-axis title").Delete End If Else MsgBox "You have to select a chart before performing this action.", _ vbExclamation, "No chart selected." End If End Sub The point is to remove the X-axis title if it exsist in the chart and replace it with a Y-axis title. If a Y-axis title exist, nothing should be done. If no title exist, a Y-axis title should be inserted. This should of course be vice versa if the user applies a bar chart. The current code works if you don't change from column to bar or similar. If you change from column to bar, you have to run it twice to remove the erronous title. -- Fredrik E. Nilsen |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for named shape in chart
"Fredrik E. Nilsen" wrote in message ... Hi, If I have understood it correctly, there is no way to control the placement of the Axis title object in a chart or control the text line breaks in it through VB. You can easily get or set the position of an axis title: Sub AxisTitlePlacement() If ActiveChart.HasAxis(xlValue, xlPrimary) Then If ActiveChart.Axes(xlValue, xlPrimary).HasTitle Then With ActiveChart.Axes(xlValue, xlPrimary).AxisTitle ' get axis title position MsgBox "Axis Position Left: " & .Left & ", Top: " & .Top ' set axis title position .Left = 0 .Top = ActiveChart.PlotArea.Height / 2 + ActiveChart.PlotArea.InsideTop - 12 End With End If End If End Sub Line breaks are not as straightforward. You cannot make the line any longer than Excel decides (other than by shrinking the text or stretching the chart), but you could insert hard breaks. You would have to write code smart enough to read the text MyText = ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text and decide where a break should be. Text boxes are in fact the way to get around the limitation you have in resizing the title box. I'm building a code to apply user defined settings on charts and the code adds a textbox in stead of using the Axis title so the placement can be controlled and the user can control the line breaks. The problem is: I the user applies one user defined chart and then change to another, the text box is added again. I'm sure there is a way to check if a named text box exist in a chart, and add it if it doesn't. This is the current code: This procedure looks for a textbox with a particular name. If it finds it, it updates the text. If it doesn't find it, it adds a new one, names it, and adds text. Sub UpdateOrAddTextBox() Dim shp As Shape Dim bTitleFound As Boolean If ActiveChart.Shapes.Count 0 Then For Each shp In ActiveChart.Shapes If shp.Name = "textbox_YAxisTitle" Then ' we found it bTitleFound = True ' adjust text and position of textbox shp.TextFrame.Characters.Text = "New Y Axis Title" End If Next End If If Not bTitleFound Then ' didn't find it, so add it Set shp = ActiveChart.Shapes.AddTextbox(msoTextOrientationUp ward, 0, _ ActiveChart.PlotArea.Height / 2 + ActiveChart.PlotArea.InsideTop - 12, 20, 100) With shp .Name = "textbox_YAxisTitle" .TextFrame.Characters.Text = "Y Axis Title" .TextFrame.AutoSize = True End With End If End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for named shape in chart
On Sun, 11 Feb 2007 22:13:27 -0500, "Jon Peltier"
wrote: "Fredrik E. Nilsen" wrote in message .. . Hi, If I have understood it correctly, there is no way to control the placement of the Axis title object in a chart or control the text line breaks in it through VB. You can easily get or set the position of an axis title: Sub AxisTitlePlacement() If ActiveChart.HasAxis(xlValue, xlPrimary) Then If ActiveChart.Axes(xlValue, xlPrimary).HasTitle Then With ActiveChart.Axes(xlValue, xlPrimary).AxisTitle ' get axis title position MsgBox "Axis Position Left: " & .Left & ", Top: " & .Top ' set axis title position .Left = 0 .Top = ActiveChart.PlotArea.Height / 2 + ActiveChart.PlotArea.InsideTop - 12 End With End If End If End Sub Ah, thank you. I was mislead by a couple of blogs, and I couldn't find any documentation about it in the help-file. Line breaks are not as straightforward. You cannot make the line any longer than Excel decides (other than by shrinking the text or stretching the chart), but you could insert hard breaks. You would have to write code smart enough to read the text MyText = ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text and decide where a break should be. Text boxes are in fact the way to get around the limitation you have in resizing the title box. The problem is that they don't want a line break for the axis-title at all so I guess text boxes is the only way to go. This procedure looks for a textbox with a particular name. If it finds it, it updates the text. If it doesn't find it, it adds a new one, names it, and adds text. Sub UpdateOrAddTextBox() Dim shp As Shape Dim bTitleFound As Boolean If ActiveChart.Shapes.Count 0 Then For Each shp In ActiveChart.Shapes If shp.Name = "textbox_YAxisTitle" Then ' we found it bTitleFound = True ' adjust text and position of textbox shp.TextFrame.Characters.Text = "New Y Axis Title" End If Next End If If Not bTitleFound Then ' didn't find it, so add it Set shp = ActiveChart.Shapes.AddTextbox(msoTextOrientationUp ward, 0, _ ActiveChart.PlotArea.Height / 2 + ActiveChart.PlotArea.InsideTop - 12, 20, 100) With shp .Name = "textbox_YAxisTitle" .TextFrame.Characters.Text = "Y Axis Title" .TextFrame.AutoSize = True End With End If End Sub Fantastic, I will try this in the morning! Now it's 5 am local time, I can barely read what you have written...:) Thanks again! -- Fredrik E. Nilsen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
chart over a shape | Charts and Charting in Excel | |||
Excel 2003: How to nudge a chart element or shape on a chart? | Charts and Charting in Excel | |||
check whether shape is a group | Excel Programming | |||
Shape ControlFormat.LinkedCell returns wrong Address for named range - BUG | Excel Programming | |||
Chart in a Shape | Excel Discussion (Misc queries) |