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!
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!