Adding comment to charts

[Flopski]

I have a sheet with 60 charts and wanted to add a comment (essentially a date) which is on a different sheet to the charts. I have VBA code to add a comment via text box but can't manage to link this to a cell (see below - I want to replace "Hello world" with a cell reference on a different sheet to the charts. I have also found that each time I run the macro it creates a new textbox ("TextBox4" for example) and places this on top of the previous ones. I would only want one comment each time it is updated i.e. delete the existing comment and replacing with a new one.

 ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 1, 1, 100, 20).TextFrame.Characters.Text = "Hello World"

 

[Hui]

Flopski

Try the following technique

Sub LoopThroughCharts_AddTextBox()

Dim CurrentSheet As Worksheet
Dim cht As ChartObject

Application.ScreenUpdating = False
Application.EnableEvents = False

Set CurrentSheet = ActiveSheet

ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 180, 20, 75, 25).Select
Selection.Formula = "=Sheet2!C2" 'Change to suit your text reference
Selection.Copy

For Each cht In CurrentSheet.ChartObjects
  cht.Activate
  ActiveChart.Paste

Next cht

ActiveSheet.Shapes(ActiveSheet.Shapes.Count).Delete

CurrentSheet.Activate
Application.EnableEvents = True

End Sub

 

[Flopski]

Thanks for that. I think I need to change the loop structure as each chart has its own date to add in and this looks like it just copies the same data into each chart from this

Selection.Formula = "=Sheet2!C2"

 

[Hui]

I would add a text box outside the chart
Copy it
Paste it in to the chart
Delete source text box

Repeat for other charts

It is a lot simpler than trying to access the Textbox formula option inside the chart

 

[Flopski]

Hi - just coming back to this and realise the deletion of the textbox hasn't worked and now some of the charts have multiple textboxes on top of each other. Is there an easy way to delete these?

 

[Hui]

Goto the Selection Pane
on the Page Layout tab