Aus langer VBA Routine Schleife basteln ? Hilfe
20.08.2018 08:35:08
David
Beitragvon duppi12 » 20. Aug 2018, 05:51
Hallo Excelfreunde,
ich versuche zuzeit aus einer ziemlich langen Routine eine Schleife zu basteln, aber leider verzweifel ich mittlerweile weil ich einfach nicht mehr weiter kommen.
Ich hoffe ihr könnt mir helfen? :(
Ich habe ein Userform, auf dieser befinden sich 4 Textfelder in denen Jahreszahlen eingetragen werden.
Es wird überprüft ob es die dazugehörigen Tabellenblätter gibt.
Anschließend kann man auf einer Multipage, Checkboxen wählen (80Stück).
Nun sollen bei Klick, je nach Wahl mehrere Diagramme erstellt werden, mit Daten aus unterschiedlichen Tabellen,
BITTE Helft mir. Ich brauche bei 80 Stück einfach eine Schleife . :( :(
If CheckBox1.Value = True And CheckBox21.Value = True Then
Sheets(TextBox1.Text).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range("F6:Q6")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = TextBox1.Text
ActiveChart.SeriesCollection(2).Name = TextBox2.Text
ActiveChart.SeriesCollection(2).Values = Sheets(TextBox2.Text).Range("F6:Q6")
ActiveChart.SetElement (msoElementChartTitleAboveChart)
ActiveChart.ChartTitle.Text = CheckBox1.Caption
ActiveChart.SeriesCollection(1).Interior.Color = RGB(100, 149, 297)
ActiveChart.ChartTitle.Font.Size = 12
ActiveChart.Parent.Left = 150
ActiveChart.Parent.Top = 350
ActiveSheet.Select
End If
If CheckBox1.Value = True And CheckBox21.Value = True And CheckBox41 = True Then
Sheets(TextBox1.Text).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range("F6:Q6")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = TextBox1.Text
ActiveChart.SeriesCollection(2).Name = TextBox2.Text
ActiveChart.SeriesCollection(2).Values = Sheets(TextBox2.Text).Range("F6:Q6")
ActiveChart.SeriesCollection(3).Name = TextBox3.Text
ActiveChart.SeriesCollection(3).Values = Sheets(TextBox3.Text).Range("F6:Q6")
ActiveChart.SetElement (msoElementChartTitleAboveChart)
ActiveChart.ChartTitle.Text = CheckBox1.Caption
ActiveChart.SeriesCollection(1).Interior.Color = RGB(100, 149, 297)
ActiveChart.ChartTitle.Font.Size = 12
ActiveChart.Parent.Left = 150
ActiveChart.Parent.Top = 350
ActiveSheet.Select
End If
If CheckBox1.Value = True And CheckBox21.Value = True And CheckBox41 = True And CheckBox61. _
Value = True Then
Sheets(TextBox1.Text).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range("F6:Q6")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = TextBox1.Text
ActiveChart.SeriesCollection(2).Name = TextBox2.Text
ActiveChart.SeriesCollection(2).Values = Sheets(TextBox2.Text).Range("F6:Q6")
ActiveChart.SeriesCollection(3).Name = TextBox3.Text
ActiveChart.SeriesCollection(3).Values = Sheets(TextBox3.Text).Range("F6:Q6")
ActiveChart.SeriesCollection(4).Name = TextBox4.Text
ActiveChart.SeriesCollection(4).Values = Sheets(TextBox4.Text).Range("F6:Q6")
ActiveChart.SetElement (msoElementChartTitleAboveChart)
ActiveChart.ChartTitle.Text = CheckBox1.Caption
ActiveChart.SeriesCollection(1).Interior.Color = RGB(100, 149, 297)
ActiveChart.ChartTitle.Font.Size = 12
ActiveChart.Parent.Left = 150
ActiveChart.Parent.Top = 350
ActiveSheet.Select
End If
If CheckBox2.Value = True And CheckBox22.Value = True Then
Sheets(TextBox1.Text).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range("F7:Q7")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = TextBox1.Text
ActiveChart.SeriesCollection(2).Name = TextBox2.Text
ActiveChart.SeriesCollection(2).Values = Sheets(TextBox2.Text).Range("F7:Q7")
ActiveChart.SetElement (msoElementChartTitleAboveChart)
ActiveChart.ChartTitle.Text = CheckBox2.Caption
ActiveChart.SeriesCollection(1).Interior.Color = RGB(100, 149, 297)
ActiveChart.ChartTitle.Font.Size = 12
ActiveChart.Parent.Left = 150
ActiveChart.Parent.Top = 380
ActiveSheet.Select
End If
If CheckBox2.Value = True And CheckBox22.Value = True And CheckBox42 = True Then
Sheets(TextBox1.Text).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range("F7:Q7")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = TextBox1.Text
ActiveChart.SeriesCollection(2).Name = TextBox2.Text
ActiveChart.SeriesCollection(2).Values = Sheets(TextBox2.Text).Range("F7:Q7")
ActiveChart.SeriesCollection(3).Name = TextBox3.Text
ActiveChart.SeriesCollection(3).Values = Sheets(TextBox3.Text).Range("F7:Q7")
ActiveChart.SetElement (msoElementChartTitleAboveChart)
ActiveChart.ChartTitle.Text = CheckBox2.Caption
ActiveChart.SeriesCollection(1).Interior.Color = RGB(100, 149, 297)
ActiveChart.ChartTitle.Font.Size = 12
ActiveChart.Parent.Left = 150
ActiveChart.Parent.Top = 380
ActiveSheet.Select
End If
If CheckBox2.Value = True And CheckBox22.Value = True And CheckBox42 = True And CheckBox62. _
Value = True Then
Sheets(TextBox1.Text).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range("F7:Q7")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = TextBox1.Text
ActiveChart.SeriesCollection(2).Name = TextBox2.Text
ActiveChart.SeriesCollection(2).Values = Sheets(TextBox2.Text).Range("F7:Q7")
ActiveChart.SeriesCollection(3).Name = TextBox3.Text
ActiveChart.SeriesCollection(3).Values = Sheets(TextBox3.Text).Range("F7:Q7")
ActiveChart.SeriesCollection(4).Name = TextBox4.Text
ActiveChart.SeriesCollection(4).Values = Sheets(TextBox4.Text).Range("F7:Q7")
ActiveChart.SetElement (msoElementChartTitleAboveChart)
ActiveChart.ChartTitle.Text = CheckBox2.Caption
ActiveChart.SeriesCollection(1).Interior.Color = RGB(100, 149, 297)
ActiveChart.ChartTitle.Font.Size = 12
ActiveChart.Parent.Left = 150
ActiveChart.Parent.Top = 380
ActiveSheet.Select
End If
Das sind die ersten Zeilen .. leider ist diese Routine bei 80 Stück einfach viel zu lang ..
An sich ist das was da steht ja nicht alzu schwierig.. nur leider kriege ich daraus keine Schleife hin. kann mir bitte jemand helfen?
Lg
David