VBA: Adding contents of a cell to a chart as a textbox (or image,

[CaroKann]

Hi,

I am in the process of creating a macro which subtotals sheets by a column
value (which contains survey respondents' ID, such as "Respondent 205", etc).
The subtotals compute averages.

I have written code which find's each person's range of averages and plots
them. Each person gets their own chart and the cell references are never
absolute, because this macro will be applied to sheets with varying numbers
of people/charts and varying observations (rows) per person.

The problem is that I can not make "ActiveChart.SeriesCollection(1).Name ="
work with a variable or (relative) named range - unless I am wrong it is very
inflexible. Thus it is a dead end.

So what I am hoping is possible is that I can have the macro select a cell
with the respondent's ID and paste/transfer that info to the chart. I can
select the correct cell with a reference like "ActiveCell.Offset(0,
-2).Range("A1").Select)" where the original activecell is selected by my
find/name-range macros I mentioned above.

I can not figure out how to get this info on the chart (I don't want it
added to a series - I just want it to appear on the chart somewhere, in some
blank space or perhaps in the title).

Any ideas?

Thanks very much in advance for your help

Jason

P.S. These charts are their own tabs, not embedded within a sheet

 

[Peter T]

Name the cell that contains the series name "SER_1" and try the following

Sub test()

Dim sName As String, s As String
sName = "SER_1"
s = "='" & ActiveWorkbook.Name & "'!" & sName
ActiveChart.SeriesCollection(1).Name = s

End Sub

If I follow what you want and if the above works, I assume you won't need
the textbox

Regards,
Peter T

 

[CaroKann]

Peter (Mr. T),

Thanks much for your timely help!

Unfortunately when I do this the series name for every chart actually shows
up as the letter "s". I think I tried an approach similar to this yesterday
with the name result.

But thanks and if you can think of anything else to try, by all means let me
know. Partial code is pasted below.

Thanks

Jason

Sub test()

Dim SER_1 As String
SER_1 = CStr(ActiveCell.Offset(0, -2).Range("A1").Select)

Dim sName As String, s As String
sName = "SER_1"
s = "='" & ActiveWorkbook.Name & "'!" & sName

Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData
Source:=Sheets("Sheet1").Range("Currentselection"), PlotBy:= _
xlRows
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R1C4:R1C20"
ActiveChart.SeriesCollection(1).Name = "s"
ActiveChart.SeriesCollection(2).Values = "=Sheet2!R1C2:R1C18"
ActiveChart.SeriesCollection(2).Name = "=Sheet2!R1C1"
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Confidential Report"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Attributes"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Intensity"
End With
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlTop
ActiveChart.HasDataTable = False
ActiveChart.ChartTitle.Select
ActiveChart.Legend.Select
Selection.Left = 242
Selection.Top = 53
ActiveChart.ChartArea.Select
ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 210.84,
30.89, _
273.48, 14.12).Select
Selection.Characters.Text = " Performance"
Selection.AutoScaleFont = False
With Selection.Characters(Start:=1, Length:=43).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.IncrementLeft 15#
Selection.ShapeRange.IncrementTop -0.03
ActiveChart.Shapes("Text Box 1").Select
Selection.Characters.Text = "Performance"
Selection.AutoScaleFont = False
With Selection.Characters(Start:=1, Length:=43).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveChart.ChartArea.Select
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 1
.MaximumScale = 7
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 4.41, 3.53, _
137.62, 57.36).Select
Selection.Characters.Text = "Number of times you attended:"
Selection.AutoScaleFont = False
With Selection.Characters(Start:=1, Length:=43).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 41
End With
Selection.ShapeRange.ScaleWidth 1.06, msoFalse, msoScaleFromTopLeft
ActiveChart.ChartArea.Select
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 15
.Weight = xlThin
.LineStyle = xlContinuous
End With
Selection.Interior.ColorIndex = xlNone
ActiveChart.ChartArea.Select
End Sub

 

[CaroKann]

P.S. I tried it again without the quotation marks - that was my mistake - but
it just gives you a run time error 1004 - object/method not supported.

I think this happened yesterday which is why I say the series name is not
flexible.

Perhaps there's a way to get the info on the charts other than the legend
(textbox, etc?). But I know that, vba aside, Excel won't let me paste from a
cell to a chart like that...

Thanks

Jason

 

[Peter T]

Some of misunderstanding going on here!

I intended "Ser_1" to be a defined name that referred to a cell that
contains the series name (select the cell, in the names box left of input
bar enter the name SER_1, or whatever)

I thought that was what you wanted. However, looking at your code it seems
you want an offset from the activecell at the time you create your chart.

Dim rCell As Range
Set rCell = ActiveCell.Offset(0, -2)

' code to add chart etc

ActiveChart.SeriesCollection(1).Name = rCell

When done look at the series formula, the first argument should rfer to the
address of the cell containing the series name.

Regards,
Peter T

 

[CaroKann]

OMG IT WORKS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

For some reason when you run the module by itself it still gives me the 1004
run time error, but when I execute the whole macro in a spread sheet IT
FINALLY WORKS

Thanks so much Pete!!

Jason