1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
| Sub DynamicChart()
' Adjusts the embedded chart on Sheet3.
Dim objChart As Chart, objChObject As ChartObject
Dim objSelection As Range, objSrcData As Range, objCategories As Range
Dim r As Long, c As Long
' Activate the sheet that contains the chart.
ActiveWorkbook.Sheets(6).Activate
' Access the chart from the ChartObject collection
' of the active sheet.
'Set objChart = ActiveSheet.ChartObjects(0).Chart
'Set objChart = ThisWorkbook.Sheets(6).ChartObjects(0).Chart
ThisWorkbook.Sheets(6).ChartObjects(0).Chart
Set objSelection = Application.InputBox(Prompt:="Select entire rows or columns to chart", _
Default:=Selection.Address, _
Type:=8)
' Determine whether the user selected more rows or columns,
' and then use either the first row or first column
' as the range for categories.
r = objSelection.Rows.Count
c = objSelection.Columns.Count
If r > c Then
Set objCategories = Range(Cells(1, 1), Cells(r, 1))
Else
Set objCategories = Range(Cells(1, 1), Cells(1, c))
End If
Set objSrcData = Union(objCategories, objSelection)
objChart.SetSourceData objSrcData
End Sub |
Partager