Passing variables to a PivotChart?

  • Thread starter Thread starter steveski
  • Start date Start date
S

steveski

My VBA code asks the user for a date range (Start Date and End Date),
and then collects data from one sheet based on the date range and
populates a second sheet with the data. Then I have a PivotChart based
on the second sheet. Is it possible to have the date range displayed on
the PivotChart?

Thanks.
 
Here's one way: this will add a TextBox to your chart (as if you had added
one using the Drawing toolbar) and fill it with 2 variables.
-----------------------------------------
Dim tbo As Shape
Dim dtmStart As Date
Dim dtmEnd As Date

dtmStart = #1/1/2004#
dtmEnd = #3/31/2004#

Set tbo = ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal,
200, 30, 140, 14)
tbo.Select
Selection.Characters.Text = dtmStart & " to " & dtmEnd
-----------------------------------------
Of course, you would need to fiddle with it to get the Text box placed &
formatted to your satisfaction.

This seems to be one of those cases where the use of Select is necessary.
Maybe there is a work-around but I found this solution once upon a deadline,
and i saw no real advantage to pursuing it further at the time (or since).
:-)

Hope this helps,
 
Back
Top