|
Annotating charts with text from within VBA
I need to be able to add some text to charts ("Max", "Min", "Target" for
example) from within an Excel VBA Macro. At this point I have been unable to get this to take place. The code that I had tried is as follows: ' add text box labels chrtobj.Chart.Shapes.AddLabel(msoTextOrientationHo rizontal, 133.5, 105#, 0# _ , 0#).Select Selection.ShapeRange(1).TextFrame.AutoSize = msoTrue Selection.Characters.Text = "UPPER" Selection.AutoScaleFont = False With Selection.Characters(Start:=1, Length:=5).Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Selection.ShapeRange.Fill.Visible = msoFalse Selection.ShapeRange.Fill.Solid Selection.ShapeRange.Fill.Transparency = 0# Selection.ShapeRange.Line.Weight = 0.75 Selection.ShapeRange.Line.DashStyle = msoLineSolid Selection.ShapeRange.Line.Style = msoLineSingle Selection.ShapeRange.Line.Transparency = 0# Selection.ShapeRange.Line.Visible = msoTrue Selection.ShapeRange.Line.ForeColor.SchemeColor = 8 Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255) Selection.ShapeRange.IncrementLeft -0.36 Selection.ShapeRange.IncrementTop -8.98 But when I use this code (which worked when I recorded it), I get a VBA "Run-time error '438': Object doesn't support this property or method" and it takes me to the debugger. Any ideas on why this is happening and how I can get the labels on the chart? Thanks, -- John O |
Annotating charts with text from within VBA
The code runs without error for me. What line is highlighted in the
debugger? How did you define chrtobj? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John O" wrote in message ... I need to be able to add some text to charts ("Max", "Min", "Target" for example) from within an Excel VBA Macro. At this point I have been unable to get this to take place. The code that I had tried is as follows: ' add text box labels chrtobj.Chart.Shapes.AddLabel(msoTextOrientationHo rizontal, 133.5, 105#, 0# _ , 0#).Select Selection.ShapeRange(1).TextFrame.AutoSize = msoTrue Selection.Characters.Text = "UPPER" Selection.AutoScaleFont = False With Selection.Characters(Start:=1, Length:=5).Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Selection.ShapeRange.Fill.Visible = msoFalse Selection.ShapeRange.Fill.Solid Selection.ShapeRange.Fill.Transparency = 0# Selection.ShapeRange.Line.Weight = 0.75 Selection.ShapeRange.Line.DashStyle = msoLineSolid Selection.ShapeRange.Line.Style = msoLineSingle Selection.ShapeRange.Line.Transparency = 0# Selection.ShapeRange.Line.Visible = msoTrue Selection.ShapeRange.Line.ForeColor.SchemeColor = 8 Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255) Selection.ShapeRange.IncrementLeft -0.36 Selection.ShapeRange.IncrementTop -8.98 But when I use this code (which worked when I recorded it), I get a VBA "Run-time error '438': Object doesn't support this property or method" and it takes me to the debugger. Any ideas on why this is happening and how I can get the labels on the chart? Thanks, -- John O |
Annotating charts with text from within VBA
I just sent you the section that didn't run. Do you want me to post the
entire subroutine so you can look at it? Thanks, -- John O "Jon Peltier" wrote: The code runs without error for me. What line is highlighted in the debugger? How did you define chrtobj? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John O" wrote in message ... I need to be able to add some text to charts ("Max", "Min", "Target" for example) from within an Excel VBA Macro. At this point I have been unable to get this to take place. The code that I had tried is as follows: ' add text box labels chrtobj.Chart.Shapes.AddLabel(msoTextOrientationHo rizontal, 133.5, 105#, 0# _ , 0#).Select Selection.ShapeRange(1).TextFrame.AutoSize = msoTrue Selection.Characters.Text = "UPPER" Selection.AutoScaleFont = False With Selection.Characters(Start:=1, Length:=5).Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Selection.ShapeRange.Fill.Visible = msoFalse Selection.ShapeRange.Fill.Solid Selection.ShapeRange.Fill.Transparency = 0# Selection.ShapeRange.Line.Weight = 0.75 Selection.ShapeRange.Line.DashStyle = msoLineSolid Selection.ShapeRange.Line.Style = msoLineSingle Selection.ShapeRange.Line.Transparency = 0# Selection.ShapeRange.Line.Visible = msoTrue Selection.ShapeRange.Line.ForeColor.SchemeColor = 8 Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255) Selection.ShapeRange.IncrementLeft -0.36 Selection.ShapeRange.IncrementTop -8.98 But when I use this code (which worked when I recorded it), I get a VBA "Run-time error '438': Object doesn't support this property or method" and it takes me to the debugger. Any ideas on why this is happening and how I can get the labels on the chart? Thanks, -- John O |
Annotating charts with text from within VBA
No, I asked which line within the code you posted is highlighted in yellow
when the error occurs. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John O" wrote in message ... I just sent you the section that didn't run. Do you want me to post the entire subroutine so you can look at it? Thanks, -- John O "Jon Peltier" wrote: The code runs without error for me. What line is highlighted in the debugger? How did you define chrtobj? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John O" wrote in message ... I need to be able to add some text to charts ("Max", "Min", "Target" for example) from within an Excel VBA Macro. At this point I have been unable to get this to take place. The code that I had tried is as follows: ' add text box labels chrtobj.Chart.Shapes.AddLabel(msoTextOrientationHo rizontal, 133.5, 105#, 0# _ , 0#).Select Selection.ShapeRange(1).TextFrame.AutoSize = msoTrue Selection.Characters.Text = "UPPER" Selection.AutoScaleFont = False With Selection.Characters(Start:=1, Length:=5).Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Selection.ShapeRange.Fill.Visible = msoFalse Selection.ShapeRange.Fill.Solid Selection.ShapeRange.Fill.Transparency = 0# Selection.ShapeRange.Line.Weight = 0.75 Selection.ShapeRange.Line.DashStyle = msoLineSolid Selection.ShapeRange.Line.Style = msoLineSingle Selection.ShapeRange.Line.Transparency = 0# Selection.ShapeRange.Line.Visible = msoTrue Selection.ShapeRange.Line.ForeColor.SchemeColor = 8 Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255) Selection.ShapeRange.IncrementLeft -0.36 Selection.ShapeRange.IncrementTop -8.98 But when I use this code (which worked when I recorded it), I get a VBA "Run-time error '438': Object doesn't support this property or method" and it takes me to the debugger. Any ideas on why this is happening and how I can get the labels on the chart? Thanks, -- John O |
Annotating charts with text from within VBA
Jon,
Sorry about that. I forgot to answer that question. It is on the second line in the code fragment that I posted (the first selection statement "Selection.ShapeRange(1).TextFrame.AutoSize = msoTrue"). -- John O "Jon Peltier" wrote: No, I asked which line within the code you posted is highlighted in yellow when the error occurs. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John O" wrote in message ... I just sent you the section that didn't run. Do you want me to post the entire subroutine so you can look at it? Thanks, -- John O "Jon Peltier" wrote: The code runs without error for me. What line is highlighted in the debugger? How did you define chrtobj? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John O" wrote in message ... I need to be able to add some text to charts ("Max", "Min", "Target" for example) from within an Excel VBA Macro. At this point I have been unable to get this to take place. The code that I had tried is as follows: ' add text box labels chrtobj.Chart.Shapes.AddLabel(msoTextOrientationHo rizontal, 133.5, 105#, 0# _ , 0#).Select Selection.ShapeRange(1).TextFrame.AutoSize = msoTrue Selection.Characters.Text = "UPPER" Selection.AutoScaleFont = False With Selection.Characters(Start:=1, Length:=5).Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Selection.ShapeRange.Fill.Visible = msoFalse Selection.ShapeRange.Fill.Solid Selection.ShapeRange.Fill.Transparency = 0# Selection.ShapeRange.Line.Weight = 0.75 Selection.ShapeRange.Line.DashStyle = msoLineSolid Selection.ShapeRange.Line.Style = msoLineSingle Selection.ShapeRange.Line.Transparency = 0# Selection.ShapeRange.Line.Visible = msoTrue Selection.ShapeRange.Line.ForeColor.SchemeColor = 8 Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255) Selection.ShapeRange.IncrementLeft -0.36 Selection.ShapeRange.IncrementTop -8.98 But when I use this code (which worked when I recorded it), I get a VBA "Run-time error '438': Object doesn't support this property or method" and it takes me to the debugger. Any ideas on why this is happening and how I can get the labels on the chart? Thanks, -- John O |
Annotating charts with text from within VBA
I suspect Excel doesn't know what chrtobj is. You need to reference it
somehow: Dim chrtobj As ChartObject Set chrtobj = ActiveSheet.ChartObjects(1) Or maybe use something like this as your command that creates the textbox: With ActiveChart.Shapes.AddLabel(msoTextOrientationHori zontal, 133.5, 105#, 0#, 0#) In fact, using AddLabel ends up with a black background, while AddTextbox has a white background. There are a lot of other inefficiencies in the code. This is streamlined a bit. The AutoFontScale is going to cause problems. The only way I could make it work is shown in the last line before End Sub. Sub ChartLabels() Dim chrtobj As ChartObject Set chrtobj = ActiveSheet.ChartObjects(1) ' add text box labels With chrtobj.Chart.Shapes.AddTextbox(msoTextOrientation Horizontal, 133.5, 105#, 0#, 0#) With .TextFrame .AutoSize = msoTrue .Characters.Text = "UPPER" With .Characters(Start:=1, Length:=5).Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With End With With .Fill .Visible = msoFalse .Solid .Transparency = 0# End With With .Line .Weight = 0.75 .DashStyle = msoLineSolid .Style = msoLineSingle .Transparency = 0# .Visible = msoTrue .ForeColor.SchemeColor = 8 .BackColor.RGB = RGB(255, 255, 255) ' TURNS IT BLACK End With .IncrementLeft -0.36 ' BUILD THESE INTO INITIAL DIMENSIONS .IncrementTop -8.98 ' (IN AddLabel STATEMENT ABOVE) End With chrtobj.Chart.TextBoxes(chrtobj.Chart.TextBoxes.Co unt).AutoScaleFont = False End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John O" wrote in message ... Jon, Sorry about that. I forgot to answer that question. It is on the second line in the code fragment that I posted (the first selection statement "Selection.ShapeRange(1).TextFrame.AutoSize = msoTrue"). -- John O "Jon Peltier" wrote: No, I asked which line within the code you posted is highlighted in yellow when the error occurs. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John O" wrote in message ... I just sent you the section that didn't run. Do you want me to post the entire subroutine so you can look at it? Thanks, -- John O "Jon Peltier" wrote: The code runs without error for me. What line is highlighted in the debugger? How did you define chrtobj? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John O" wrote in message ... I need to be able to add some text to charts ("Max", "Min", "Target" for example) from within an Excel VBA Macro. At this point I have been unable to get this to take place. The code that I had tried is as follows: ' add text box labels chrtobj.Chart.Shapes.AddLabel(msoTextOrientationHo rizontal, 133.5, 105#, 0# _ , 0#).Select Selection.ShapeRange(1).TextFrame.AutoSize = msoTrue Selection.Characters.Text = "UPPER" Selection.AutoScaleFont = False With Selection.Characters(Start:=1, Length:=5).Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Selection.ShapeRange.Fill.Visible = msoFalse Selection.ShapeRange.Fill.Solid Selection.ShapeRange.Fill.Transparency = 0# Selection.ShapeRange.Line.Weight = 0.75 Selection.ShapeRange.Line.DashStyle = msoLineSolid Selection.ShapeRange.Line.Style = msoLineSingle Selection.ShapeRange.Line.Transparency = 0# Selection.ShapeRange.Line.Visible = msoTrue Selection.ShapeRange.Line.ForeColor.SchemeColor = 8 Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255) Selection.ShapeRange.IncrementLeft -0.36 Selection.ShapeRange.IncrementTop -8.98 But when I use this code (which worked when I recorded it), I get a VBA "Run-time error '438': Object doesn't support this property or method" and it takes me to the debugger. Any ideas on why this is happening and how I can get the labels on the chart? Thanks, -- John O |
Annotating charts with text from within VBA
Jon,
I am not surprised that you found several inefficiencies in the code. I do not profess to be a good programmer and am just learning about interacting with charts from VBA. Thank you for the suggested changes. I will try them out and let you know how it goes. One thing that I probably should mention is that the code that I posted is only part of a subroutine that is being called by another macro. The subroutine as I am using it is called by the following statement: Sub create_chart(chart_number, XaxisDataArray, YaxisDataArray, current_worksheet, current_component) I pass in the X data array (in XaxisDataArray defined as Variant in the calling subroutine), the Y data array (in YaxisDataArray defined as Variant in the calling subroutine), the current worksheet where the chart will be placed (in current_worksheet defined as String in the calling subroutine), the Y axis label (as current_component defined as Variant in the calling subroutine), and the chart number (is this the first, second, third, etc. chart on this worksheet defined as Integer in the calling subroutine and used to position the charts on the worksheet so they don't overlap). I create the chart object inside the subroutine by the command Set chrtobj = ActiveSheet.ChartObjects.Add(10, y_chart_position, 800, 300) where y_chart_position is defined as an Integer. So, if the subroutine doesn't know what the chrtobj is, I must be doing something to confuse it in my code between when I create the chart and when I try to add the text. If you want to see the entire subroutine as it is, I can send that to you or post it, whichever is preferred. I must warn you, though, that you will see plenty more inefficiencies in the code. Thanks. -- John O "Jon Peltier" wrote: I suspect Excel doesn't know what chrtobj is. You need to reference it somehow: Dim chrtobj As ChartObject Set chrtobj = ActiveSheet.ChartObjects(1) Or maybe use something like this as your command that creates the textbox: With ActiveChart.Shapes.AddLabel(msoTextOrientationHori zontal, 133.5, 105#, 0#, 0#) In fact, using AddLabel ends up with a black background, while AddTextbox has a white background. There are a lot of other inefficiencies in the code. This is streamlined a bit. The AutoFontScale is going to cause problems. The only way I could make it work is shown in the last line before End Sub. Sub ChartLabels() Dim chrtobj As ChartObject Set chrtobj = ActiveSheet.ChartObjects(1) ' add text box labels With chrtobj.Chart.Shapes.AddTextbox(msoTextOrientation Horizontal, 133.5, 105#, 0#, 0#) With .TextFrame .AutoSize = msoTrue .Characters.Text = "UPPER" With .Characters(Start:=1, Length:=5).Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With End With With .Fill .Visible = msoFalse .Solid .Transparency = 0# End With With .Line .Weight = 0.75 .DashStyle = msoLineSolid .Style = msoLineSingle .Transparency = 0# .Visible = msoTrue .ForeColor.SchemeColor = 8 .BackColor.RGB = RGB(255, 255, 255) ' TURNS IT BLACK End With .IncrementLeft -0.36 ' BUILD THESE INTO INITIAL DIMENSIONS .IncrementTop -8.98 ' (IN AddLabel STATEMENT ABOVE) End With chrtobj.Chart.TextBoxes(chrtobj.Chart.TextBoxes.Co unt).AutoScaleFont = False End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John O" wrote in message ... Jon, Sorry about that. I forgot to answer that question. It is on the second line in the code fragment that I posted (the first selection statement "Selection.ShapeRange(1).TextFrame.AutoSize = msoTrue"). -- John O "Jon Peltier" wrote: No, I asked which line within the code you posted is highlighted in yellow when the error occurs. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John O" wrote in message ... I just sent you the section that didn't run. Do you want me to post the entire subroutine so you can look at it? Thanks, -- John O "Jon Peltier" wrote: The code runs without error for me. What line is highlighted in the debugger? How did you define chrtobj? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John O" wrote in message ... I need to be able to add some text to charts ("Max", "Min", "Target" for example) from within an Excel VBA Macro. At this point I have been unable to get this to take place. The code that I had tried is as follows: ' add text box labels chrtobj.Chart.Shapes.AddLabel(msoTextOrientationHo rizontal, 133.5, 105#, 0# _ , 0#).Select Selection.ShapeRange(1).TextFrame.AutoSize = msoTrue Selection.Characters.Text = "UPPER" Selection.AutoScaleFont = False With Selection.Characters(Start:=1, Length:=5).Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Selection.ShapeRange.Fill.Visible = msoFalse Selection.ShapeRange.Fill.Solid Selection.ShapeRange.Fill.Transparency = 0# Selection.ShapeRange.Line.Weight = 0.75 Selection.ShapeRange.Line.DashStyle = msoLineSolid Selection.ShapeRange.Line.Style = msoLineSingle Selection.ShapeRange.Line.Transparency = 0# Selection.ShapeRange.Line.Visible = msoTrue Selection.ShapeRange.Line.ForeColor.SchemeColor = 8 Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255) Selection.ShapeRange.IncrementLeft -0.36 Selection.ShapeRange.IncrementTop -8.98 But when I use this code (which worked when I recorded it), I get a VBA "Run-time error '438': Object doesn't support this property or method" and it takes me to the debugger. Any ideas on why this is happening and how I can get the labels on the chart? Thanks, -- John O |
Annotating charts with text from within VBA
Jon,
The code you sent me works. Thanks again. Now I just have to figure out how to position the text inside the chart to where I want it. I want to locate it near one of the lines on the chart. I know what the data for that line is, but am not sure how to use that information to position the label. Do you have any suggestions? -- John O "Jon Peltier" wrote: I suspect Excel doesn't know what chrtobj is. You need to reference it somehow: Dim chrtobj As ChartObject Set chrtobj = ActiveSheet.ChartObjects(1) Or maybe use something like this as your command that creates the textbox: With ActiveChart.Shapes.AddLabel(msoTextOrientationHori zontal, 133.5, 105#, 0#, 0#) In fact, using AddLabel ends up with a black background, while AddTextbox has a white background. There are a lot of other inefficiencies in the code. This is streamlined a bit. The AutoFontScale is going to cause problems. The only way I could make it work is shown in the last line before End Sub. Sub ChartLabels() Dim chrtobj As ChartObject Set chrtobj = ActiveSheet.ChartObjects(1) ' add text box labels With chrtobj.Chart.Shapes.AddTextbox(msoTextOrientation Horizontal, 133.5, 105#, 0#, 0#) With .TextFrame .AutoSize = msoTrue .Characters.Text = "UPPER" With .Characters(Start:=1, Length:=5).Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With End With With .Fill .Visible = msoFalse .Solid .Transparency = 0# End With With .Line .Weight = 0.75 .DashStyle = msoLineSolid .Style = msoLineSingle .Transparency = 0# .Visible = msoTrue .ForeColor.SchemeColor = 8 .BackColor.RGB = RGB(255, 255, 255) ' TURNS IT BLACK End With .IncrementLeft -0.36 ' BUILD THESE INTO INITIAL DIMENSIONS .IncrementTop -8.98 ' (IN AddLabel STATEMENT ABOVE) End With chrtobj.Chart.TextBoxes(chrtobj.Chart.TextBoxes.Co unt).AutoScaleFont = False End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John O" wrote in message ... Jon, Sorry about that. I forgot to answer that question. It is on the second line in the code fragment that I posted (the first selection statement "Selection.ShapeRange(1).TextFrame.AutoSize = msoTrue"). -- John O "Jon Peltier" wrote: No, I asked which line within the code you posted is highlighted in yellow when the error occurs. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John O" wrote in message ... I just sent you the section that didn't run. Do you want me to post the entire subroutine so you can look at it? Thanks, -- John O "Jon Peltier" wrote: The code runs without error for me. What line is highlighted in the debugger? How did you define chrtobj? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John O" wrote in message ... I need to be able to add some text to charts ("Max", "Min", "Target" for example) from within an Excel VBA Macro. At this point I have been unable to get this to take place. The code that I had tried is as follows: ' add text box labels chrtobj.Chart.Shapes.AddLabel(msoTextOrientationHo rizontal, 133.5, 105#, 0# _ , 0#).Select Selection.ShapeRange(1).TextFrame.AutoSize = msoTrue Selection.Characters.Text = "UPPER" Selection.AutoScaleFont = False With Selection.Characters(Start:=1, Length:=5).Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Selection.ShapeRange.Fill.Visible = msoFalse Selection.ShapeRange.Fill.Solid Selection.ShapeRange.Fill.Transparency = 0# Selection.ShapeRange.Line.Weight = 0.75 Selection.ShapeRange.Line.DashStyle = msoLineSolid Selection.ShapeRange.Line.Style = msoLineSingle Selection.ShapeRange.Line.Transparency = 0# Selection.ShapeRange.Line.Visible = msoTrue Selection.ShapeRange.Line.ForeColor.SchemeColor = 8 Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255) Selection.ShapeRange.IncrementLeft -0.36 Selection.ShapeRange.IncrementTop -8.98 But when I use this code (which worked when I recorded it), I get a VBA "Run-time error '438': Object doesn't support this property or method" and it takes me to the debugger. Any ideas on why this is happening and how I can get the labels on the chart? Thanks, -- John O |
Annotating charts with text from within VBA
My bad. You said the problem was on the second line, and I read it as
"first" line. I had some issues with that line, which is why I played the silly game to stick the AutoFontScale to the end. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John O" wrote in message ... Jon, I am not surprised that you found several inefficiencies in the code. I do not profess to be a good programmer and am just learning about interacting with charts from VBA. Thank you for the suggested changes. I will try them out and let you know how it goes. One thing that I probably should mention is that the code that I posted is only part of a subroutine that is being called by another macro. The subroutine as I am using it is called by the following statement: Sub create_chart(chart_number, XaxisDataArray, YaxisDataArray, current_worksheet, current_component) I pass in the X data array (in XaxisDataArray defined as Variant in the calling subroutine), the Y data array (in YaxisDataArray defined as Variant in the calling subroutine), the current worksheet where the chart will be placed (in current_worksheet defined as String in the calling subroutine), the Y axis label (as current_component defined as Variant in the calling subroutine), and the chart number (is this the first, second, third, etc. chart on this worksheet defined as Integer in the calling subroutine and used to position the charts on the worksheet so they don't overlap). I create the chart object inside the subroutine by the command Set chrtobj = ActiveSheet.ChartObjects.Add(10, y_chart_position, 800, 300) where y_chart_position is defined as an Integer. So, if the subroutine doesn't know what the chrtobj is, I must be doing something to confuse it in my code between when I create the chart and when I try to add the text. If you want to see the entire subroutine as it is, I can send that to you or post it, whichever is preferred. I must warn you, though, that you will see plenty more inefficiencies in the code. Thanks. -- John O "Jon Peltier" wrote: I suspect Excel doesn't know what chrtobj is. You need to reference it somehow: Dim chrtobj As ChartObject Set chrtobj = ActiveSheet.ChartObjects(1) Or maybe use something like this as your command that creates the textbox: With ActiveChart.Shapes.AddLabel(msoTextOrientationHori zontal, 133.5, 105#, 0#, 0#) In fact, using AddLabel ends up with a black background, while AddTextbox has a white background. There are a lot of other inefficiencies in the code. This is streamlined a bit. The AutoFontScale is going to cause problems. The only way I could make it work is shown in the last line before End Sub. Sub ChartLabels() Dim chrtobj As ChartObject Set chrtobj = ActiveSheet.ChartObjects(1) ' add text box labels With chrtobj.Chart.Shapes.AddTextbox(msoTextOrientation Horizontal, 133.5, 105#, 0#, 0#) With .TextFrame .AutoSize = msoTrue .Characters.Text = "UPPER" With .Characters(Start:=1, Length:=5).Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With End With With .Fill .Visible = msoFalse .Solid .Transparency = 0# End With With .Line .Weight = 0.75 .DashStyle = msoLineSolid .Style = msoLineSingle .Transparency = 0# .Visible = msoTrue .ForeColor.SchemeColor = 8 .BackColor.RGB = RGB(255, 255, 255) ' TURNS IT BLACK End With .IncrementLeft -0.36 ' BUILD THESE INTO INITIAL DIMENSIONS .IncrementTop -8.98 ' (IN AddLabel STATEMENT ABOVE) End With chrtobj.Chart.TextBoxes(chrtobj.Chart.TextBoxes.Co unt).AutoScaleFont = False End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John O" wrote in message ... Jon, Sorry about that. I forgot to answer that question. It is on the second line in the code fragment that I posted (the first selection statement "Selection.ShapeRange(1).TextFrame.AutoSize = msoTrue"). -- John O "Jon Peltier" wrote: No, I asked which line within the code you posted is highlighted in yellow when the error occurs. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John O" wrote in message ... I just sent you the section that didn't run. Do you want me to post the entire subroutine so you can look at it? Thanks, -- John O "Jon Peltier" wrote: The code runs without error for me. What line is highlighted in the debugger? How did you define chrtobj? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John O" wrote in message ... I need to be able to add some text to charts ("Max", "Min", "Target" for example) from within an Excel VBA Macro. At this point I have been unable to get this to take place. The code that I had tried is as follows: ' add text box labels chrtobj.Chart.Shapes.AddLabel(msoTextOrientationHo rizontal, 133.5, 105#, 0# _ , 0#).Select Selection.ShapeRange(1).TextFrame.AutoSize = msoTrue Selection.Characters.Text = "UPPER" Selection.AutoScaleFont = False With Selection.Characters(Start:=1, Length:=5).Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Selection.ShapeRange.Fill.Visible = msoFalse Selection.ShapeRange.Fill.Solid Selection.ShapeRange.Fill.Transparency = 0# Selection.ShapeRange.Line.Weight = 0.75 Selection.ShapeRange.Line.DashStyle = msoLineSolid Selection.ShapeRange.Line.Style = msoLineSingle Selection.ShapeRange.Line.Transparency = 0# Selection.ShapeRange.Line.Visible = msoTrue Selection.ShapeRange.Line.ForeColor.SchemeColor = 8 Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255) Selection.ShapeRange.IncrementLeft -0.36 Selection.ShapeRange.IncrementTop -8.98 But when I use this code (which worked when I recorded it), I get a VBA "Run-time error '438': Object doesn't support this property or method" and it takes me to the debugger. Any ideas on why this is happening and how I can get the labels on the chart? Thanks, -- John O |
Annotating charts with text from within VBA
You can add the text as the data label for a data point, and it will move as
the point moves. If none of your data goes where you want the label, add a dummy XY series with a point where the label goes, then hide the dummy series (no lines, no markers). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John O" wrote in message ... Jon, The code you sent me works. Thanks again. Now I just have to figure out how to position the text inside the chart to where I want it. I want to locate it near one of the lines on the chart. I know what the data for that line is, but am not sure how to use that information to position the label. Do you have any suggestions? -- John O "Jon Peltier" wrote: I suspect Excel doesn't know what chrtobj is. You need to reference it somehow: Dim chrtobj As ChartObject Set chrtobj = ActiveSheet.ChartObjects(1) Or maybe use something like this as your command that creates the textbox: With ActiveChart.Shapes.AddLabel(msoTextOrientationHori zontal, 133.5, 105#, 0#, 0#) In fact, using AddLabel ends up with a black background, while AddTextbox has a white background. There are a lot of other inefficiencies in the code. This is streamlined a bit. The AutoFontScale is going to cause problems. The only way I could make it work is shown in the last line before End Sub. Sub ChartLabels() Dim chrtobj As ChartObject Set chrtobj = ActiveSheet.ChartObjects(1) ' add text box labels With chrtobj.Chart.Shapes.AddTextbox(msoTextOrientation Horizontal, 133.5, 105#, 0#, 0#) With .TextFrame .AutoSize = msoTrue .Characters.Text = "UPPER" With .Characters(Start:=1, Length:=5).Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With End With With .Fill .Visible = msoFalse .Solid .Transparency = 0# End With With .Line .Weight = 0.75 .DashStyle = msoLineSolid .Style = msoLineSingle .Transparency = 0# .Visible = msoTrue .ForeColor.SchemeColor = 8 .BackColor.RGB = RGB(255, 255, 255) ' TURNS IT BLACK End With .IncrementLeft -0.36 ' BUILD THESE INTO INITIAL DIMENSIONS .IncrementTop -8.98 ' (IN AddLabel STATEMENT ABOVE) End With chrtobj.Chart.TextBoxes(chrtobj.Chart.TextBoxes.Co unt).AutoScaleFont = False End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John O" wrote in message ... Jon, Sorry about that. I forgot to answer that question. It is on the second line in the code fragment that I posted (the first selection statement "Selection.ShapeRange(1).TextFrame.AutoSize = msoTrue"). -- John O "Jon Peltier" wrote: No, I asked which line within the code you posted is highlighted in yellow when the error occurs. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John O" wrote in message ... I just sent you the section that didn't run. Do you want me to post the entire subroutine so you can look at it? Thanks, -- John O "Jon Peltier" wrote: The code runs without error for me. What line is highlighted in the debugger? How did you define chrtobj? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John O" wrote in message ... I need to be able to add some text to charts ("Max", "Min", "Target" for example) from within an Excel VBA Macro. At this point I have been unable to get this to take place. The code that I had tried is as follows: ' add text box labels chrtobj.Chart.Shapes.AddLabel(msoTextOrientationHo rizontal, 133.5, 105#, 0# _ , 0#).Select Selection.ShapeRange(1).TextFrame.AutoSize = msoTrue Selection.Characters.Text = "UPPER" Selection.AutoScaleFont = False With Selection.Characters(Start:=1, Length:=5).Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Selection.ShapeRange.Fill.Visible = msoFalse Selection.ShapeRange.Fill.Solid Selection.ShapeRange.Fill.Transparency = 0# Selection.ShapeRange.Line.Weight = 0.75 Selection.ShapeRange.Line.DashStyle = msoLineSolid Selection.ShapeRange.Line.Style = msoLineSingle Selection.ShapeRange.Line.Transparency = 0# Selection.ShapeRange.Line.Visible = msoTrue Selection.ShapeRange.Line.ForeColor.SchemeColor = 8 Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255) Selection.ShapeRange.IncrementLeft -0.36 Selection.ShapeRange.IncrementTop -8.98 But when I use this code (which worked when I recorded it), I get a VBA "Run-time error '438': Object doesn't support this property or method" and it takes me to the debugger. Any ideas on why this is happening and how I can get the labels on the chart? Thanks, -- John O |
Annotating charts with text from within VBA
Jon,
I am very interested in your suggestion about adding the data label to a point. I did not realize you could do this. This would be exactly what I need. I tried to get the coding for this by recording a macro and going through the process of adding a data label to a point, but when I paste that code into my macro that is creating the chart, it fails. The code that was recorded for me is ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(1).Points(12).Select ActiveChart.SeriesCollection(1).Points(12).ApplyDa taLabels AutoText:=True, _ LegendKey:=False, ShoSeriesName:=True, ShowCategoryName:=False, _ ShowValue:=False, ShowPercentage:=False, ShowBubbleSize:=False ActiveChart.PlotArea.Select ActiveChart.SeriesCollection(1).Name = "=""UPPER""" ActiveChart.SeriesCollection(1).DataLabels.Select With Selection.Border .ColorIndex = 57 .Weight = xlHairline .LineStyle = xlContinuous End With Selection.Shadow = False With Selection.Interior .ColorIndex = 2 .PatternColorIndex = 1 .Pattern = xlSolid End With When I paste it into my macro code, I am doing some additional formatting of the chart, so I pasted it into that section. This is what that code looks like: With chartobj.Chart.SeriesCollection(1) .Border.ColorIndex = 5 .Border.Weight = xlMedium .Border.LineStyle = xlContinuous .MarkerBackgroundColorIndex = 5 + i .MarkerForegroundColorIndex = 5 + i .MarkerStyle = xlDash .Smooth = False .MarkerSize = 2 .Shadow = False .Name = "UPPER" With .DataLabels.Border <--- this line gives an error "Unable to set the ColorIndex property of the Border class" .ColorIndex = 57 .Weight = xlHairline .LineStyle = xlContinuous End With With .DataLabels.Interior <--- this line gives an error "Unable to get the Interior property of the DataLabels class" .ColorIndex = 2 .PatternColorIndex = 1 .Pattern = xlSolid End With .Points(1).ApplyDataLabels AutoText:=True, _ LegendKey:=False, ShowSeriesName:=True, ShowCategoryName:=False, _ ShowValue:=False, ShowPercentage:=False, ShowBubbleSize:=False End With If I skip over the 2 With sections that fail, the .Points statement works and displays the data point label (not boxed or with a white background which is what the Border and Interior With sections were supposed to do). I admit that I do not know much about macros, so it may be something quite obvious that I am doing wrong, but if you (or someone else watching this thread) can tell me how to correct the error and get my data point label displayed with a border and a white background, I would really appreciate it. Thanks, -- John O "Jon Peltier" wrote: You can add the text as the data label for a data point, and it will move as the point moves. If none of your data goes where you want the label, add a dummy XY series with a point where the label goes, then hide the dummy series (no lines, no markers). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John O" wrote in message ... Jon, The code you sent me works. Thanks again. Now I just have to figure out how to position the text inside the chart to where I want it. I want to locate it near one of the lines on the chart. I know what the data for that line is, but am not sure how to use that information to position the label. Do you have any suggestions? -- John O "Jon Peltier" wrote: I suspect Excel doesn't know what chrtobj is. You need to reference it somehow: Dim chrtobj As ChartObject Set chrtobj = ActiveSheet.ChartObjects(1) Or maybe use something like this as your command that creates the textbox: With ActiveChart.Shapes.AddLabel(msoTextOrientationHori zontal, 133.5, 105#, 0#, 0#) In fact, using AddLabel ends up with a black background, while AddTextbox has a white background. There are a lot of other inefficiencies in the code. This is streamlined a bit. The AutoFontScale is going to cause problems. The only way I could make it work is shown in the last line before End Sub. Sub ChartLabels() Dim chrtobj As ChartObject Set chrtobj = ActiveSheet.ChartObjects(1) ' add text box labels With chrtobj.Chart.Shapes.AddTextbox(msoTextOrientation Horizontal, 133.5, 105#, 0#, 0#) With .TextFrame .AutoSize = msoTrue .Characters.Text = "UPPER" With .Characters(Start:=1, Length:=5).Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With End With With .Fill .Visible = msoFalse .Solid .Transparency = 0# End With With .Line .Weight = 0.75 .DashStyle = msoLineSolid .Style = msoLineSingle .Transparency = 0# .Visible = msoTrue .ForeColor.SchemeColor = 8 .BackColor.RGB = RGB(255, 255, 255) ' TURNS IT BLACK End With .IncrementLeft -0.36 ' BUILD THESE INTO INITIAL DIMENSIONS .IncrementTop -8.98 ' (IN AddLabel STATEMENT ABOVE) End With chrtobj.Chart.TextBoxes(chrtobj.Chart.TextBoxes.Co unt).AutoScaleFont = False End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John O" wrote in message ... Jon, Sorry about that. I forgot to answer that question. It is on the second line in the code fragment that I posted (the first selection statement "Selection.ShapeRange(1).TextFrame.AutoSize = msoTrue"). -- John O "Jon Peltier" wrote: No, I asked which line within the code you posted is highlighted in yellow when the error occurs. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John O" wrote in message ... I just sent you the section that didn't run. Do you want me to post the entire subroutine so you can look at it? Thanks, -- John O "Jon Peltier" wrote: The code runs without error for me. What line is highlighted in the debugger? How did you define chrtobj? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John O" wrote in message ... I need to be able to add some text to charts ("Max", "Min", "Target" for example) from within an Excel VBA Macro. At this point I have been unable to get this to take place. The code that I had tried is as follows: ' add text box labels chrtobj.Chart.Shapes.AddLabel(msoTextOrientationHo rizontal, 133.5, 105#, 0# _ , 0#).Select Selection.ShapeRange(1).TextFrame.AutoSize = msoTrue Selection.Characters.Text = "UPPER" Selection.AutoScaleFont = False With Selection.Characters(Start:=1, Length:=5).Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Selection.ShapeRange.Fill.Visible = msoFalse Selection.ShapeRange.Fill.Solid Selection.ShapeRange.Fill.Transparency = 0# Selection.ShapeRange.Line.Weight = 0.75 Selection.ShapeRange.Line.DashStyle = msoLineSolid Selection.ShapeRange.Line.Style = msoLineSingle Selection.ShapeRange.Line.Transparency = 0# Selection.ShapeRange.Line.Visible = msoTrue Selection.ShapeRange.Line.ForeColor.SchemeColor = 8 Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255) Selection.ShapeRange.IncrementLeft -0.36 Selection.ShapeRange.IncrementTop -8.98 But when I use this code (which worked when I recorded it), I get a VBA "Run-time error '438': Object doesn't support this property or method" and it takes me to the debugger. Any ideas on why this is happening and how I can get the labels on the chart? Thanks, -- John O |
Annotating charts with text from within VBA
Jon,
You can ignore my previous post. I figured out why it was giving me the error. I should have kept the .points.applydatalabels statement before the 2 with statements. I guess this is because until I apply the data labels, there is nothing for the border or interior property to contain. I think I understand a bit better now how some of these properties work. Thanks for all your help. I really appreciate it. -- John O "John O" wrote: Jon, I am very interested in your suggestion about adding the data label to a point. I did not realize you could do this. This would be exactly what I need. I tried to get the coding for this by recording a macro and going through the process of adding a data label to a point, but when I paste that code into my macro that is creating the chart, it fails. The code that was recorded for me is ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(1).Points(12).Select ActiveChart.SeriesCollection(1).Points(12).ApplyDa taLabels AutoText:=True, _ LegendKey:=False, ShoSeriesName:=True, ShowCategoryName:=False, _ ShowValue:=False, ShowPercentage:=False, ShowBubbleSize:=False ActiveChart.PlotArea.Select ActiveChart.SeriesCollection(1).Name = "=""UPPER""" ActiveChart.SeriesCollection(1).DataLabels.Select With Selection.Border .ColorIndex = 57 .Weight = xlHairline .LineStyle = xlContinuous End With Selection.Shadow = False With Selection.Interior .ColorIndex = 2 .PatternColorIndex = 1 .Pattern = xlSolid End With When I paste it into my macro code, I am doing some additional formatting of the chart, so I pasted it into that section. This is what that code looks like: With chartobj.Chart.SeriesCollection(1) .Border.ColorIndex = 5 .Border.Weight = xlMedium .Border.LineStyle = xlContinuous .MarkerBackgroundColorIndex = 5 + i .MarkerForegroundColorIndex = 5 + i .MarkerStyle = xlDash .Smooth = False .MarkerSize = 2 .Shadow = False .Name = "UPPER" With .DataLabels.Border <--- this line gives an error "Unable to set the ColorIndex property of the Border class" .ColorIndex = 57 .Weight = xlHairline .LineStyle = xlContinuous End With With .DataLabels.Interior <--- this line gives an error "Unable to get the Interior property of the DataLabels class" .ColorIndex = 2 .PatternColorIndex = 1 .Pattern = xlSolid End With .Points(1).ApplyDataLabels AutoText:=True, _ LegendKey:=False, ShowSeriesName:=True, ShowCategoryName:=False, _ ShowValue:=False, ShowPercentage:=False, ShowBubbleSize:=False End With If I skip over the 2 With sections that fail, the .Points statement works and displays the data point label (not boxed or with a white background which is what the Border and Interior With sections were supposed to do). I admit that I do not know much about macros, so it may be something quite obvious that I am doing wrong, but if you (or someone else watching this thread) can tell me how to correct the error and get my data point label displayed with a border and a white background, I would really appreciate it. Thanks, -- John O "Jon Peltier" wrote: You can add the text as the data label for a data point, and it will move as the point moves. If none of your data goes where you want the label, add a dummy XY series with a point where the label goes, then hide the dummy series (no lines, no markers). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John O" wrote in message ... Jon, The code you sent me works. Thanks again. Now I just have to figure out how to position the text inside the chart to where I want it. I want to locate it near one of the lines on the chart. I know what the data for that line is, but am not sure how to use that information to position the label. Do you have any suggestions? -- John O "Jon Peltier" wrote: I suspect Excel doesn't know what chrtobj is. You need to reference it somehow: Dim chrtobj As ChartObject Set chrtobj = ActiveSheet.ChartObjects(1) Or maybe use something like this as your command that creates the textbox: With ActiveChart.Shapes.AddLabel(msoTextOrientationHori zontal, 133.5, 105#, 0#, 0#) In fact, using AddLabel ends up with a black background, while AddTextbox has a white background. There are a lot of other inefficiencies in the code. This is streamlined a bit. The AutoFontScale is going to cause problems. The only way I could make it work is shown in the last line before End Sub. Sub ChartLabels() Dim chrtobj As ChartObject Set chrtobj = ActiveSheet.ChartObjects(1) ' add text box labels With chrtobj.Chart.Shapes.AddTextbox(msoTextOrientation Horizontal, 133.5, 105#, 0#, 0#) With .TextFrame .AutoSize = msoTrue .Characters.Text = "UPPER" With .Characters(Start:=1, Length:=5).Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With End With With .Fill .Visible = msoFalse .Solid .Transparency = 0# End With With .Line .Weight = 0.75 .DashStyle = msoLineSolid .Style = msoLineSingle .Transparency = 0# .Visible = msoTrue .ForeColor.SchemeColor = 8 .BackColor.RGB = RGB(255, 255, 255) ' TURNS IT BLACK End With .IncrementLeft -0.36 ' BUILD THESE INTO INITIAL DIMENSIONS .IncrementTop -8.98 ' (IN AddLabel STATEMENT ABOVE) End With chrtobj.Chart.TextBoxes(chrtobj.Chart.TextBoxes.Co unt).AutoScaleFont = False End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John O" wrote in message ... Jon, Sorry about that. I forgot to answer that question. It is on the second line in the code fragment that I posted (the first selection statement "Selection.ShapeRange(1).TextFrame.AutoSize = msoTrue"). -- John O "Jon Peltier" wrote: No, I asked which line within the code you posted is highlighted in yellow when the error occurs. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John O" wrote in message ... I just sent you the section that didn't run. Do you want me to post the entire subroutine so you can look at it? Thanks, -- John O "Jon Peltier" wrote: The code runs without error for me. What line is highlighted in the debugger? How did you define chrtobj? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John O" wrote in message ... I need to be able to add some text to charts ("Max", "Min", "Target" for example) from within an Excel VBA Macro. At this point I have been unable to get this to take place. The code that I had tried is as follows: ' add text box labels chrtobj.Chart.Shapes.AddLabel(msoTextOrientationHo rizontal, 133.5, 105#, 0# _ , 0#).Select Selection.ShapeRange(1).TextFrame.AutoSize = msoTrue Selection.Characters.Text = "UPPER" Selection.AutoScaleFont = False With Selection.Characters(Start:=1, Length:=5).Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Selection.ShapeRange.Fill.Visible = msoFalse Selection.ShapeRange.Fill.Solid Selection.ShapeRange.Fill.Transparency = 0# Selection.ShapeRange.Line.Weight = 0.75 Selection.ShapeRange.Line.DashStyle = msoLineSolid Selection.ShapeRange.Line.Style = msoLineSingle Selection.ShapeRange.Line.Transparency = 0# Selection.ShapeRange.Line.Visible = msoTrue Selection.ShapeRange.Line.ForeColor.SchemeColor = 8 Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255) Selection.ShapeRange.IncrementLeft -0.36 Selection.ShapeRange.IncrementTop -8.98 But when I use this code (which worked when I recorded it), I get a VBA "Run-time error '438': Object doesn't support this property or method" and it takes me to the debugger. Any ideas on why this is happening and how I can get the labels on the chart? Thanks, -- John O |
| All times are GMT +1. The time now is 01:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com