+ Reply to Thread
Results 1 to 54 of 54

Connect data markers

  1. #1
    Forum Contributor
    Join Date
    06-23-2008
    Posts
    116

    Connect data markers

    Hi all:

    The attached document has a graph that joins all four points of a data marker properly, till the 10th iteration is encounter. At this point, two data markers do not connect. Any suggestions? Thanks.

    - Ravi
    Attached Files Attached Files
    Last edited by ravichander; 02-12-2009 at 12:09 AM.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Unable to connect data markers

    Because the formula in this 2 cells is incorrect.

    BoxesScreen7 $U$6 and $S$6
    Cheers
    Andy
    www.andypope.info

  3. #3
    Forum Contributor
    Join Date
    06-23-2008
    Posts
    116

    Re: Unable to connect data markers

    Hi Andy:

    Thank you for your response. Yes, that fixed it. However, why are Series Point 13 and 14 in bold? Thanks.

    - Ravi

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Unable to connect data markers

    When you say bold do you mean the lines are slightly thicker?

    If so this is just a result of the way the chart is drawn. Try changing the size of the chart and you will see the thicker lines may well change to other series.

    To avoid this you could just plot a single line rather than 2 between each set of points. More difficult to determine which values to use but the thicker lines will go.

  5. #5
    Forum Contributor
    Join Date
    06-23-2008
    Posts
    116

    Re: Unable to connect data markers

    Hi Andy:

    Yes, I meant that some lines were thicker. Would an If statement to check whether a line has already been plotted be the way to resolve this? Thanks.

    - Ravi

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Unable to connect data markers

    Yes. Only output data for 1 vertical. You just need to determine what values to use. You may still need 2 sets of values but try and avoid laying the lines over each other.

    You may be better off using Error bars.

  7. #7
    Forum Contributor
    Join Date
    06-23-2008
    Posts
    116

    Re: Unable to connect data markers

    Hi Andy:

    Change the Chart Type to Error bars? Thanks.

    - Ravi

  8. #8
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Unable to connect data markers

    Not change the chart type. xy-scatter is what you need.

    Instead of joining the points with the normal line use x and y error bars with custom values.
    A similar approach is used to create a step chart
    http://www.andypope.info/charts/stepchart.htm

  9. #9
    Forum Contributor
    Join Date
    06-23-2008
    Posts
    116

    Re: Unable to connect data markers

    Hi Andy: Thank you for the link on the XY Scatter with Error Bars. I honestly do not know how to approach this. Further the points have to be joined to form a box and not just steps as in your illustration. Can you get me started on this? Thanks.

    - Ravi

  10. #10
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Unable to connect data markers

    You take the principle of the step chart and extend it to make boxes.

    See attached.
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    06-23-2008
    Posts
    116

    Re: Unable to connect data markers

    Andy:

    Thank you. I will work on it. Appreciate the help.

    - Ravi

  12. #12
    Forum Contributor
    Join Date
    06-23-2008
    Posts
    116

    Re: Unable to connect data markers

    Hi Andy:

    Here is what I thought of doing.

    The following VBA code checks to see if the first cell has any data. If there is data, then it will call a macro that will process the first line of the example that you gave me.

    Please Login or Register  to view this content.
    This code processes the first line of your example.

    Please Login or Register  to view this content.
    How would I write code to select D21 and check for the existence of any data, and then if there is, increment macro PayGradeBoxesScreen7 to process the next line?

    Thanks.

    - Ravi

  13. #13
    Forum Contributor
    Join Date
    06-23-2008
    Posts
    116

    Re: Unable to connect data markers

    A minor correction. The Range to be slected in the first part of the code is B20 and not D20. Thanks.

    - Ravi

  14. #14
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Unable to connect data markers

    What exactly are you trying to do?

  15. #15
    Forum Contributor
    Join Date
    06-23-2008
    Posts
    116

    Re: Unable to connect data markers

    Hi Andy:

    I am trying to automate the process of populating data just like you had in your example to then generate the graph. Thanks.

    - Ravi

  16. #16
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Unable to connect data markers

    Please Login or Register  to view this content.

  17. #17
    Forum Contributor
    Join Date
    06-23-2008
    Posts
    116

    Re: Unable to connect data markers

    Thanks Andy I will try it out.

  18. #18
    Forum Contributor
    Join Date
    06-23-2008
    Posts
    116

    Re: Unable to connect data markers

    Hi Andy, your solution worked. Thanks a lot!

    - Ravi

  19. #19
    Forum Contributor
    Join Date
    06-23-2008
    Posts
    116

    Re: Unable to connect data markers

    Hi Andy:


    In the worksheet you posted in this thread (670622.xls), in one instance having a value of zero in B20 seemed to display the following error:

    "Run-time error '1004'
    Unable to set the XValues property of the series class"

    This happened only once and I was not able to replicate the issue using a zero in the referenced cell. Any thoughts? Thanks.

    - Ravi

  20. #20
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Unable to connect data markers

    Can you post a workbook that demonstrates the problem

  21. #21
    Forum Contributor
    Join Date
    06-23-2008
    Posts
    116

    Re: Unable to connect data markers

    Hi Andy:

    Here is a work sheet with the actual data that caused the run-time error. However, the error did not show up this time. It happened only once. Thanks.

    - Ravi
    Attached Files Attached Files

  22. #22
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Unable to connect data markers

    Lets worry about it when the error does appear consistently

  23. #23
    Forum Contributor
    Join Date
    06-23-2008
    Posts
    116

    Re: Unable to connect data markers

    Andy:

    I have attached a screen print of the error when it happened. Thanks.

    - Ravi
    Last edited by ravichander; 03-20-2009 at 02:31 PM.

  24. #24
    Forum Contributor
    Join Date
    06-23-2008
    Posts
    116

    Re: Unable to connect data markers

    LOL, OK Andy. I will see if that error surfaces again. Thanks.

    - Ravi

  25. #25
    Forum Contributor
    Join Date
    06-23-2008
    Posts
    116

    Re: Unable to connect data markers

    Quote Originally Posted by Andy Pope View Post
    Lets worry about it when the error does appear consistently
    Hi Andy:

    Here is a situation where the graph was not displayed but came with the same error 1004. Clicking on help said something about macros, which were already enabled to run. In some instance there were messages that said that the worksheet was read only, thus not allowing any changes to be made. This happened on laptops, one with XP, one with Vista, one with 2003 excel one
    with 2007. I don't have a cue as to what might be happening here.

    I have attached the referenced worksheets for your review. Thanks.

    - Ravi
    Last edited by ravichander; 03-20-2009 at 02:31 PM.

  26. #26
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Unable to connect data markers

    you passworded the VBA so I can not see anything.

    It throws an error when loading the splash screen.
    It also errors when deleting series, although I don't know why it's deleting series.

    Looks like you need to add some general error trapping into the code to check for certain states, such as no series.
    You may also encounter problems trying to manipulate chart series that have no data.

  27. #27
    Forum Contributor
    Join Date
    06-23-2008
    Posts
    116

    Re: Unable to connect data markers

    Quote Originally Posted by Andy Pope View Post
    you passworded the VBA so I can not see anything.
    Sent to you.

    Quote Originally Posted by Andy Pope View Post
    It throws an error when loading the splash screen.
    It also errors when deleting series, although I don't know why it's deleting series.
    I think the deletion was to ensure that only the required number of seriese were created each time.

    Quote Originally Posted by Andy Pope View Post
    Looks like you need to add some general error trapping into the code to check for certain states, such as no series.
    You may also encounter problems trying to manipulate chart series that have no data.
    Noticed that changing the name of the file also produces errors.

    As an aside, any issues with Excel 2007 saved as a xls file on the Vista Operating System? Should I start this topic as a new thread? Thanks Andy.

    - Ravi

  28. #28
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Unable to connect data markers

    You need to change the way you reference the chart as currently you are using a name that could change if you created another chart.

    Please Login or Register  to view this content.
    As for error when the workbook name changes that is because, again, you are using fixed names as the reference.

    so instead of references like this
    Please Login or Register  to view this content.
    use
    Please Login or Register  to view this content.

  29. #29
    Forum Contributor
    Join Date
    06-23-2008
    Posts
    116

    Re: Unable to connect data markers

    Thanks Andy. Is there a way of globally changing the reference from the specific workbook to ThisWorkBook.Activate?

    Are there any known issues for Excel 2002-07 runing on Vista? Thanks.

    - Ravi

  30. #30
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Unable to connect data markers

    Find/Replace

    Charts are completely new in xl2007. Some of the Object model uses new properties. And in places the object model is incomplete.
    I don't think you will find a detailed list it's more a case of try it and see. Then fix the problems.

  31. #31
    Forum Contributor
    Join Date
    06-23-2008
    Posts
    116

    Re: Unable to connect data markers

    Thanks Andy.

  32. #32
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Unable to connect data markers

    Deleting or assigning series based on empty cells will cause a problem.

    If you switch chart type to column before deleting that should help.

    I have also changed the way the ranges are assigned so only values rows are used.
    Attached Files Attached Files

  33. #33
    Forum Contributor
    Join Date
    06-23-2008
    Posts
    116

    Re: Unable to connect data markers

    Hi Andy:

    Thank you for your kind response.

    When I tried opening the file you sent, it display a Run time Error: '9', Subscript out of range. What could have triggered this? Thanks.

    - Ravi

  34. #34
    Forum Contributor
    Join Date
    06-23-2008
    Posts
    116

    Re: Unable to connect data markers

    Hi Andy:

    Another observation was that the Pay Policy line is flat on the axis, which should not be the case. Thanks.

    - Ravi

  35. #35
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Unable to connect data markers

    The subscript error is caused by you selecting a sheet that does not exist in the file.

    Please Login or Register  to view this content.
    Check the range used for that series, is it correct?

  36. #36
    Forum Contributor
    Join Date
    06-23-2008
    Posts
    116

    Re: Unable to connect data markers

    Hi Andy:

    I will check on what you have asked and let you know.

    Meanwhile, I noticed that you have created a Macro1. Not sure where you are calling this routine from though. Thanks.

    - Ravi

  37. #37
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Unable to connect data markers

    Probably redundent code used whilst testing.
    I use the macro recorder to generate code sometimes

  38. #38
    Forum Contributor
    Join Date
    06-23-2008
    Posts
    116

    Re: Unable to connect data markers

    Quote Originally Posted by Andy Pope View Post
    The subscript error is caused by you selecting a sheet that does not exist in the file.

    Please Login or Register  to view this content.
    Check the range used for that series, is it correct?
    Hi Andy:

    Yes, the series for the pay policy line was not right. Looks OK now. I will trest and let you know the outcome. Thanks.

    - Ravi

  39. #39
    Forum Contributor
    Join Date
    06-23-2008
    Posts
    116

    Re: Unable to connect data markers

    Hi Andy:

    Here is the problem I am experiencing:

    When displaying the pay structure graph, it displays correctly the first time this button is clicked. However, on any subsequent occasions, it does not display the pay grade or pay range lines, except for the first pay grade. It also gives the following message: “Run Time Error 1004 – This object has no legend."

    I have attached the file for your convenience. Thanks.
    Attached Files Attached Files

  40. #40
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Unable to connect data markers

    make sure the chart has a legend before excuting the command

    Please Login or Register  to view this content.

  41. #41
    Forum Contributor
    Join Date
    06-23-2008
    Posts
    116

    Re: Unable to connect data markers

    Hi Andy:

    Thank you for your kind response.

    I included the statement you suggested and tested the outcome. Wheat I see happening now is that I keep getting legends such as "Series 4", "Series 5" and so on with each time the Display Graph button is clicked. Is this expected?

    Thanks.

    - Ravi

  42. #42
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Unable to connect data markers

    I don't get this when testing.

    You already have code to remove excess series
    Please Login or Register  to view this content.

  43. #43
    Forum Contributor
    Join Date
    06-23-2008
    Posts
    116

    Re: Unable to connect data markers

    Hi Andy:

    Thank you for your kind response.

    As stated in my previous email, I added the code you suggested. Then here is what I did:

    1. Clicked the Display Graph button
    2. The graph was displayed properly with the legends Maximum $, Minimum $, and Pay Policy Line displayed partially in the gray area of the chart.
    3. Clicked the Return button above the graph
    4. For testing purposes, I clicked again on Display Graph. Observed that a new legend Series4 was introduced along with those stated in (2) above.

    My assumption is that if the Display Graph button is clicked more than once, then the above can happen.

    Can this be prevented, since the Display Graph button can be clicked many times for various reasons.

    Also can the font of the legends on the right side of the graph be reduced, then perhaps they won't show up in the gray area of the graph.

    Thanks Andy.

    - Ravi

    P.S. I tested on a clean copy and noticed that the first time the Display Graph button was clicked, the legend on the right side of the graph were in a small rectangle and was displayed on the top right corner and just had Maximum and Minimum. When I clicked the Display Graph button again, then the same legend was display right in the gray area of the graph and included the "Pay Policy Line".
    Last edited by ravichander; 11-08-2009 at 04:29 PM. Reason: To include another observation

  44. #44
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Unable to connect data markers

    I still can not reproduce the problem you describe.
    I can press multiple times the display and return button. I can even delete legend entries or the whole legend and the chart is rebuit.

    change font size using
    Please Login or Register  to view this content.

  45. #45
    Forum Contributor
    Join Date
    06-23-2008
    Posts
    116

    Re: Unable to connect data markers

    Andy:

    Thank you for your kind response.

    For your convenience, I have attached a test copy that illustrates what I have been experiencing. Try the attached file to see what goes on. Thanks.

    - Ravi
    Attached Files Attached Files

  46. #46
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Unable to connect data markers

    No difference. Legend if as expected still.

    But I do get an error when pressing the Display graph button.

    #1004: Unable to set the scrollcolumn

  47. #47
    Forum Contributor
    Join Date
    06-23-2008
    Posts
    116

    Re: Unable to connect data markers

    Hi Andy:

    Thank you for your response.

    I have not seen the error you have identified, but thank you for catching it.

    I made a copy of the file I sent you, and did the following:

    1. Deleted the legend on the right side of the graph
    2. Deleted the contents of all cells
    3. Saved the file
    4. Re-entered data
    5. Clicked Display Graph
    6. Observation: Graph and teh boxes were displayed properly, except that the 3 legends to the right of the graph were not in the white part of the graphs, rather partially in the gray area.
    7. Clicked Return
    8. Clicked Display Graph again
    7. Observation: Graph and the boxes were displayed, however, five new series were displayed (in addition to those in (6) above) in the legend as "Series4.. to Series8". I hope you see this happening.

    Thanks.

    - Ravi

  48. #48
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Unable to connect data markers

    No I do not see this happening. In fact with the error I see nothing happening as the code does not complete the chart.

    What is the VB password?

    What version of xl are you using?

  49. #49
    Forum Contributor
    Join Date
    06-23-2008
    Posts
    116

    Re: Unable to connect data markers

    Hi Andy:

    I will send you the password. I am using Microsoft Office 2007 version of Excel and have saved the file as a 93-97 compatible version. The Operating System is Windows XP with the latest service pack. I can't figure out why I see this and you do not. Thanks.

    - Ravi

  50. #50
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Unable to connect data markers

    I have been testing in xl2003 as your profile does not specify version and the file was not in xl2007 format.

    With xl2007 I can reproduce your problem, caused by adding series after removing excess via code.

    try this revision to the display graph button routine.

    Please Login or Register  to view this content.

  51. #51
    Forum Contributor
    Join Date
    06-23-2008
    Posts
    116

    Re: Unable to connect data markers

    Hi Andy:

    I added the code you suggested, and it worked like a charm. The legend now only show the Max, Min and Pay Policy line. I'll incorporate this change into the full version and let you know the outcome.

    As always, you kind assistance and knowledge in this area is really appreciated.

    - Ravi

  52. #52
    Forum Contributor
    Join Date
    06-23-2008
    Posts
    116

    Re: Unable to connect data markers

    Hi Andy:

    The pay structure graph is displayed correctly the first time it comes up. However, if any changes are made to the minimum and maximum dollars, and the new data is saved, the graph will not display properly. The "Run-time error '1004' This object has no legend." message comes up.

    While debugging, this error message occurs in the following macro.

    Sub PG1

    ActiveChart.Legend.LegendEntries(ActiveChart.Legend.LegendEntries.Count).Delete

    I am not sure how to address the issue when data is changed, and the data can be changed to get the right fit. To see what actually happens, try changing the data, say limit it to five pay grades, change the min and max dollars, save and then click Display Graph, and you will see the error dialog box.

    Your assistance will be very much appreciated. Thanks Andy.

    - Ravi

  53. #53
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Connect data markers

    again I can not reproduce the error you mention.

    Try changing the click event code to restore legend and the remove entries.

    Please Login or Register  to view this content.

  54. #54
    Forum Contributor
    Join Date
    06-23-2008
    Posts
    116

    Re: Connect data markers

    Hi Andy:

    Apologize for the delayed response (was not well). The code you gave above worked and tested out OK with changes to the number of pay grades. Hopefully nothing else surfaces.

    Again. thank you very much for you kind assistance.

    - Ravi

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1