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