| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
Guest
Posts: n/a
|
|
|
|
||
|
|
| |
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message news  0F5824B-9B87-49A2-BEEB-(E-Mail Removed)...>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" <(E-Mail Removed)> wrote in message >> news:541A5E54-AFCC-43CC-B6FD-(E-Mail Removed)... >> >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(msoTextOrientationHorizontal, 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 >> >> >> |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message > news 0F5824B-9B87-49A2-BEEB-(E-Mail Removed)...> >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" <(E-Mail Removed)> wrote in message > >> news:541A5E54-AFCC-43CC-B6FD-(E-Mail Removed)... > >> >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(msoTextOrientationHorizontal, 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 > >> > >> > >> > > > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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(msoTextOrientationHorizontal, 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(msoTextOrientationHorizontal, 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.Count).AutoScaleFont = False End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John O" <(E-Mail Removed)> wrote in message news:8A298E32-B56B-4D6A-AEFB-(E-Mail Removed)... > 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" <(E-Mail Removed)> wrote in message >> news 0F5824B-9B87-49A2-BEEB-(E-Mail Removed)...>> >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" <(E-Mail Removed)> wrote in message >> >> news:541A5E54-AFCC-43CC-B6FD-(E-Mail Removed)... >> >> >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(msoTextOrientationHorizontal, >> >> > 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 >> >> >> >> >> >> >> >> >> |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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(msoTextOrientationHorizontal, 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(msoTextOrientationHorizontal, 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.Count).AutoScaleFont = > False > End Sub > > > - Jon > ------- > Jon Peltier, Microsoft Excel MVP > Tutorials and Custom Solutions > Peltier Technical Services, Inc. - http://PeltierTech.com > _______ > > > > "John O" <(E-Mail Removed)> wrote in message > news:8A298E32-B56B-4D6A-AEFB-(E-Mail Removed)... > > 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" <(E-Mail Removed)> wrote in message > >> news 0F5824B-9B87-49A2-BEEB-(E-Mail Removed)...> >> >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" <(E-Mail Removed)> wrote in message > >> >> news:541A5E54-AFCC-43CC-B6FD-(E-Mail Removed)... > >> >> >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(msoTextOrientationHorizontal, > >> >> > 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 > >> >> > >> >> > >> >> > >> > >> > >> > > > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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(msoTextOrientationHorizontal, 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(msoTextOrientationHorizontal, 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.Count).AutoScaleFont = > False > End Sub > > > - Jon > ------- > Jon Peltier, Microsoft Excel MVP > Tutorials and Custom Solutions > Peltier Technical Services, Inc. - http://PeltierTech.com > _______ > > > > "John O" <(E-Mail Removed)> wrote in message > news:8A298E32-B56B-4D6A-AEFB-(E-Mail Removed)... > > 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" <(E-Mail Removed)> wrote in message > >> news 0F5824B-9B87-49A2-BEEB-(E-Mail Removed)...> >> >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" <(E-Mail Removed)> wrote in message > >> >> news:541A5E54-AFCC-43CC-B6FD-(E-Mail Removed)... > >> >> >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(msoTextOrientationHorizontal, > >> >> > 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 > >> >> > >> >> > >> >> > >> > >> > >> > > > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message news:AD76928E-A184-45A0-BB20-(E-Mail Removed)... > 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(msoTextOrientationHorizontal, 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(msoTextOrientationHorizontal, >> 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.Count).AutoScaleFont = >> False >> End Sub >> >> >> - Jon >> ------- >> Jon Peltier, Microsoft Excel MVP >> Tutorials and Custom Solutions >> Peltier Technical Services, Inc. - http://PeltierTech.com >> _______ >> >> >> >> "John O" <(E-Mail Removed)> wrote in message >> news:8A298E32-B56B-4D6A-AEFB-(E-Mail Removed)... >> > 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" <(E-Mail Removed)> wrote in message >> >> news 0F5824B-9B87-49A2-BEEB-(E-Mail Removed)...>> >> >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" <(E-Mail Removed)> wrote in message >> >> >> news:541A5E54-AFCC-43CC-B6FD-(E-Mail Removed)... >> >> >> >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(msoTextOrientationHorizontal, >> >> >> > 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 >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message news:79E13041-C6FC-4AD0-9705-(E-Mail Removed)... > 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(msoTextOrientationHorizontal, 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(msoTextOrientationHorizontal, >> 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.Count).AutoScaleFont = >> False >> End Sub >> >> >> - Jon >> ------- >> Jon Peltier, Microsoft Excel MVP >> Tutorials and Custom Solutions >> Peltier Technical Services, Inc. - http://PeltierTech.com >> _______ >> >> >> >> "John O" <(E-Mail Removed)> wrote in message >> news:8A298E32-B56B-4D6A-AEFB-(E-Mail Removed)... >> > 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" <(E-Mail Removed)> wrote in message >> >> news 0F5824B-9B87-49A2-BEEB-(E-Mail Removed)...>> >> >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" <(E-Mail Removed)> wrote in message >> >> >> news:541A5E54-AFCC-43CC-B6FD-(E-Mail Removed)... >> >> >> >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(msoTextOrientationHorizontal, >> >> >> > 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 >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> |
|
|
|
|
|||
|
|
| |
|
| Thread Tools | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Annotating charts in Excel | rewb2@yahoo.co.uk | Microsoft Excel Discussion | 1 | 21st Sep 2008 02:28 PM |
| Annotating email from someone else | Paul | Microsoft Outlook Discussion | 0 | 6th Aug 2008 05:56 AM |
| Symbols for annotating a text for elocution purposes | =?Utf-8?B?bXRtdHQ=?= | Microsoft Word Document Management | 1 | 15th Feb 2007 11:15 AM |
| annotating charts | Rob Hargreaves | Microsoft Excel Charting | 0 | 9th Jun 2005 06:17 PM |
| paste-linking annotating text boxes | K. Georgiadis | Microsoft Excel Charting | 1 | 21st May 2004 02:38 AM |
