Creating a graph each time I change a drop down list value

Question

I have tried writing a code where each time I change values of like 7 drop down lists, it would create a new line chart, but when I store it into a Command button and activate it, it says "Wrong Number of arguments or invalid property assignment". I am quite new to VBA, so sorry for any inconvenience caused.

Sub CreateDynamicChart()
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim chartRange As Range
    Dim dropDown As Shape
    Dim dropDownRange As Range
    
    ' Set the worksheet object
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace with your sheet name
    
    ' Set the range of the master table for the chart
    Set chartRange = ws.Range("A1:H10") ' Replace with your master table range
    
    ' Create a new chart object
    Set chartObj = ws.ChartObjects.Add(Left:=100, Top:=100, Width:=400, Height:=300)
    
    ' Set the chart source data
    chartObj.Chart.SetSourceData Source:=chartRange
    
    ' Set the drop-down lists and their associated ranges
    Set dropDown = ws.Shapes("DropDown1") ' Replace with your drop-down list shape names
    
    Set dropDownRange = ws.Range("A1:A10") ' Replace with the range for the first drop-down list
    
    ' Add a change event for each drop-down list
    With dropDown.OLEFormat.Object
        With .Object
            .OnAction = "UpdateChart"
            .LinkedCell = dropDownRange.Cells(1).Address
        End With
    End With
    
    ' Repeat the above block for each of the other six drop-down lists, modifying the drop-down shape and range
    
    ' Call the UpdateChart procedure to create the initial chart
    UpdateChart
End Sub

Sub UpdateChart()
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim chartRange As Range
    
    ' Set the worksheet object
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace with your sheet name
    
    ' Set the range of the master table for the chart
    Set chartRange = ws.Range("A1:H10") ' Replace with your master table range
    
    ' Clear existing chart
    For Each chartObj In ws.ChartObjects
        chartObj.Delete
    Next chartObj
    
    ' Create a new chart object
    Set chartObj = ws.ChartObjects.Add(Left:=100, Top:=100, Width:=400, Height:=300)
    
    ' Set the chart source data based on the selected values from the drop-down lists
    ' Modify the below lines to reference the correct drop-down lists and ranges
    
    Dim selectedValue1 As String
    Dim selectedValue2 As String
    ' ...
    Dim selectedValue7 As String
    
    selectedValue1 = ws.Range("A1").Value
    selectedValue2 = ws.Range("A2").Value
    ' ...
    selectedValue7 = ws.Range("A7").Value
    
    ' Create the dynamic range for the chart based on the selected values
    Dim dynamicRange As Range
    Set dynamicRange = chartRange.Resize(, 1).Offset(, 1).Find(selectedValue1).Offset(, 1)
    
    For i = 2 To 7
        Set dynamicRange = Intersect(dynamicRange.EntireRow, chartRange.Columns(i)).Find(selectedValue1).Offset(, 1)
    Next i
    
    ' Set the chart source data
    chartObj.Chart.SetSourceData Source:=dynamicRange
    
    ' Format the chart as desired
    
End Sub

Tried Using this code, but just comes up with the error.

Use the F8 key to step through the code line-by-line; which line does the error appear on? Which line/s aren't doing what you expect? — Spencer Barnes, Jul 10 '23 at 06:27
Have you investigated Pivot Charts? All this is built in with no code required. — Nick.Mc, Jul 10 '23 at 06:33
Perhaps the data ranges for the charts can be controlled by the drop downs; see https://stackoverflow.com/a/69077964/4961700 — Solar Mike, Jul 10 '23 at 08:34

Creating a graph each time I change a drop down list value

0 Answers0