PDA

View Full Version : [SOLVED] Aligning a chart textbox with a variable datapoint



qitjch
01-26-2016, 01:54 PM
I've got a line chart that gets updated daily and each day, I manually move the textbox to the last point in the series and change the value as well. I have written some VBA code with the goal of automating these actions. The macro is functioning properly except for the fact that it is not positioning the text box at the correct data point.

The macro should: Delete the previous days PPV textbox. Then, with the last point in the series, create a new textbox named PPV and align it just below the data point. Lastly, set the value of this textbox to I35 of another tab, with accounting formatting and no decimals.


The two attached images highlight what is happening when I run this macro. I am able to pull the correct data point# from the other worksheet as well as create the new chart textbox and assign it a value; however, it is placing the textbox in the wrong location. The red arrows illustrate where I would like the textbox to be moved to.

1529315294

Sub UpdateChart()


'This sub updates the chart text box with current values and aligns it with yesterday's data point


'declare variables
Dim ws As Worksheet
Dim i As Long
Dim y As Long
Dim d As Date
Dim oShape As shape


'set ws and todays date
Set ws = Worksheets("PPV Recap by Date")
d = Date


'deletes previous day's textbox
For Each oShape In Worksheets(1).ChartObjects(1).Chart.Shapes
If oShape.Name = "PPV" Then
oShape.Delete
Exit For
End If
Next oShape



Sheets("PPV Recap by Date").Select


'find cell in column B that contains today's date and offset to find plot point# from column A
For i = 5 To ws.Cells(Rows.Count, "B").End(xlUp).Row
If Cells(i, "B").Value = d Then
ws.Cells(i, "B").Select
ActiveCell.Offset(-1, -1).Select
End If
Next

'set y = to plot point#
y = ActiveCell.Value

'set chart object
Dim tmpCHR As ChartObject

Set tmpCHR = Sheet1.ChartObjects(1) 'put index of your chartobject here


'for first series, for point 2nd here
'(change accordingly to what you need)
With tmpCHR.Chart.SeriesCollection(1).Points(y)

Set shp = Sheet1.ChartObjects(1).Chart.Shapes.AddTextbox(msoTextOrientationHorizontal , _
.Left + tmpCHR.Left, _
.Top + tmpCHR.Top, 80, 15)

'set value of textbox
shp.TextFrame2.TextRange.text = Format(Worksheets("PPV Recap by Date").Range("I31").Value, "$#,##0;($#,##0)")
End With

'names and formats Textbox
shp.Name = "PPV"
shp.line.Visible = msoFalse
shp.Fill.Visible = msoFalse

End Sub


If you need any additional information or have any questions, let me know!

p45cal
01-27-2016, 05:40 AM
try changing
Set shp = Sheet1.ChartObjects(1).Chart.Shapes.AddTextbox(msoTextOrientationHorizontal , .Left + tmpCHR.Left, .Top + tmpCHR.Top, 80, 15)
to:
Set shp = Sheet1.ChartObjects(1).Chart.Shapes.AddTextbox(msoTextOrientationHorizontal , .Left, .Top , 80, 15)
which will get you close; now you have to tweak a little.

As an aside, why delete and recreate, why not move and update text?
Something along the lines of:
Sub UpdateChart2()
'This sub updates the chart text box with current values and aligns it with yesterday's data point
Dim i As Long, y As Long, tmpCHR As ChartObject
'set chart object:
Set tmpCHR = Sheet1.ChartObjects(1) 'put index of your chartobject here
Set shp = tmpCHR.Chart.Shapes("PPV")

With Worksheets("PPV Recap by Date")
'find cell in column B that contains today's date and offset to find plot point# from column A
'and set y = to plot point#
For i = 5 To .Cells(.Rows.Count, "B").End(xlUp).Row
If .Cells(i, "B").Value = Date Then y = .Cells(i - 1, "A").Value
Next
'for first series, for point 2nd here
'(change accordingly to what you need)
With tmpCHR.Chart.SeriesCollection(1).Points(y)
'place TextBox:
shp.Top = .Top
shp.Left = .Left
End With
'set value of textbox:
shp.TextFrame2.TextRange.Text = Format(.Range("I31").Value, "$#,##0;($#,##0)")
End With
End Sub
(untested)

qitjch
01-28-2016, 08:23 AM
try changing

With tmpCHR.Chart.SeriesCollection(1).Points(y)
'place TextBox:
shp.Top = .Top
shp.Left = .Left
End With
'set value of textbox:

[/CODE](untested)

I didn't realize I could simply move the existing text box to the last point with nearly the same syntax as creating a new one. I'll definitely go with that route as it is much cleaner. Thanks!