Creating charts based on user preference using user form excel vba

[KimLeng]

Hi,

I would like to create charts using user form based on user preference, I manage to get my desired user form from the 1st part of my code, but can't seem to get my combobox value in 2nd part to change the chart type. Any help would be appreciated, thanks in advanced!

Private Sub UserForm_Activate()

Dim rng As Range
Dim ctl As Control
Set rng = Selection

For i = 1 To rng.Columns.Count - 1
    Set ctl = Me.Controls.Add("forms.label.1")
        With ctl
            .Name = "Series" & i + 1
            .Caption = rng.Cells(1, 1 + i)
            .Top = 15 + (i * 35)
            .Left = 24
            .TextAlign = 2
            .Width = 126
            .Height = 12
        End With
Next i

For i = 1 To rng.Columns.Count - 1
    Set ctl = Me.Controls.Add("forms.combobox.1")
         With ctl
            .Name = "ComboBox" & i + 1
            .Top = 15 + (i * 35)
            .Left = 216
            .TextAlign = 2
            .Width = 174
            .Height = 15
            .AddItem "Clustered Column"
            .AddItem "Stacked Column"
            .AddItem "Line"
            .AddItem "Stacked Line"
        End With
Next i
End Sub

Private Sub CommandButton1_Click()

Dim rng As Range
Set rng = Selection
Dim cht As Chart
Set cht = ActiveChart

    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select

   With cht
        If ComboBox2 = "Clustered Column" Then
            cht.FullSeriesCollection(1).ChartType = xlColumnClustered
        ElseIf ComboBox2 = "Stacked Column" Then
            cht.FullSeriesCollection(1).ChartType = xlColumnStacked
        ElseIf ComboBox2 = "Line" Then
            cht.FullSeriesCollection(1).ChartType = xlLine
        ElseIf ComboBox2 = "Stacked Line" Then
            cht.FullSeriesCollection(1).ChartType = xlLineStacked
        End If
    End With
    
    With cht
        If ComboBox3 = "Clustered Column" Then
            cht.FullSeriesCollection(2).ChartType = xlColumnClustered
        ElseIf ComboBox3 = "Stacked Column" Then
            cht.FullSeriesCollection(2).ChartType = xlColumnStacked
        ElseIf ComboBox3 = "Line" Then
            cht.FullSeriesCollection(2).ChartType = xlLine
        ElseIf ComboBox3 = "Stacked Line" Then
            cht.FullSeriesCollection(2).ChartType = xlLineStacked
        End If
    End With
    
    With cht
        If ComboBox4 = "Clustered Column" Then
            cht.FullSeriesCollection(3).ChartType = xlColumnClustered
        ElseIf ComboBox4 = "Stacked Column" Then
            cht.FullSeriesCollection(3).ChartType = xlColumnStacked
        ElseIf ComboBox4 = "Line" Then
            cht.FullSeriesCollection(3).ChartType = xlLine
        ElseIf ComboBox4 = "Stacked Line" Then
            cht.FullSeriesCollection(3).ChartType = xlLineStacked
        End If
    End With
            
Unload Me

End Sub