Results 1 to 15 of 15

Thread: charts (2000)

  1. #1
    New Lounger
    Join Date
    Feb 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    charts (2000)

    Hi! I have a problem and hope you can help me.
    I have a data consist of 10 company share prices. I need to build a chart for each of them. Is there any easier way to build the chart without needing to do 10 different macros? Also, the user cannot select the range of the data, the data is suppose to be invisible. The user only need to choose the name of the company from the list boxes and chart of that company will appear.

    Please help.
    Thank you.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: charts (2000)

    Could you elaborate on the data setup an desired results.

    It is possible to have 1 chart created and from a pick list have the company chosen and the plot for that company made. It might need any macros (depending on how it is set up).

    Steve

  3. #3
    New Lounger
    Join Date
    Feb 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: charts (2000)

    Well, the data look like this:
    Company A 150404 1.72 Company B 150404 1.6
    Company A 160404 1.70 Company B 160404 1.5

    So, say there's company A and B. 2nd column is date and 3rd is share price. What I want to do is, I will display a list of all the company and when the user choose a company, say company A, then the chart of the share price of company A will pop up. I know you can do this with recorded macro but I have 20 company right now and it will be a bit messy to record 20 macro for each company.
    And, I cannot let the user choose the range themselves because the data of this share price is invisible(hide in the workbook).
    So are there any other ways than record 20 macros?
    Hope this make it clearer what my problem is.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: charts (2000)

    Still not clear of the setup, more details please: perhaps post a sample workbook, and indicate the "hidden" items and what the use will see.

    I can see ways to do it with INDEX (if each company data is in separate columns) or even with an autofilter (if company name is all in the same column).

    With the first, you can create a forms toolbar pulldown (could even be put on the chart itself) and the selection will add the "column" for the index), extract that column of data.

    In the second, the pulldown is used (this would require a macro) to do an autofilter on the data. The chart would be based on the entire dataset and charting only shows the filtered data.

    You could do some "formula setup" to extract instead of filtering, but this is a more complicated setup.

    In either case (formula route, macro, etc) we need more details of your setup, to explain how to do it if this isn't enough details.

    Steve

  5. #5
    New Lounger
    Join Date
    Feb 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: charts (2000)

    well, here I attach how the data look like. There are 2 worksheets: The worksheet which contain the data of share prices is the one that will be invisible and the user only able to see the 2nd worksheet where there's suppose to be list box to let user choose the company.

    Hope this do help.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: charts (2000)

    What is the meaning of the data in your "invisible sheet"? Are columns A:C for one company, columns D:F for another company etcetera?

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: charts (2000)

    I am not clear what you want to make a chart of.

    If the use select the company "ABC" for example, you have 2 sets of 3 points. Is one the "X" and the other the "Y" values? Will you always have 3 data-pairs? Will the number of pairs be the same for all companies? Will the number of pairs stay constant?

    We need more details...

    Steve

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: charts (2000)

    Try this example chart:
    I took the "invisible sheet" and added a new column.
    In A4:A6 I have the list of company names.
    In A3 is the number of the selected company, In A2 is the label for the chart.

    The chart plots the 3rd col of data agains the 2nd col. You can adjust as appropriate

    I have added a forms toolbar combobox to select the company. The names are pulled from A4:A6 and the "cell link" is A3

    The "magic" is done via named ranges. Insert - name -define and the following names/refersto were created

    <table border=1><td>name</td><td>RefersTo</td><td>cboNames</td><td>=OFFSET(INDIRECT("'invisible sheet'!$A$4"),0,0,CompCount,1)</td><td>ChartX</td><td>=OFFSET(INDIRECT("'invisible sheet'!$A$1"),0,ColNumber+1,ColRows,1)</td><td>ChartY</td><td>=OFFSET(INDIRECT("'invisible sheet'!$A$1"),0,ColNumber+2,ColRows,1)</td><td>ColNumber</td><td>=3*(ColSelected-1)+1</td><td>ColRows</td><td>=COUNTA(OFFSET(INDIRECT("'invisible sheet'!$A$1"),0,ColNumber,65536,1))</td><td>ColSelected</td><td>='invisible sheet'!$A$3</td><td>CompCount</td><td>=COUNTA('invisible sheet'!$1:$1)/3</td></table>

    "cboNames" is the list of compnay names. It starts in A4 and expands as new columns form companies are added. It gets the length from the named range Company count, which counts the number of columns with data and divides it by three.

    I added a formula in A4:
    <pre>=INDEX(1:1,3*(ROW()-4)+2)</pre>

    and copied it down the to row88 which will be complete max for number of columns)

    When you select from the combobox it reads from the list of company names and puts the result in cell named "ColSelected"
    ColSelected is used in "ColNumber" to determine, based on the selection, which col number offset is for that column. [=3*(ColSelected-1)+1] which means if you choose comp1, the first col from A is selected ([img]/forums/images/smilies/cool.gif[/img], if comp 2 is selected the col 4 to the right of A is selected (E), etc.
    Using ColNumber, "ColRows" is calculated, by determining the number of items in the selected column. This allows the companies to have different numbers of values (NOTE: there must be no blanks within the dataset or this will not work)

    Given a colnumber and colrows, the "ChartX" range is 1 col from the "colNumber" and that many rows, and the "ChartY" range is 2 col from colNumber

    The chart has a formula (='invisible sheet'!$A$2) for the title, so as you choose a company the title changes. The X,Y ranges are based on the named range, so as the company is selected the data that is plotted also changes...

    This has no macros it is all formulas. All the user has to do is select the company from the pulldown and the chart is updated.

    You can Hide the invisible sheet if desired.

    Hope this helps,
    Steve

  9. #9
    New Lounger
    Join Date
    Feb 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: charts (2000)

    well steve, what u just did is exactly what i want but i did not learn index in my vba class so it's gonna be a bit suspicious to use them in my project.
    ABC is the name of 1 company, 2nd column is date and 3rd column is the share price. The data will stay like that:
    1st column:name of the company.
    2nd column: date
    3rdcolumn: share price

    hmm.. do you have other ways in solving my problem? Using macro perhaps?
    But what you showed is really great, wish I learnt that in my class. Thanks Steve.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: charts (2000)

    There are all sorts of other ways, some with macro some without.

    You can create 50 charts (one for each company) and have the macro choose that chart.

    You can have the macro do what I do with index,
    You can have a macro do most anything...

    What do you want the macro to do? I have no idea what macro coding you have learned. If you haven't learned about using INDEX in class. I am not sure what macro techniques you have learned either.

    Also if this is for a class, don't you think you should be doing it yourself rather than getting others to do it for you <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Steve

  11. #11
    New Lounger
    Join Date
    Feb 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: charts (2000)

    yeah, i should do it myself but i've got no idea how to do it. I only know how to build the chart using the macro. And I only gave you sample data [img]/forums/images/smilies/wink.gif[/img].
    Hmm.. when a user click a combo box to choose the company, I want a chart of that company share price to pop up. I know I can do this by recording macro to build the chart but I've got 20 company so it will be messy to do 20 macro.
    So, I would like to know if there's another way to do this? If none, then I guess I should just stick to record 20 different macros.

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: charts (2000)

    You only need 1 macro that runs from the combobox.

    Once the company is chosen, create the chart from the appropriate data.

    Or you can create the chart with just one set of the data, then with the macro reassign the range

    Or you can create the chart based on a named range, and use the macro to redefine this name

    There are multiple ways depending on the techniques you have learned in class.

    Steve

  13. #13
    New Lounger
    Join Date
    Feb 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: charts (2000)

    well, i create a chart by recording a macro. Then I edit it, here the code I edit:

    Sheets("1").Select
    ActiveSheet.Range("a1").Select
    Charts.Add
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SetSourceData Source:=ActiveSheet.ActiveCell.End(xlToRight), _
    PlotBy:=xlColumns

    Why it wouldn't work when I change the "source data" to activesheet.activecell as above?
    Please tell me what is wrong with the code. Thanks

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: charts (2000)

    At this point in the macro, the activesheet is the chart you just added and it has no "activecells"

    Even if the activesheet were a worksheet, are you trying to set the source data to only 1 cell: ActiveSheet.ActiveCell.End(xlToRight) is only 1 cell and usually one plots a range and makes the source at least 2 rows and at least 2 columns...

    What source range do you want to set?

    you could do something like this
    ActiveChart.SetSourceData Source:=Worksheets("1").Range("B1:C3"), _
    PlotBy:=xlColumns

    The source needs to be a range and you must define the worksheet the range is on.

    Steve

  15. #15
    New Lounger
    Join Date
    Feb 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: charts (2000)

    oh,ok, now i understand. Thanks.
    Have found the solution to my problem now. Thanks for all your help. [img]/forums/images/smilies/biggrin.gif[/img]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •