Creating a pull-down menu with VBA code attached

jsnyder

New Member
Joined
Jan 17, 2005
Messages
15
I am trying to insert a pull-down menu on a sheet, but I don't think I can use the Data-->Validation method. What I am looking to do is have a pull-down menu with five options (the last five months). When the user clicks an option there is a VBA routine executed (updating a chart's range to read data from a different month). I know this may sound silly, but I'm still a basic user and even though I know how to write the code to update the charts, I can't figure out how to create the pull-down menu in the first place. Thanks in advance.

Jason
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
I thin Validation may be just what you need. The nice thing about validation is that the item you select is attached to a cell, just like a cell you would type into. You could use a Worksheet_Change() event to detect changes on the sheet, and if the Target isn't the cell you have validation on, exit the event. Otherwise, do your other VBA code. Could you incorporate something like that? Hope that helps!
 

KleptoOne

Board Regular
Joined
Nov 18, 2004
Messages
111
I have used standard formulas to modify charts depending on a validated dropdown list. It all depends on how you have your data structured. Maybe if you post a sample of your spreadsheet we can find a way of utilizing a match/index method or somthing simular.
 

jsnyder

New Member
Joined
Jan 17, 2005
Messages
15
Basically, I have a sheet with a graph and two sets of option buttons on it that control what's in the graph. One set of buttons changes the range of data that is read into the cells used as the range for the graph (with this type of code).

Private Sub OptionButton1_Click()
Sheets("trck-data").Select
ActiveSheet.Range("A4:O16").FormulaR1C1 = "=R[16]C"
Sheets("trck-all").Select
End Sub

The other set actually updates the range being read by the graph by shifting the columns over (with this code).

Private Sub OptionButton18_Click()
ActiveSheet.ChartObjects("Chart 28").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets("trck-data").Range("A3:E16"), _
PlotBy:=xlRows
ActiveWindow.Visible = False
Windows("V1.xls").Activate
Range("D24").Select

End Sub

The problem is that these option buttons take up too much space on the page, there are 20 buttons in all. I think it would be more space-efficient to use two pull-down menus to manipulate the graph this way but don't know how to do it. I'm new to the Data-->Validation method, so I'm not sure how to get it to do what I want. Thanks for any additional help you can provide!

Regards,
Jason
 

Watch MrExcel Video

Forum statistics

Threads
1,130,307
Messages
5,641,442
Members
417,209
Latest member
Agbarker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top