Hide/view chart based on cell value

[AlexPi]

I'm new to VBA and trying to learn a few tricks from this forum. I have some code below that I cannot get to work correctly.

I want to show or hide 3 different charts based on the cell value in G20. I have a data validation list in G20 that has 3 options, Estimate, Goal, or Actual cost. I want the user to be able to choose a name from the list and a different chart to appear.

I think I'm close but I'm probably missing something...Right now it will only show Chart 2.

Thanks for any help!

Sub getchart()
 
If Range("G20") = "Estimate" Then
          Sheets("Overview").ChartObjects("BudgetOverview").Visible = True
            Sheets("Overview").ChartObjects("Chart 1").Visible = False
            Sheets("Overview").ChartObjects("Chart 2").Visible = False
If Range("G20") = "Goal" Then
            
         Sheets("Overview").ChartObjects("BudgetOverview").Visible = False
            Sheets("Overview").ChartObjects("Chart 1").Visible = True
            Sheets("Overview").ChartObjects("Chart 2").Visible = False
            
            
Else
            
        Sheets("Overview").ChartObjects("BudgetOverview").Visible = False
          Sheets("Overview").ChartObjects("Chart 1").Visible = False
          Sheets("Overview").ChartObjects("Chart 2").Visible = True            
 
            
     End If
     End If
     
End Sub

 

[Trevor G]

You need either an end if or else if like this

If Range("G20") = "Estimate" Then
Sheets("Overview").ChartObjects("BudgetOverview").Visible = True
Sheets("Overview").ChartObjects("Chart 1").Visible = False
Sheets("Overview").ChartObjects("Chart 2").Visible = False
End IF

If Range("G20") = "Goal" Then

Sheets("Overview").ChartObjects("BudgetOverview").Visible = False
Sheets("Overview").ChartObjects("Chart 1").Visible = True
Sheets("Overview").ChartObjects("Chart 2").Visible = False
End IF

If Range("G20") = "Actual cost" Then
Sheets("Overview").ChartObjects("BudgetOverview").Visible = False
Sheets("Overview").ChartObjects("Chart 1").Visible = False
Sheets("Overview").ChartObjects("Chart 2").Visible = True
End IF

Or like this

Sub getchart()
If Range("G20") = "Estimate" Then
Sheets("Overview").ChartObjects("BudgetOverview").Visible = True
Sheets("Overview").ChartObjects("Chart 1").Visible = False
Sheets("Overview").ChartObjects("Chart 2").Visible = False
ElseIf Range("G20") = "Goal" Then
Sheets("Overview").ChartObjects("BudgetOverview").Visible = False
Sheets("Overview").ChartObjects("Chart 1").Visible = True
Sheets("Overview").ChartObjects("Chart 2").Visible = False
Else
Sheets("Overview").ChartObjects("BudgetOverview").Visible = False
Sheets("Overview").ChartObjects("Chart 1").Visible = False
Sheets("Overview").ChartObjects("Chart 2").Visible = True
End If
End Sub

 

[AlexPi]

Perfect! that's exactly what I needed. Now my next question is can I get it to automatically run that code when the cell value changes?

Thanks for your help!

 

[Trevor G]

Use the right mouse button on the worksheet name and select view code, then from the drop downs at the top select the General and change that to Worksheet, Make sure the right drop down shows you SelectionChange and then add your code in there, I have done a test and it seems to work OK.

 

[AlexPi]

hmmm...still can't get it to work. When I change the drop down menu's the new code I get is

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("G20").Value = "Estimate" Then
          Sheets("Overview").ChartObjects("BudgetOverview").Visible = True
            Sheets("Overview").ChartObjects("Chart 1").Visible = False
            Sheets("Overview").ChartObjects("Chart 2").Visible = False
ElseIf Range("G20").Value = "Goal" Then
            
         Sheets("Overview").ChartObjects("BudgetOverview").Visible = False
            Sheets("Overview").ChartObjects("Chart 1").Visible = True
            Sheets("Overview").ChartObjects("Chart 2").Visible = False
            
Else
            
        Sheets("Overview").ChartObjects("BudgetOverview").Visible = False
          Sheets("Overview").ChartObjects("Chart 1").Visible = False
          Sheets("Overview").ChartObjects("Chart 2").Visible = True
 
            
     End If
End Sub

 

[AlexPi]

oops my mistake! I got it to work. Thanks for all your help!

Alex

 

[Trevor G]

Pleased to read you have a working solution Alex.

 

[Rabbit1980]

Hello,
Whit this VBA you help me a lot, thanks for that.

I have just one problem and I will be realy happy if you help me.
I make few buttons and I show/hide charts by them. But the problem is that if I clik to button nothing happend. Afte I click somewhere at sheet, just after that the chart shown.
I also try to use ApplicationSreen updating but with no success.

Thanks for your help