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]

About the Break mode thing; see:

"Can't Execute Code in Break Mode" When Running Macro

 

[Ayadi]

Ok, so I started reading and found out that I had to click the stop button at the top when in development mode to stop the break, so I did. Nothing was shown in red when I clicked shift+Ctrl+F9 before or after doing that though.

So I went and ran it again and it says (in the exact same section as in my previous screenshot): Compile error: Sub or function not defined.


On a different note, I just saw that in my first attempt (the long one previous to today's code) I forgot the end ifs :-/ I am trying it now and it seems to work with data validation with this code for example:

Sub makeVisible(sName)
Dim chts, ct
With Sheets(1)
chts = Array("ChRevY", "ChRevShY1", "ChRevShY2", "ChRevShY3", "ChRevShY4", "ChRevShY5", "ChGroY", "ChContY")
For Each ct In chts
.Shapes(ct).Visible = False
Next ct
If sName <> "" Then .Shapes(sName).Visible = True
End With
End Sub
Sub ShowChart()
If Range("U3") = 1 And Range("C10") = "Total Revenue" And Range("F10") = "Year" Then
makeVisible "ChRevY"
Exit Sub
End If
If Range("U3") = 1 And Range("C10") = "Revenue Share" And Range("F10") = "Year" Then
makeVisible "ChRevShY1"
Exit Sub
End If
If Range("U3") = 1 And Range("C10") = "Growth YoY" And Range("F10") = "Year" Then
makeVisible "ChGroY"
Exit Sub
End If
If Range("U3") = 1 And Range("C10") = "Contribution to Growth" And Range("F10") = "Year" Then
makeVisible "ChContY"
Exit Sub
End If
End Sub


Still the proper drop down menus would be so much better to have but at least this is working if the other code doesn't

Also, with this version, I cannot ask excel to show more than one graph at the same time can I? Eg:

If Range("U3") = 1 And Range("C10") = "Revenue Share" And Range("F10") = "Year" Then
makeVisible "ChRevShY1", "ChRevShY2", "ChRevShY3", "ChRevShY4", "ChRevShY5"
Exit Sub
End If



Thank you!!

 

[tlowry]

Compile error: Sub or function not defined.

What was being highlighted in the code?

 

[tlowry]

Also, with this version, I cannot ask excel to show more than one graph at the same time can I? Eg:

If Range("U3") = 1 And Range("C10") = "Revenue Share" And Range("F10") = "Year" Then
makeVisible "ChRevShY1", "ChRevShY2", "ChRevShY3", "ChRevShY4", "ChRevShY5"
Exit Sub
End If

It was designed to take a single chart. That was before it knew there would be this anomaly.

 

[Ayadi]

I think there is something with the "shapes" parameter because there's where the error lies in the new code you gave me, but also when I tried with the code that works, adding this last resizing parameter, it also gives me an error and highlights the word "shapes". Also says Sub or Function not defined. Trying to find answers on the web but not being successful yet...

 

[Ayadi]

What was being highlighted in the code?

The first line with Sub.... and then the word Shapes. It doesn't seem to like it in either version.

 

[tlowry]

Can you copy and paste a few lines of code (above and below) where it errors out?

Also, please use the # button to delineate code.

For extra credit here's some code to allow array/not array as an argument.

Sub SetVisible(sName, Optional TorF As Boolean = True)
    Dim ct
    For Each ct In IIf(TypeName(sName) = "String", Array(sName), sName)
        If ct <> "" Then Sheets(1).Shapes(ct).Visible = TorF
    Next ct
End Sub
Sub TestSetVisible()
    SetVisible Array("Chart 1", "Chart 3", "Chart 4")
    SetVisible "Chart 3", False
End Sub

 

[Ayadi]

Hi tlowry,

Sorry for the delay, I had to travel. I'm back trying to make this work and the lines of code do not make the error go away. It keeps highlighting the "shapes" function :-/

I am getting a bit lost with all the code and do not want to be misplacing it so maybe I'll try one version first and make sure it works before trying a more complex one? Or should we keep trying? I'm sorry for all the trouble!

 

[tlowry]

This is not trouble.

I did some work on the application and I think we should start with the current one (below)

Instructions (Please follow them exactly)

• In a new workbook, copy and paste all the charts into sheet1

Charts must be named:
"ChRevReg", "ChRevShReg1", "ChRevShReg2", "ChRevShReg3", _
"ChRevShReg4", "ChRevShReg5", "ChGroReg", "ChContReg", "ChRevY", _
"ChRevShY", "ChGroY", "ChContY", "MktRevY", "MktRevShY", "MktGroY", _
"MktContY", "ProgRevY", "ProgRevShY", "ProgGroY", "ProgContY", "ProdRevY", _
"ProdRevShY", "ProdGroY", "ProdContY"

• Put 4 option buttons on sheet1 (Names OptionButton1 to OptionButton4)
• Put 2 ComboBoxes on sheet1 (ComboBox1 and ComboBox2)
• Put the code in the sheets module (Please don't make any changes until it works)
• Run "SetupCB"

Select an OptionButton and two ComboBox selections

A chart should be displayed...

Test (No changes, please)

If it fails (when it fails?), I need:

• What was being done (User selections)
• The error text displayed
• The full line(s) of code where it failed

'-----------------------------------------
'-----------------------------------------
' 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")
    If ct <> "ChRevShReg" Then
        Shapes(ct).DrawingObject.Left = rng.Left
        Shapes(ct).DrawingObject.Top = rng.Top
        Shapes(ct).DrawingObject.Width = rng.Width
        Shapes(ct).DrawingObject.Height = rng.Height
    Else
        'resize four charts ...
    End If
    
    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")
            Shapes(nm).DrawingObject.Visible = True
        Next
    Else
        Shapes(ct).DrawingObject.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")
        Shapes(ct).DrawingObject.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

 

[Ayadi]

Hi tlowry! Honestly thank you so much!

I have done as you described, all charts on sheet1, added the radio buttons with their respective names, combo boxes as well.

I inserted a module (module1) and pasted your code as is. There are no other modules in this workbook.

I saved.

Pressed F8, and ran "Setup CB"

Gave me the error:

Compile Error:
Sub or Function not defined.

It takes me to 2 places:

1. Under Sub SetupCB() ' cleans up after/before use
It highlights the word shapes right after If ct <> "ChRevShReg" Then

2. Under Sub DisplayChart() ' Displays chart(s)
It highlights the word shapes right after For Each nm In Array("ChRevShReg1", "ChRevShReg2", "ChRevShReg3", "ChRevShReg4")

It immediately gives me these errors when running the code, so nothing is appearing in the combo boxes as it seems it doesn't want to run.

Thanks!