Trying to change the source field on a chart by using a acro button
the user can set the amount of data which i plotted on the graph by entering a number into a spreadsheet field
This allows the user to view data in more detail if required using the function to zoo in on a particular section
The first version seems to work setting the nuber to the data set required
'Roll name source set
ActiveChart.SeriesCollection(1).Name = "='Sheet1'!$D$89"
'Roll data source set
ActiveChart.SeriesCollection(1).Values = "='Sheet1'!$D$80:$D$3000"
however when I try to change this to allow the user to vary the data being plotted excel complains of errors
'Pitch
ActiveChart.SeriesCollection(2).Name = "='Sheet1'!$E$79"
ActiveChart.SeriesCollection(2).Values = "='Sheet1'!$E$80:$E$(80+[Q123])"
'Yaw
ActiveChart.SeriesCollection(3).Name = "='Sheet1'!$F$79"
ActiveChart.SeriesCollection(3).Values = "='Sheet1'!$F$80:$F$nor"
Whole code is displayed below
Sub Macro1()
'
' Macro1 Macro
Dim nor As Integer
nor = [q123]
'Activates Roll, Pitch, Yaw graph for variable Change
ActiveSheet.ChartObjects("Chart 4").Activate
'Roll name source set
ActiveChart.SeriesCollection(1).Name = "='Sheet1'!$D$89"
'Roll data source set
ActiveChart.SeriesCollection(1).Values = "='Sheet1'!$D$80:$D$3000"
'Pitch
ActiveChart.SeriesCollection(2).Name = "='Sheet1'!$E$79"
ActiveChart.SeriesCollection(2).Values = "='Sheet1'!$E$80:$E$(80+[Q123])"
'Yaw
ActiveChart.SeriesCollection(3).Name = "='Sheet1'!$F$79"
ActiveChart.SeriesCollection(3).Values = "='Sheet1'!$F$80:$F$nor"
End Sub
Bookmarks