Using ComboBoxes and Buttons to generate XY Scatter plots. RRS feed

  • Question

  • Hey Everyone. I hope everyone is having a great day. My basic concern is learning how to write VBA code that will use buttons and comboBoxes to generate XY scatter plots, but it gets a little more complicated than that. Please bear with me, as I'm still learning the best way to communicate my questions.

    I have 5 Lots, each carrying a unique ID. From these 5 Lots I have derived a series of various data parameters(Para1 thru Para5), for each Lot ID. All of these parameters are stored in their respective sheets(i.e. each given parameter from each Lot ID is stored on a sheet for only that parameter).
    I need to plot the data in each parameter sheet using an XY-Scatter graph, where the X-Axis is always set to Para1 and the Y-Axis varies through the other 4 parameters.
    To generate this graph, I need two Combo Boxes and a button that will allow a user to choose a given parameter(Combobox1) and Lot ID(Combobx2) and plot that selected parameter against the constant X(Para1) from the same LOT.

    I have written code up to this point, sorting and placing the data to be plotted, but now I'm stuck trying to figuring out how to use it to create useful charts. I need help with all of it, but ANY aspect would be helpful. Please let me know if I need to clarify anything or if there is a better way to get at my question. I'm really curious to understand this. Thanks in advance!!

    Wednesday, December 11, 2013 6:39 PM

All replies

  • It would be a lot easier to use formulas in the chart data source that are linked to the cells with the results of the ComboBox selections, and that simply update as those values are changed. No VBA required.

    Wednesday, December 11, 2013 8:38 PM
  • Thanks for that answer Bernie. Where can I find more information about using formulas in the chart data source?

    Wednesday, December 11, 2013 9:10 PM
  •  I think I have come up with a better way of describing what it is Im trying to accomplish. I need to turn this process into VBA code:

    My command button is boolean. if selected(yes), then For this sheet(graphing sheet), if combobox1(ParaBox) has a cell selected from the range B1:B5(representing the five different parameters) from sheet2(OptionsSheet), then copy the sheet with the corresponding(same name for sheet as for selected para) name from the Zarchive workbook in a sheet(copysheet) in this workbook. And if Combobox2(lotBox) has a cell selected from the range C1:C5 from OptionsSheet, then copy the corresponding column(each columns first cell has the LOTID it came from) from the CopySheet into the "B" Column in a new sheet(graphDataSheet) And if LotBox has a cell selected from the range C1:C5 from OptionsSheet then go to sheet1(Para1) in the Zarchive workbook and copy/paste the corresponding Lot column into Coulumn A in GraphDataSheet. If all three true, then XYsmoothscatter plot.

    I know there is alot of holes there insofar as things necessary to make it work, but I think that basic outline should cover my original concern. Please let meknow what you think about this and if there are any aspects of it you might be able to help with.
    Thursday, December 12, 2013 7:05 PM