View Full Version : Solved: Why does this not add borders to the textbox added on the active chart?

02-22-2009, 04:16 PM
Hi Everybody

I have now, with the help of you all, been able to add a textbox on a chart in a worksheet. All the other relevant parameters of the textbox have also been specified and seem to work.

But I am still not able to add the borders to this text box. I have tried all I know.

Can someone please help!

I wonder if this is that compicated but who knows!

Sub CopyChartsAsPictures()
Dim thisObjectTop As Long, thisObjectLeft As Long
Dim myDocument As Worksheet
Dim thisSheetChartCount As Integer, start1 As Integer, wrkg1 As Integer

Dim plotAreaLeft As Double, plotAreaWidth As Double, plotAreaTop As Double, plotAreaHeight As Double
Dim chartArealeft As Double, chartAreaWidth As Double, chartAreaHeight As Double, chartAreaTop As Double
Dim chartTitleHeight As Double, chartTitleWidth As Double
Dim valueAxisLeft As Double, valueAxisTop As Double
Dim test1 As Double, test2 As Double, test3 As Double, test4 As Double

Dim chartRowConstant As Integer
Dim graphSheetName As String
graphSheetName = "Comparative Graphs"
ActiveSheet.Cells(1, 1).Select
Set myDocument = ActiveSheet
thisSheetChartCount = myDocument.ChartObjects.Count
start1 = 4: wrkg1 = start1

Dim shapesCount As Long, i As Long
Dim thisShapeName As String

Do While wrkg1 <= thisSheetChartCount
thisObjectTop = Selection.Top
thisObjectLeft = Selection.Left

With ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 0, 0, 100, 20).TextFrame
.Characters.Text = "Data Valid to FY 2"
.AutoSize = True: .AutoMargins = False
.MarginBottom = 0: .MarginLeft = 0
.MarginRight = 0: .MarginTop = 0
.HorizontalAlignment = xlHAlignCenter
.VerticalAlignment = xlVAlignCenter
With .Characters.Font
.Name = "Times New Roman": .Size = 10
.FontStyle = "Bold": .ColorIndex = 3
End With
End With

shapesCount = ActiveChart.Shapes.Count
For i = 1 To shapesCount
If ActiveChart.Shapes(i).Type = msoTextBox Then
ActiveChart.Shapes(i).Line.ForeColor.RGB = RGB(255, 0, 0)
' ActiveChart.Shapes(i).Top = -4 'thisObjectTop
' ActiveChart.Shapes(i).Left = -4 'thisObjectLeft
End If


thisShapeName = ActiveChart.Shapes(i).Name
Next i


With Selection
.ShapeRange.Line.Weight = 0.75
.ShapeRange.Line.DashStyle = msoLineSolid
.ShapeRange.Line.Style = msoLineSingle
.ShapeRange.Line.Visible = msoTrue
.ShapeRange.Line.ForeColor.SchemeColor = 64
.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
End With


With myDocument.ChartObjects(wrkg1).Chart
.HasTitle = True
plotAreaLeft = .PlotArea.Left: chartArealeft = .ChartArea.Left
plotAreaTop = .PlotArea.Top: chartAreaTop = .ChartArea.Top
plotAreaWidth = .PlotArea.Width: chartAreaWidth = .ChartArea.Width
plotAreaHeight = .PlotArea.Height: chartAreaHeight = .ChartArea.Height
valueAxisLeft = .Axes(xlValue).Left: valueAxisTop = .Axes(xlValue).Top
.ChartTitle.Top = chartAreaHeight
chartTitleHeight = (.ChartArea.Height - .ChartTitle.Top)
'.ChartTitle.Top = (chartAreaTop + Round(((plotAreaTop - chartAreaTop - chartTitleHeight) / 2), 0)) - Orig

.ChartTitle.Top = (Round(((valueAxisTop - chartAreaTop - chartTitleHeight) / 2), 0)) 'This centres better between top of plot area and top of Chart Borders

.ChartTitle.Left = chartAreaWidth
chartTitleWidth = (.ChartArea.Width - .ChartTitle.Left)
'.ChartTitle.Left = .Axes(xlValue).Left + Round(((plotAreaWidth - .Axes(xlValue).Left - chartTitleWidth) / 2), 0) - Orig

.ChartTitle.Left = .Axes(xlValue).Left + Round(((plotAreaWidth + plotAreaLeft - .Axes(xlValue).Left - chartTitleWidth) / 2), 0) 'This centres better between value axis and plot area width

test1 = .ChartTitle.Top
test2 = .ChartTitle.Left
test3 = .Axes(xlValue).Top
test4 = .Axes(xlValue).Left

End With

ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, Format:=xlBitmap 'Picture
Selection.ShapeRange.Top = thisObjectTop
Selection.ShapeRange.Left = thisObjectLeft
thisSheetChartCount = thisSheetChartCount - 1

End Sub

Best regards

Deepak Agarwal

02-22-2009, 06:11 PM
Why not post a workbook so we can see what your code does do, and test any solutions?

02-22-2009, 06:23 PM
Sorry, Mate

Here it is! I thought maybe it might not be relevant, in this instance!

Best regards

Deepak Agarwal

Kenneth Hobs
02-22-2009, 07:56 PM
The macro fails before the problem area. If no one replies within 16 hours, please workup a very short example with one chart. It will make the issue easier to troubleshoot. The easier you make it for us, the faster we can isolate the problem and help you.

02-22-2009, 09:17 PM
Dear Kenneth

I am attaching a new spreadsheet now! I am very surprised that the one that was attached didn't work. Honestly, I didn't try it before posting since it was just a copy of the parent document but when I tried it myself, you were right, it didn't work for me either. For that I apologise.

This version I have checked myself and it works. Hopefully, you should not have any problems.

Best regards


Andy Pope
02-23-2009, 02:55 AM
To add a border try this revision.

With ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 0, 0, 100, 20)
With .TextFrame
.Characters.Text = "Data Valid to FY 2"
.AutoSize = True: .AutoMargins = False
.MarginBottom = 0: .MarginLeft = 0
.MarginRight = 0: .MarginTop = 0
.HorizontalAlignment = xlHAlignCenter
.VerticalAlignment = xlVAlignCenter
With .Characters.Font
.Name = "Times New Roman": .Size = 10
.FontStyle = "Bold": .ColorIndex = 3
End With
End With
' border
With .Line
.Style = msoLineSingle
.Transparency = 0#
.Visible = msoTrue
End With
End With

Kenneth Hobs
02-23-2009, 12:34 PM
I was going to post similar to Andy's code so let us know if that works out.

02-23-2009, 01:47 PM
All of you Legends

Thanks a zillion for getting me through this - there was no way that I would have been able to do it without your help!

Might be a small thing for you all but this is going to have such a huge repurcussion to some of the automated regular reporting with graphs (lots of them since the target clientele for most of these high level reports are more of picture people than text people) that I do.

Greatly appreciated.

Best regards

Deepak Agarwal