Creating charts based on user preference using user form excel vba

KimLeng

New Member
Joined
Mar 15, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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!

VBA Code:
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

VBA Code:
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top