Zweiten Chart zuordnen
16.02.2015 05:16:26
Markus
ich möchte über eine Combobox zwei verschiedene Charts ansprechen, d.h. ein Chart soll ab Zelle E12 auf Sheet15 angezeigt werden und Chart 2 ab Zelle U12 im gleichen Sheet. Die Combobox heisst cboKPIProcessPortfolio. Bisher habe ich folgenden Code (nur für einen Chart) - s.u.! Jemand ne Ahnung wie ich den zweiten Chart zuordnen kann und dann beim auswählen eines Dropdownpunktes beide gemeinsam zeige?
VG
Code Sheet15:
Private Sub cboKPIProcessPortfolio_Change()
Application.ScreenUpdating = False
Call LoadFavChart(Me.cboKPIProcessPortfolio)
Application.ScreenUpdating = True
End Sub
Code zum Laden des 1. Chartes:
Sub LoadFavChart(ByRef cb As combobox)
Dim Port_code As String
Dim cht As ChartObject
Application.ScreenUpdating = False
Sheet15.Activate
Call UnlockWSChart
On Error Resume Next
'Delete old chart
If ActiveSheet.ChartObjects.Count >= 1 Then
ActiveSheet.ChartObjects.Select
ActiveSheet.ChartObjects.Delete
End If
Debug.Print cb.Value
'Add new chart
Port_code = cb.Value
'If a KPI is selected
If Port_code "Please select Portfolio" Then
'Switch to workspace where all KPI diagrams are stored and copy diagram
Sheet12.Select
ActiveSheet.ChartObjects(Port_code).Select
Selection.Copy
'Paste diagram to the chart area of the favorite KPI window
Sheet15.Select
ActiveSheet.Paste Destination:=Range("E12")
'Select and delink the diagram from it's data source
ActiveSheet.ChartObjects.Select
Call DelinkChartFromData
End If
Call LockWSChart
Application.ScreenUpdating = True
End Sub
Code zur Initialisierung der Combobox:
Sub InitializeFavCombobox(ByRef cb As combobox)
Dim Val As Integer
Dim I As Integer
Dim K As Integer
Dim FavIndex As Integer
Dim FavName As String
Application.ScreenUpdating = False
'Store previously selected favorite KPI
FavIndex = 0
FavName = cb.Value
cb.Clear
cb.AddItem "Please select Portfolio"
Val = Sheet12.ChartObjects.Count
'Sort entries alphabetically and add to combobox
For I = 1 To Val
For K = 1 To cb.ListCount - 1
cb.ListIndex = K
If Sheet12.ChartObjects(I).Name cb.AddItem Sheet12.ChartObjects(I).Name, K
GoTo SKIPHERE
End If
Next K
cb.AddItem Sheet12.ChartObjects(I).Name
SKIPHERE:
Next I
'Check on which place the previous favorite KPI is and remember this position
For K = 1 To cb.ListCount - 1
cb.ListIndex = K
If cb.Value = FavName Then
FavIndex = K
End If
Next K
'Set Listindex to previous favorite KPI
cb.ListIndex = FavIndex
Application.ScreenUpdating = True
End Sub
Code des Buttons über welchen die gesamte Initialisierung lauft:
Private Sub cmdShowResultsI_Click()
Application.ScreenUpdating = False
'Load Favorite KPIs in Comboboxes
Call InitializeFavCombobox(Sheet15.cboKPIProcessPortfolio)
'Reset diagram of Favorite KPIs
Sheet15.Select
Call UnlockWSChart
With Sheet15
If .ChartObjects.Count >= 1 Then
.ChartObjects.Select
.ChartObjects.Delete
End If
End With
Call LockWSChart
Call LoadFavChart(Sheet15.cboKPIProcessPortfolio)
Application.ScreenUpdating = True
End Sub