Drop down list value to define ranges in macro

[lukasz_rz]

Hello Guys.

This is where I'm with my excel file.
I have 20 different data tables with different number of rows. I have one single chart that presents the numbers from a single data table.
I would like to run a macro based on the cell value change (drop down list). Depending on the choice, I would need different range to be used in a macro. Macro is made to assign the chart values to one of the data tables.

A part of the code is:

wks.ChartObjects("IN1").Activate
Set cht = ActiveChart

cht.SetSourceData Source:=rng_v, PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = Range(wks.Cells(4, 5), wks.Cells(4, 10))

  Dim iSrs As Long
  For iSrs = 1 To cht.SeriesCollection.Count
    If iSrs > rng.Cells.Count Then Exit For
    cht.SeriesCollection(iSrs).Name = _
        "=" & rng.Cells(iSrs).Address(, , , True)
  Next

rng_v and rng are the ranges I would like to set up based on the choice from my drop down list. For example, when I choose "Option1" from the list, I want macro to choose rng = A1:A5 and rng_v = B1:B5. when I choose "Option2", then rng = A12:A15, rng_v = B12:B15, etc. That is how I would be able to update my chart based on the value in one cell. Just one click to choose different data ranges reflected on the chart.



Thanks in advance.

 

[sijpie]

    Dim rRngY As Range, rRngX As Range  'the ranges to use in your graph. Always use one or two capital letters in your variable declaration
    
    Select Case Range("D1") ' Assuming the cell D1 is the one changed by the choice of the user. Modify to suit <<<<<<<<<<<
        Case "Option1"
            Set rRngY = Range("A1:A5")
            Set rRngX = Range("B1:B5")
        Case "Option2"
            Set rRngY = Range("A12:A15")
            Set rRngX = Range("B12:B15")
        Case "Option3"
             Set rRngY = Range("A21:A25")
            Set rRngX = Range("B21:B25")
        Case "Option4"
            Set rRngY = Range("A31:A35")
            Set rRngX = Range("B31:B35")
        Case Else
            MsgBox "Invalid entry in cell D1"
    End Select
    
    'Continue with your graph macro here, using the range names declared above

I always use some capital letters in the declaration of my variables. I then type them without capitals. If VBA editor does not change the letters to capitals, it means i made a typo in the name.

The select case construction is the same as a long list of If and Elsif , but much more efficient both in typing and in execution.

See my guide to better programming, link in my tag line