We help IT Professionals succeed at work.

Marco Excel 2007

Fernandino1977
on
Hello,

I have a table in excel with 135 rows (134 customers + header) and 13 columns (Jan to December + customer name) capturing 12 month of sales for each customer.

excel macro to produce linear chart
I'm trying to produce a macro that when I press a button it ask me which customer of my list of customers captured in my range B3:B134 I wan to produce a linear graph for. If, for instance I chose customer ABC Ltd, which is in in row 3, it then captures the range B2:N2 (the header showing the months) and then B3:N3, which has the monthly sales figures.

Hope this all make sense?

Thanks,

Fernando
Comment
Watch Question

armchair_scouseDeveloper

Commented:
I have a feeling you might need to use VBA to achieve this, though I am prepared to be proven wrong! :o)

You need two elements to fulfil your requirements:
a) the prompt to ask you for which client you wish to produce a linear graph
b) the graph itself

Here's an idea of how you could approach this:
- create your linear chart manually for one customer by doing this:
  - add a new worksheet to your workbook with the table in it
  - copy your data series (months and sales figures) to a defined data area on your chart
     worksheet (e.g. in columns A and B)
  - use the Chart Wizard to build your linear chart (as an example)

Once you have the sample chart, use some VBA code to do the following:
 - prompt for a company name (the InputBox method should do http://msdn.microsoft.com/en-us/library/aa195768(v=office.11).aspx)
 - once the value from the InputBox has been checked against the list of values on your spreadsheet, if a value is found, copy the appropriate data series to your chart template worksheet in the defined data area
 - refresh/update the linear chart (and change the chart title to match the selected company)

If you are unfamiliar with VBA, then say so, and I imagine further examples may appear here... ;o)
armchair_scouseDeveloper

Commented:
I have attached a sample file to illustrate what I said above.  It'll give you a head start.
Chart.xlsm

Author

Commented:
WOW, that's awesome! Thanks!

Is it easy enough to create a drop down menu that allow you to select the company instead of typing it down? I have over 130 customers and that will make life so much easier :)

Fernando
armchair_scouseDeveloper

Commented:
To create a dropdown menu, you'd need to create a UserForm and add a dropdown component to it, and add an OK button, then display the UserForm instead of using the InputBox.  You can populate the the dropdown using an Excel range, using this kind of syntax:

cboCustomer.List = Worksheets("Data").Range("A2:A100").Value

Then once you show the UserForm, the user selects a customer then clicks OK.  You can read the selected value in cboCustomer and then continue the processing from there.
Developer
Commented:
Here's an updated version with a dropdown box on a userform.  Tweak as you see fit.
ChartPlusDropDown.xlsm

Author

Commented:
Your solutions absolutely rocks!

Thanks a lot. I've copied paste the code in my excel sheet and adjusted the columns and range and it's working like a treat.

Thank you so much!

Fernando

Ps.- If I get the time I'll get to learn VBA. So powerful if you know it well :)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.