Thread: Macro for chart?

Originally Posted by mag7417

Hi!

I am having problems making a macro (or something else) that will allow me to plot data from columns I select.

OK. I have a sheet with, say, 10 columns with data. I want to be able to plot any number of them against time (which is in my 11th column). I want to, on a separate sheet have the columns of all listed next to check boxes so that the user can check any of them and have excel plot them in a chart next to the pick-list.

Can anyone help me?

Hi,

one way would be a small Userform with 10 checkboxes, and 'Hide' any column not checked.

Excel will then follow the Tools, Options, Chart setting to show only Visible data.

Does this help?

---

Originally Posted by mag7417

Yeah, in a way it does. The prob though is that I can think of all sorts of good things that could be done. Say, e.g. that I have three white cells in the one sheet where I want the chart. In those I could simply type the column headings of the columns I want in the chart. That is, if I type "One" Excel looks in the other sheet for the column named "One" and uses that data for the plot. If I write "Two" it takes the cell with the "Two" heading and if I leave the cell empty, nothing is plotted. This would allow for three columns of data to be plotted.

Another option would be to first create my chart with my reference data (constant) and the time line (also constant). I then go to the sheet with the data and write some VB code so that all I need to do is to activate the column heading cell and press, say CTRL-p, and it plots that column in my chart.

I just ran into a big, fat brick wall as far as to actually figure out how to do any of these two things or use the solution you provided me with.

you could also set it so that if you click in row 1 the column is hidden, but if you click in A1, A2, A3 etc the column (1 2 3 = A B C etc) are Unhidden, with a Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
No one really wants to type column names when they can click-on them in a cell.

try the attached for that to see (ignore the Duck words, re-type them as column names.)
you may also need to exclude A1 in the action

hth
---