Excel Scatter Chart Point Macro Crashing

[RichardWale]

I had a post on the MS Office forum about how to interrogate an Excel Scatter Chart point so that I could display more information about the record that had created the point.

Skip Vought replied immediately with a cracking answer, which I have incorporated and works very well, with one exception, in that in one specific circumstance Excel crashes. I am running Excel 2010.

I have tried to put in 'Error Trapping' and that appeared to work, but it still crashes sometimes.

When a point is selected, I have created an option to go to the 'Enquiry' screen for more details about the item that the point represents, or stay on the 'Cosmos Chart' and select another point - it is the second 'No' option that seems to be causing the problem.

I wondered if it is something to do with the mouse and 'focus', so tried putting in some 'SendKeys ("{ESC}")' lines into the code, but that appeared to cause more problems and the macro would 'hang' - seems to be a well-known issue with VBA? I have taken most of the 'SendKeys ("{ESC}")' lines out of the code.

A full working version of the Stock Model is attached, and the sequence that triggers a crash seems to be:

- Open the analysis and enable macros
- From the worksheet tabs, select 'Cosmos Chart'
- Click on any point and choose 'No'
- Chart now has multiple data points 'highighted' - how does this happen?
- Repeat the data point selection and 'No' and only a single point is highlighted, the one selected
- Repeat the point selection and 'No' a number of times and Excel crashes, AND at the point that it crashes turns off the keypad 'Num Lock' - that is really bizarre! Sometimes it crashes after the first point and 'No' have been selected
- Selecting 'Yes', AND using the 'Cosmos Chart' button at the top right of the 'Enquiry' screen, then selecting a point and 'No', does not appear to create any problems - even repeating the complete cycle of 'Yes' and 'No' a number of times
- Selecting 'Yes' and then returning directly to the 'Cosmos Chart' worksheet by selecting the worksheet tab, AND then selecting a point and 'No' can cause a crash
- Selecting 'Yes' and then returning directly to the 'Cosmos Chart' worksheet by selecting the worksheet tab, AND then selecting a point and 'Yes' appears to be fine

I have tried replicating the steps that the 'Yes' option takes as part of the 'No' option in the macro, but it still crashes fairly predictably.

This is the code, but the full working Excel file is attached:

Option Explicit
Private Sub Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)

'Skip Vought 2016 Nov 18, added to by Richard Wale 19 Nov 2016
'click on a data point and chose bewteen selecting another data point,
'or viewing the selected part number in the 'Item Enquiry' screen

Dim IDNum As Long
Dim a As Long
Dim b As Long
Dim xl As Application, ws As Worksheet
Dim itemrefrange As String
Dim itemref As String
Dim response1 As String
Dim itemdesc As String

Sheets("Cosmos Chart").Select
itemrefrange = Sheets("Formulae").Range("A1203").Value
With ActiveWorkbook.Names("Item_Ref")
.Name = "Item_Ref"
.RefersToR1C1 = itemrefrange
.Comment = ""
End With

Set xl = Application
Set ws = Sheets("Cosmos Data")

Sheets("Cosmos Chart").Select
ActiveChart.GetChartElement x, y, IDNum, a, b

If IDNum = xlSeries Then
itemref = xl.Index(ws.Range("Item_Ref"), 1, b) 'log Item Ref
End If

Sheets("Formulae").Select
Range("A1208").Value = itemref
If IsError(Range("A1209").Value) Then 'clicking on non-data points creates an error
GoTo LabelMsg
End If
itemdesc = Range("A1209").Value 'find item description
Sheets("Cosmos Chart").Select
response1 = MsgBox("Item: " & itemref & Chr(10) & "Desc: " & itemdesc _
& Chr(10) & Chr(10) & "Press 'Yes' to continue to 'Item Enquiry' Screen" _
& Chr(10) & Chr(10) & "or" & Chr(10) & Chr(10) & _
"Press 'No' to select another point on the Cosmos Chart", _
vbYesNo + vbDefaultButton1, "Stock Model 2010 - Cosmos Chart Enquiry")

Set ws = Nothing
Set xl = Nothing

If response1 = vbYes Then
Sheets("Cosmos Data").Select
Cells(1, b + 15).Select 'select appropriate item record
Call System_Macros.Enquiry5
GoTo Labelend
End If

Sheets("Cosmos Data").Select 'need to select different ws to avoid Excel error
Cells(1, b + 15).Select 'select appropriate item record
Call System_Macros.Enquiry5
Call System_Macros.Cosmos_Chart

GoTo Labelend

LabelMsg:

Sheets("Cosmos Chart").Select
MsgBox "Only click on data points" & Chr(10) & Chr(10) & _
"Anywhere else can cause errors", , "Cosmos Chart Warning"
Call System_Macros.Cosmos_Chart

Labelend:

End Sub

Can anyone see anything that I have done that could cause this please?

It's so infuriating, as the data point 'click to enquire' works really well otherwise.

If there is no answer to the problem, I will have to remove the Yes/No option and just default to the 'Enquiry' screen after selecting a data point.

 

[SkipVought]

Hi,

Just briefly reviewed the code you posted.

I'd suggest a slightly different approach to event coding. Think in terms of smaller chunks of code that do a limited task.

The first task processes the mouse_down event on the chart that captures a data point and calculates the Item. It ends with a Small UserForm that displays the Item and give three options: a) go to the 'Enquiry' screen for more details about the item that the point represents, b) stay on the 'Cosmos Chart' or c) Exit (optional)

You've got a bunch more happening after this choice. This other stuff, it seems to me, ought to be decoupled from the event. You need some other event to process what happens when you go to the Enquiry screen, maybe its the Sheet_Activate event, but that would mean that any time that the Enquiry sheet is activated, then that code will process. It could be a button event on that screen or a Selection event. I don't know how your process works.

BTW, I did see some familiar things, like replenishment and inventory. I've worked in a supply chain organization. Did net inventory, requirements, replenishments (surplus/shortage) interactive charts, bill of material shortage analysis (we had deep bills, multiple major assemblies, 24 month lead times) for aircraft, MRP, MRPII.

Retired now.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Holy macro! What are you doing right off the bat in the Mouse_down event?

'
    Sheets("Cosmos Chart").Select
    
    itemrefrange = Sheets("Formulae").Range("A1203").Value
    
    With ActiveWorkbook.Names("Item_Ref")
        .Name = "Item_Ref"
        .RefersToR1C1 = itemrefrange
        .Comment = ""
    End With

This has ALREADY been defined as Item! So why do you need itemrefrange? Any time you migh change the data points, your hard value will be OUT OF DATE!

This range is automatically calculated via...

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.DisplayAlerts = False
    [[b]Item_ref[/b]].CurrentRegion.CurrentRegion.CreateNames False, True, False, False
    Application.DisplayAlerts = True
End Sub

...which, it appears, that you failed to copy to the Cosmos Data Sheet Object, and modify accordingly.

So, assuming that you add the Worksheet_Change event code to the Cosmos Data Sheet Object code sheet, you can delete the unnecessary code for itemref.

ALSO, there is no need to Sheets("Cosmos Chart").Select since this mouse_down event is in the Cosmos Chart sheet! Yer already there!

AND...

    Sheets("Formulae").Select
    Range("A1208").Value = itemref

...avoid Select & Activate unless necessary. This code should be...

    Sheets("Formulae").Range("A1208").Value = itemref

Then

'
        Sheets("Cosmos Chart").Select
        MsgBox "Only click on data points" & Chr(10) & Chr(10) & _
            "Anywhere else can cause errors", , "Cosmos Chart Warning"

Totally unnecessary! If you code the event to exit if any other object is selected....

'
    If IDNum = xlSeries Then
        itemref = xl.Index(ws.Range("Item_Ref"), 1, b)                          'log Item Ref
'do all the rest of the stuff you want to do here, inside this IF...End If.
'that way any other selection falls to the end.
    End If
'these are the last things in the event.
    Set ws = Nothing
    Set xl = Nothing

I can't understand this...

Sheets("Formulae").Select
Range("A1208").Value = itemref
If IsError(Range("A1209").Value) Then 'clicking on non-data points creates an error

Why would an error in Formulae!A1209 mean anything on the Chart? I do see that A1208 is storing the Item corresponding to the selected data point. But what is itemdesc? Is that a static value? I can't see where it is assigned.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Private Sub Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)

    'Skip Vought 2016 Nov 18, added to by Richard Wale 19 Nov 2016
    
    'click on a data point and chose bewteen selecting another data point,
    'or viewing the selected part number in the 'Item Enquiry' screen
    
    Dim IDNum As Long
    Dim a As Long
    Dim b As Long
    Dim xl As Application, ws As Worksheet
    Dim itemref As String
    Dim itemdesc As String
    Dim response1 As Variant
   
    Set xl = Application
    Set ws = Sheets("Cosmos Data")
        
    ActiveChart.GetChartElement x, y, IDNum, a, b
    
    If IDNum = xlSeries Then
        itemref = xl.Index(ws.Range("Item_Ref"), 1, b)                          'log Item Ref
        Sheets("Formulae").Range("A1208").Value = itemref
        itemdesc = Sheets("Formulae").Range("A1209").Value
        
        response1 = MsgBox("Item:  " & itemref & Chr(10) & "Desc:  " & itemdesc _
            & Chr(10) & Chr(10) & "Press 'Yes' to continue to 'Item Enquiry' Screen" _
            & Chr(10) & Chr(10) & "or" & Chr(10) & Chr(10) & _
            "Press 'No' to select another point on the Cosmos Chart", _
            vbYesNo + vbDefaultButton1, "Stock Model 2010 - Cosmos Chart Enquiry")
        
        Select Case response1
            Case vbYes
                ws.Select
                Cells(1, b + 15).Select                                                 'select appropriate item record
                Call System_Macros.Enquiry5
            Case vbNo
                'do nothing -- stay on Cosmo Chart
            Case Else
                'go somewhere else???
        End Select
        
    End If
        
    Set ws = Nothing
    Set xl = Nothing
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

You have a sheet named Formulae. I often had a sheet called Factors. Same purpose--to document various constants and variable values as they are encountered in events.

I Named all my ranges where these values resided. NEVER USED A1 or R1C1 notation. They are virtually MEANINGLESS!

Here's how it might change your code...

'....
        itemref = xl.Index(ws.Range("Item_Ref"), 1, b)    'log Item Ref
        With [ItemRef]                                    'range in Formulae!A1208
           .NumberFormat = "@"
           .Value = itemref                         
        End With
        itemdesc = [ItemDesc].Value                       'range in Formulae!A1209
'....

...There is a feature in Formulas > Defined Names > Create from Selection that I use a lot. I would typically have the Range Names in column A and the data in column B, something like this
[pre]
ItemRef 000010
ItemDesc Desc 000010
[/pre]
So using Create names from values in the > LEFT column. Use the same technique when the names are in the TOP row, for tth whole table.

Better yet, you ought to check out the Structured Table feature that was introduced in version 2007.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[RichardWale]

Skip,

Lots to look at now, thanks (I think!). Yes, definitely thank you.

A couple of explanations:

Holy macro! What are you doing right off the bat in the Mouse_down event?

CODE
'
Sheets("Cosmos Chart").Select

itemrefrange = Sheets("Formulae").Range("A1203").Value

With ActiveWorkbook.Names("Item_Ref")
.Name = "Item_Ref"
.RefersToR1C1 = itemrefrange
.Comment = ""
End With

This has ALREADY been defined as Item! So why do you need itemrefrange? Any time you might change the data points, your hard value will be OUT OF DATE!

The 'Hard Value' is not 'Hard', the formula in 'Sheets("Formulae").Range("A1203").Value' is updated with the current range, and is used to update the 'Named Range' itemrefrange - I need to understand and use the code you suggested to do the same thing.

The 'Name' 'Item' that was in the original code is already used in the Stock Model, so I changed it for the Cosmos Chart code to 'Item_Ref'.

I can't understand this...

CODE
Sheets("Formulae").Select
Range("A1208").Value = itemref
If IsError(Range("A1209").Value) Then 'clicking on non-data points creates an error

Why would an error in Formulae!A1209 mean anything on the Chart? I do see that A1208 is storing the Item corresponding to the selected data point. But what is itemdesc? Is that a static value? I can't see where it is assigned.

The 'itemref' is pasted to cell A1208 in the 'Formulae' worksheet, and there is a 'vlookup' formula in cell A1209 that returns the corresponding 'Description' (itemdesc) for the selected item from the 'Stock Analysis' worksheet. If you click anywhere on the chart area, apart from a data point, the vlookup in A1209 errors, so by trapping the error the macro continues to work (and also informs the user to only click on data points). This is covered much better in your later post.

I am not able to do anything much on this for a couple of days, as I have to go to Ireland, and my brain needs a rest, but will get into again when I am back later in the week.

Thanks again,

Richard

 

[SkipVought]

Sorry. I meant to go back and modify some of my former comments,

Here's a change to the mouse_down event...

'
        Select Case response1
            Case vbYes
                [b]ws.Activate
                Range("Item_Ref")(b).Select[/b]       'select appropriate item record
'                Call System_Macros.Enquiry5      '[b]took this out. Is it REALLY necessary?
'what needs to be updated/recalculated after simply browsing in a chart?
'BTW, after exercising the options numerous times, no crashes[/b]
            Case vbNo
                'do nothing -- stay on Cosmo Chart
            Case Else
                'go somewhere else???
        End Select

Enjoy yourself in the Emerald Isle. Might be a bit brisk by the Bonny Bay.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[RichardWale]

Hi Skip,

The trip to Ireland was good, but no time to see anything except airport and offices!

Have now had a bit of time to test out the revised code and as you say, no crashes, which is great.

'Call System_Macros.Enquiry5 'took this out. Is it REALLY necessary?'

Yes it is, it goes to the 'Item Enquiry' screen that shows all the details about the selected item and helps to explain why the item is in a particular place on the Cosmos chart. It can also help to guide what action is required to correct the existing stock profile for the item.

There is still one piece of behaviour which is odd. When the 'Yes' option is selected, it goes to the 'Enquiry' screen, and when returning to the Cosmos chart, either by selecting the worksheet directly, or via the 'Cosmos Chart' button, no data points are left highlighted, AND another point on the chart can be selected with a single mouse 'click', which is as it should be!

If 'No' is selected, the Cosmos chart stays open, but with one or multiple data points highlighted and to do anything with the mouse requires 2 separate clicks (not a double-click). This is not just to select another data point, but also the select another worksheet, click on one of the buttons on the chart or any action at all through the mouse.

If I add a 'chart refresh' by calling the System_Macros.Cosmos_Chart macro, it still has the same data points highlighted, but now only requires a single click to do anything with the mouse. BUT, it also crashes inconsistently!! So not a solution.

A niggle, but if there is a solution, that would be very welcome.

Hope you had a great Thanksgiving?

Thanks, Richard

 

[SkipVought]

Had a wonderful time with family. Thanks for asking.

'Call System_Macros.Enquiry5 'took this out. Is it REALLY necessary?'

Yes it is, it goes to the 'Item Enquiry' screen that shows all the details about the selected item and helps to explain why the item is in a particular place on the Cosmos chart. It can also help to guide what action is required to correct the existing stock profile for the item.

Well the reason I questioned the call to Enquiry5, is that it was obvious to me that the code first selects a cell in Cosmos Data. BUT if you execute Enquiry5, you go somewhere else??? So I guess that the cell gets selected, but you don't see that unless you go back to the Cosmos Data sheet. And I do see that you rely on this selection in other places in your code.

'
        Select Case response1
            Case vbYes
                ws.Activate
                Range("Item_Ref")(b).Select                                                 'select appropriate item record
                Call System_Macros.Enquiry5
            Case vbNo
                'do nothing -- stay on Cosmo Chart
                SendKeys "{ESC}"
            Case Else
                'go somewhere else???
        End Select

So how does this function?

I see that you have lots and LOTS of code. Viewing the Enquiry5 code, I see stuff that seems to be superfluous. Maybe could be streamlined to function better??? But then I don't know the built in things, like the cell selection in the Cosmos Data sheet. Thats where I'd put a value in the Formulae sheet, rather that relying on a selection on a sheet. But I also know that applications grow like Topsy. This one probably grew enormously over the years. Each new feature gets added, and often a redesign is prohibitive. You're not a professional programmer, but you've done a good enough job. Making some of these changes might have unintended consequences.

Anyhow, glad I could provide some assistance. Like I stated earlier, I have some background in supply chain management so I recognize some of this stuff functionally as well.

Good luck. Post back any time.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]