Connect data markers

**Andy Pope** · 11-09-2009, 11:29 AM

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

**ravichander** · 11-09-2009, 12:18 PM

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

**Andy Pope** · 11-09-2009, 12:23 PM

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?

**ravichander** · 11-09-2009, 12:40 PM

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

**Andy Pope** · 11-09-2009, 12:51 PM

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.

Code:

Private Sub CommandButton6_Click()
    'If Range("BL9") > 0 Then
     '   MsgBox "DATA ENTRY IS INCOMPLETE!", vbExclamation, "Warning"
    'Else
        Call DisplayPayStructureGraph
    'End If
    
    Do While ActiveSheet.ChartObjects(1).Chart.Legend.LegendEntries.Count > 3
        ActiveSheet.ChartObjects(1).Chart.Legend.LegendEntries(4).Delete
    Loop
    ActiveSheet.ChartObjects(1).Chart.Legend.Font.Size = 10
    
    
End Sub

**ravichander** · 11-09-2009, 01:09 PM

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

**ravichander** · 11-15-2009, 09:44 PM

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

**Andy Pope** · 11-16-2009, 05:24 AM

again I can not reproduce the error you mention.

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

Code:

Private Sub CommandButton6_Click()
    'If Range("BL9") > 0 Then
     '   MsgBox "DATA ENTRY IS INCOMPLETE!", vbExclamation, "Warning"
    'Else
        Call DisplayPayStructureGraph
    'End If
    
    ActiveSheet.ChartObjects(1).Chart.HasLegend = False
    ActiveSheet.ChartObjects(1).Chart.HasLegend = True
    Do While ActiveSheet.ChartObjects(1).Chart.Legend.LegendEntries.Count > 3
        ActiveSheet.ChartObjects(1).Chart.Legend.LegendEntries(4).Delete
    Loop
    ActiveSheet.ChartObjects(1).Chart.Legend.Font.Size = 10
    
    
End Sub

**ravichander** · 11-23-2009, 09:44 PM

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