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]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:DoNotOptimizeForBrowser/> </w:WordDocument> </xml><![endif]--> Welcome to MrExcel!

You have quite a task to start with.

What I suggest is that you develop all the charts in one sheet. Make them all not visible. Then, based on the criteria, make one of them visible.

Let’s start with the very basics on this:

• Start with a new Excel workbook
• Put some data in a sheet and create three charts (Don’t do a lot of work here. We just need three charts in the sheet)
• Note the names of the charts (Right click a chart and select “Chart Window” and the name is displayed as the window title.)
• Go into development mode (Alt F11)
• Create a module (Insert | Module)
• Copy and paste this code:

Sub makeVisible(sName)
    Dim chts, ct
    With Sheets(1)
        chts = Array("[COLOR=#0000ff]Chart 1[/COLOR]", "[COLOR=#0000cd]Chart 2[/COLOR]", "[COLOR=#0000ff]Chart 3[/COLOR]")
        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("P2") = 1 And Range("D4") = "Revenue" And Range("F4") = "Year" Then
        makeVisible "[COLOR=#0000ff]Chart 1[/COLOR]"
        Exit Sub
    End If
    If Range("P2") = 2 And Range("D4") = "Revenue" And Range("F4") = "Year" Then
        makeVisible "[COLOR=#0000ff]Chart 2"[/COLOR]
        Exit Sub
    End If
    If Range("P2") = 2 And Range("D4") = "Revenue" And Range("F4") = "Month" Then
        makeVisible "[COLOR=#0000ff]Chart 3[/COLOR]"
        Exit Sub
    End If
End Sub

• Replace the names (in blue) with your chart names
• Leave development mode (Alt F11)
• Set the conditions for the first chart P2 = 1, D4= Revenue, F4 = Year
• Run ShowChart (alt F8)
• Change the conditions to the second chart (not specified) in your description (If Range("P2") = 2 And Range("D4") = "Revenue" And Range("F4") = "Year"
• Run ShowChart

The point here is to use a method that will show only one chart based on the conditions and determine if you like it. Please don;t modify the code (other than the chart names) until we get this working


Good luck

 

[Ayadi]

Hi Tlowry!

Thank you so so much for this! It works!!! I only have a couple of follow up questions here now if you don't mind:

1. How do I originally hide the charts so that the first selection starts as a blank?

2. How do I do for the macro to be constantly running so that people don't have to constantly click on run macro to see the chart? I.e. so that whenever they change a condition (a drop-down selection, etc) the graph auto refreshes? If that is not possible, is there a way to include a button that says "Refresh" that will perform this action?

Thank you!!!!!!!

 

[tlowry]

1. How do I originally hide the charts so that the first selection starts as a blank?

Put this in the module and run ClearCharts

Sub ClearCharts()
    makeVisible ""
End Sub

2. How do I do for the macro to be constantly running so that people don't have to constantly click on run macro to see the chart? I.e. so that whenever they change a condition (a drop-down selection, etc) the graph auto refreshes? If that is not possible, is there a way to include a button that says "Refresh" that will perform this action?

This is fairly straight forward. We will do this once we get the visible/not visible thing going.

Next phase:

How many charts are we going to use?

We could make life easier (for us) if we named the charts like:

"Revenue2YearChartShow"

Think about it.

 

[Ayadi]

Ok here we go with what I've tried and the logic behind it:

Probabilities


Radio Buttons: 4
1. Channel
2. Market
3. Program
4. Product


According to selection, number appears on hidden cell U3 (selection 1)
1st drop-down in cell C10 (selection 2)
2nd drop-down in cell F10 (selection 3)


Region option in dropdowns only shown at Channel level.


Probabilities with dropdows:


Selection1 - Selection2 - Selection3 - Result (graph to show)
1 - Total Revenue - Region = ChRevReg
1 - Revenue Share - Region = ChRevShReg1, ChRevShReg2, ChRevShReg3, ChRevShReg4, ChRevShReg5 (note: maybe I can group them and show one object instead?)
1 - Growth YoY - Region = ChGroReg
1 - Contribution to Growth - Region = ChContReg
1 - Total Revenue - Year = ChRevY
1 - Revenue Share - Year = ChRevShY
1 - Growth YoY - Year = ChGroY
1 - Contribution to Growth - Year = ChContY
2 - Total Revenue - Year = MktRevY
2 - Revenue Share - Year = MktRevShY
2 - Growth YoY - Year = MktGroY
2 - Contribution to Growth - Year = MktContY
3 - Total Revenue - Year = ProgRevY
3 - Revenue Share - Year = ProgRevShY
3 - Growth YoY - Year = ProgGroY
3 - Contribution to Growth - Year = ProgContY
4 - Total Revenue - Year = ProdRevY
4 - Revenue Share - Year = ProdRevShY
4 - Growth YoY - Year = ProdGroY
4 - Contribution to Growth - Year = ProdContY


All charts:

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


I tried putting the following code but excel gives me an error in the line in red below:


Module1

Sub ClearCharts()
makeVisible ""
End Sub

Sub makeVisible(sName)
Dim chts, ct
With Sheets(1)
chts = 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")
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") = "Region" Then
makeVisible "ChRevReg"
Exit Sub
If Range("U3") = 1 And Range("C10") = "Revenue Share" And Range("F10") = "Region" Then
makeVisible "ChRevShReg1", "ChRevShReg2", "ChRevShReg3", "ChRevShReg4", "ChRevShReg5"
Exit Sub
If Range("U3") = 1 And Range("C10") = "Growth YoY" And Range("F10") = "Region" Then
makeVisible "ChGroReg"
Exit Sub
If Range("U3") = 1 And Range("C10") = "Contribution to Growth" And Range("F10") = "Region" Then
makeVisible "ChContReg"
Exit Sub
If Range("U3") = 1 And Range("C10") = "Total Revenue" And Range("F10") = "Year" Then
makeVisible "ChRevY"
Exit Sub
If Range("U3") = 1 And Range("C10") = "Revenue Share" And Range("F10") = "Year" Then
makeVisible "ChRevShY"
Exit Sub
If Range("U3") = 1 And Range("C10") = "Growth YoY" And Range("F10") = "Year" Then
makeVisible "ChGroY"
Exit Sub
If Range("U3") = 1 And Range("C10") = "Contribution to Growth" And Range("F10") = "Year" Then
makeVisible "ChContY"
Exit Sub
If Range("U3") = 2 And Range("C10") = "Total Revenue" And Range("F10") = "Year" Then
makeVisible "MktRevY"
Exit Sub
If Range("U3") = 2 And Range("C10") = "Revenue Share" And Range("F10") = "Year" Then
makeVisible "MktRevShY"
Exit Sub
If Range("U3") = 2 And Range("C10") = "Growth YoY" And Range("F10") = "Year" Then
makeVisible "MktGroY"
Exit Sub
If Range("U3") = 2 And Range("C10") = "Contribution to Growth" And Range("F10") = "Year" Then
makeVisible "MktContY"
Exit Sub
If Range("U3") = 3 And Range("C10") = "Total Revenue" And Range("F10") = "Year" Then
makeVisible "ProgRevY"
Exit Sub
If Range("U3") = 3 And Range("C10") = "Revenue Share" And Range("F10") = "Year" Then
makeVisible "ProgRevShY"
Exit Sub
If Range("U3") = 3 And Range("C10") = "Growth YoY" And Range("F10") = "Year" Then
makeVisible "ProgGroY"
Exit Sub
If Range("U3") = 3 And Range("C10") = "Contribution to Growth" And Range("F10") = "Year" Then
makeVisible "ProgContY"
Exit Sub
If Range("U3") = 4 And Range("C10") = "Total Revenue" And Range("F10") = "Year" Then
makeVisible "ProdRevY"
Exit Sub
If Range("U3") = 4 And Range("C10") = "Revenue Share" And Range("F10") = "Year" Then
makeVisible "ProdRevShY"
Exit Sub
If Range("U3") = 4 And Range("C10") = "Growth YoY" And Range("F10") = "Year" Then
makeVisible "ProdGroY"
Exit Sub
If Range("U3") = 4 And Range("C10") = "Contribution to Growth" And Range("F10") = "Year" Then
makeVisible "ProdContY"
Exit Sub
End If
End Sub



Any clues?

Thanks!!!

 

[Ayadi]

Error says "Can't execute in break mode" and takes me back to that line Sub makeVisible(sName)

 

[Ayadi]

Expanding on the post above:

Error says "Can't execute in break mode" and takes me back to that line "Sub makeVisible(sName)" and also "Sub ShowChart()"

Also tried a smaller version of the code and gives another error. You can see it here.

Thank you as always for your help.

 

[tlowry]

Here’s the next go-at-it

Install instructions

• Create a worksheet with all the charts
• Add (with Control Toolbox)
  • Four OptionButtons (OptionButton1, OptionButton2 OptionButton3 OptionButton4)
  • Two ComboBoxes (ComboBox1, ComboBox2)
• Go into development mode (alt F11)
• Select the worksheet’s module (double click on sheet in Microsoft Excel Objects)
• Copy and paste this code into the module
• Exit development mode (alt F11)
• Save the workbook (Ctrl S)
• Run SetupCB

• Test a lot and let me know about the issues you find

What do you want to happen when:

Navigation between worksheets
Initial opening


​

'-----------------------------------------
'-----------------------------------------
' 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
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
        Shapes(ct & 1).DrawingObject.Visible = True
        Shapes(ct & 2).DrawingObject.Visible = True
        Shapes(ct & 3).DrawingObject.Visible = True
        Shapes(ct & 4).DrawingObject.Visible = True
    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! I don't know how to express my gratitude because I believe this is taking you quite a lot of time!! Thank you!!!!

Unfortunately, after following all the steps to the letter, I encountered this error. Nothing seems to work, not the drop downs, not the graphs... It just keeps giving me that error, highlighting "Shapes".

I believe the previous attempt with the data validation also worked very well but when I tried it with more options (my extended version in the previous thread) it gave me a similar error. I'm with Office 2007 if that makes any difference? I just can't understand why it works with a small test and not with more options.. I am very willing to change the names of the graphs if that makes it any easier as well.

Also, as a very last request, is there a way I can tell excell to show the graph in range B12:J29 and make it the size of that range? If not then I'll do it manually once we get it to work, it's not an issue .

I'm so sorry for all the trouble and I'm really appreciative of all your help and time!!

 

[tlowry]

Let's not give up now.

1. There are some codes that cannot be run in break mode. When you run the code I sent, make sure that none of the lines are displayed in a red background in development mode. (Ctrl shift F9)

2. To "resize" charts:

    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