dynamic graph based on drop down list

[jyoon731]

Hi, I am new to excel vba, and I need some urgent help from the masters of VBA.
I will need help to program these two steps.



1. How can I insert a predetermined value into certain range based on the result of the the drop down menu?

For example, if there is a drop down list containing values 1 and 2 in cell A1,
I want the different values to appear in a table in columns F:G according to the drop down result.

17077

2. Then, I want to make a dynamic bar graph with data in columns F:G, which can automatically resize according to the size of the data I have put in.

Please please can anyone help with this coding?


Many thanks,
Jungyoon

17076

[Kenneth Hobs]

Welcome to the forum!

Normally, the best way to do that is to just set your chart to a named range. Then you don't need a 3rd table.

Of course the worksheet's change event would trigger the change. Doing it by a different RefersTo in a named range or deleting the data in F&G and then filling by table 1 or table 2 is simple too.

Without names, right click the sheet's tab, View Code, and paste:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Range("F3", Range("F3").End(xlDown)).Resize(, 2).Value = ""
Select Case Target.Value
Case 1
Range("C3", Range("C3").End(xlDown)).Resize(, 2).Copy Range("F3")
Case 2
Range("C9", Range("C9").End(xlDown)).Resize(, 2).Copy Range("F3")
Case Else
End Select
End Sub

[jyoon731]

Hi Thanks so much for your help! It helped me a lot!
I just have one more connecting question.

Regarding part of your code "If Target.Address <> "$A$1" Then Exit Sub"

how can I customize this so that I can get the same value even when the dropdown menu (which was originally in cell A1) has been moved to another worksheet? (say from "Sheet 1" to "Sheet 2")?
I tried to define the specific worksheet where the drop down menue will be as below, but it won't work.


17104


Can you please help?

Many thanks,
Jungyoon

[Kenneth Hobs]

Sheet Change event firing is what it is doing. You can simply copy the code to each sheet object or I could show you how to pass a Target range to a Sub in a Module. Your Change event code would then just call the Module's Sub.

[jyoon731]

Hi, thank you for the explanation.

Can you show me how I can write my target range to a Sub in a Module in cases where the drop down menu will be in Sheet 2 cell A1, and the values to be
inserted according to the drop down menu in a specific range will be in Sheet 1?

I am attaching an example file for better understanding.

Thank you once again.
Jungyoon

[Tom Jones]

With small modification of Kenneth's code.
Put this in Sheet2


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Sheet1.Range("F3", Sheet1.Range("F3").End(xlDown)).Resize(, 2).Value = ""
Select Case Target.Value
Case 1
Sheet1.Range("C3", Sheet1.Range("C3").End(xlDown)).Resize(, 2).Copy Sheet1.Range("F3")
Case 2
Sheet1.Range("C9", Sheet1.Range("C9").End(xlDown)).Resize(, 2).Copy Sheet1.Range("F3")
Case Else
End Select
End Sub