I need help with a VBA that can show specific charts upon conditions

[Ayadi]

Hi,

I am doing a project with excel and have been watching a lot of videos and reading on the web to be able to do what I am planning to do but have not found a good solution yet.

Basically I am creating an interactive dashboard where I am allowing people to choose from the first section one option with a radio button, depending on that section they have a drop-down menu created with data validation, and then depending on that selection, one more data validation drop-down. In total I have around 20 possible combinations.

My aim is to show a different graph (completely different from different data and tables, so dynamic graphs are not an option) depending on the selections.

I tried doing it without a macro by inserting an object/image and using formulas such as indirect, etc. and it worked but the quality of the image that gets imported is terrible and it also takes absolute ages to refresh.

My request and question is how I can make it work with a macro and make it run permanently so I don't have to hit "run" each time I make a selection?

Eg. If cell C3 = 1 and cell D4 = "Revenue" and cell F4 = "Year", show graph1... If C3 = 2 and ........ show graph2, etc

Someone told me I could cheat by putting all graphs on top of each other where I want to show them, and create a macro to bring to front the chart that I want to show. However, I still don't know how to make it work, especially having the first option as a radio button.

If there is another way of doing it I would be very grateful if you could enlighten me!

Also, where would I have to have my graphs? Same sheet or a different one?

Thanks ever so much for your help!!

 

[tlowry]

I think we generated the charts differently mine are shapes yours are not. So I changed all the references to ChartObjects.

Replace all the code with:

'-----------------------------------------
'-----------------------------------------
' Events...
'-----------------------------------------
Private Sub ComboBox1_Change()
    DisplayChart
End Sub
Private Sub ComboBox2_Change()
    DisplayChart
End Sub
Private Sub OptionButton1_Click()
    If Not OptionButton1.Value = True Then Exit Sub
    Range("U3") = 1
    SetDefaultCBs True
End Sub
Private Sub OptionButton2_Click()
    If Not OptionButton2.Value = True Then Exit Sub
    Range("U3") = 2
    SetDefaultCBs True
    ComboBox2.ListIndex = 0
End Sub
Private Sub OptionButton3_Click()
    If Not OptionButton3.Value = True Then Exit Sub
    Range("U3") = 3
    SetDefaultCBs True
    ComboBox2.ListIndex = 0
End Sub
Private Sub OptionButton4_Click()
    If Not OptionButton4.Value = True Then Exit Sub
    Range("U3") = 4
    SetDefaultCBs True
    ComboBox2.ListIndex = 0
End Sub
' end of event capture
'-----------------------------------------
'-----------------------------------------
Sub SetDefaultCBs(bReset) ' Sets up Combos based on options
    If OptionButton1 = True Then
        ComboBox2.List = Array("Region", "Year")
    Else
        ComboBox2.List = Array("Year")
    End If
    If bReset = True Then
        ComboBox2.ListIndex = -1
        ComboBox1.ListIndex = -1
    End If
End Sub
'-----------------------------------------
Sub SetupCB() ' cleans up after/before use
    
    
    ClearCharts         'make all charts not visiblw
    ComboBox1.List = Array("Total Revenue", "Revenue Share", "Growth YoY")
    ComboBox2.List = Array("")
    ComboBox1.LinkedCell = "C10"    ' Set linked cells of combos
    ComboBox2.LinkedCell = "F10"
    ComboBox1.ListIndex = -1        ' "Deselect" combos
    ComboBox2.ListIndex = -1
    
    OptionButton1.Value = False     ' turn off all obs
    OptionButton2.Value = False
    OptionButton3.Value = False
    OptionButton4.Value = False
    Dim ct
    
    Dim rng: Set rng = Range("B12:J29")

    For Each ct In Array("ChRevReg", "ChRevShReg1", "ChRevShReg2", "ChRevShReg3", _
                    "ChRevShReg4", "ChRevShReg5", "ChGroReg", "ChContReg", "ChRevY", _
                    "ChRevShY", "ChGroY", "ChContY", "MktRevY", "MktRevShY", "MktGroY", _
                    "MktContY", "ProgRevY", "ProgRevShY", "ProgGroY", "ProgContY", "ProdRevY", _
                    "ProdRevShY", "ProdGroY", "ProdContY")
            ChartObjects(ct).Left = rng.Left
            ChartObjects(ct).Top = rng.Top
            ChartObjects(ct).Width = rng.Width
            ChartObjects(ct).Height = rng.Height
    Next ct
    
End Sub
'-----------------------------------------
Sub DisplayChart()  ' Displays chart(s)
    ClearCharts
    On Local Error Resume Next
    Dim ct
    ct = GetChartName(Range("U3") & "|" & Range(ComboBox1.LinkedCell) _
                & "|" & Range(ComboBox2.LinkedCell))
    If ct = "ChRevShReg" Then
        Dim nm
        For Each nm In Array("ChRevShReg1", "ChRevShReg2", "ChRevShReg3", "ChRevShReg4")
            ChartObjects(nm).Visible = True
        Next
    Else
        ChartObjects(ct).Visible = True
    End If
End Sub

'-----------------------------------------
Sub ClearCharts()   ' Makes all charts not visible
    Dim ct
    For Each ct In Array("ChRevReg", "ChRevShReg1", "ChRevShReg2", "ChRevShReg3", _
                    "ChRevShReg4", "ChRevShReg5", "ChGroReg", "ChContReg", "ChRevY", _
                    "ChRevShY", "ChGroY", "ChContY", "MktRevY", "MktRevShY", "MktGroY", _
                    "MktContY", "ProgRevY", "ProgRevShY", "ProgGroY", "ProgContY", "ProdRevY", _
                    "ProdRevShY", "ProdGroY", "ProdContY")
        ChartObjects(ct).Visible = False
    Next ct
End Sub

Function GetChartName(sInputString)  ' Gets chart name from selections
    If ComboBox1.ListIndex = -1 Then Exit Function
    If ComboBox2.ListIndex = -1 Then Exit Function
    Dim nameArr, s1, s2, s3
    nameArr = Split(sInputString, "|")
    If UBound(nameArr) <> 2 Then Exit Function
    Dim rb
    rb = Array("Ch", "Mkt", "Prog", "Prod")
    s1 = rb(nameArr(0) - 1)
    Select Case nameArr(1)
        Case "Total Revenue": s2 = "Rev"
        Case "Revenue Share": s2 = "RevSh"
        Case "Growth YoY": s2 = "Gro"
        Case Else: Exit Function
    End Select
    Select Case nameArr(2)
        Case "Region": s3 = "Reg"
        Case "Year": s3 = "Y"
        Case Else: Exit Function
    End Select
    GetChartName = s1 & s2 & s3
End Function

And let me know.