In VBA, how can I tell when a chart series point becomes no longer selected

%3CLINGO-SUB%20id%3D%22lingo-sub-2356519%22%20slang%3D%22en-US%22%3EIn%20VBA%2C%20how%20can%20I%20tell%20when%20a%20chart%20series%20point%20becomes%20no%20longer%20selected%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2356519%22%20slang%3D%22en-US%22%3E%3CP%3EWhen%20the%20user%20selects%20a%20point%20in%20a%20chart%20series%20(it's%20a%20scatter%20plot)%2C%20it%20triggers%20an%20EventChart_Select%20event.%20In%20the%20event%20code%2C%20I%20change%20the%20appearance%20of%20the%20point%20so%20if%20the%20user%20looks%20away%20from%20the%20screen%20and%20looks%20back%20again%2C%20they%20can%20still%20see%20the%20point%20they%20have%20selected.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20the%20point%20is%20no%20longer%20selected%2C%20I%20want%20to%20change%20its%20appearance%20back%20to%20%22normal.%22%20Unfortunately%2C%20there%20is%20no%20single%20event%20that%20I'm%20aware%20of%20(I'm%20relatively%20new%20to%20VBA)%20where%20this%20can%20be%20done.%26nbsp%3B%20If%20the%20selection%20changes%20because%20a%20cell%20on%20the%20worksheet%20selected%2C%20it%20triggers%20a%20Worksheet_SelectionChange%20event.%26nbsp%3B%20If%20the%20selection%20changes%20because%20something%20else%20on%20the%20chart%20is%20selected%2C%20it%20triggers%20another%20EventChart_Select%20event.%26nbsp%3B%20So%20if%20I%20want%20to%20change%20the%20appearance%20of%20the%20point%2C%20I%20need%20to%20add%20code%20in%20both%20places.%20It%20feels%20like%20there%20should%20be%20a%20better%20way.%26nbsp%3B%20Is%20there%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2356519%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2356810%22%20slang%3D%22en-US%22%3ERe%3A%20In%20VBA%2C%20how%20can%20I%20tell%20when%20a%20chart%20series%20point%20becomes%20no%20longer%20selected%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2356810%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F382386%22%20target%3D%22_blank%22%3E%40perkin_warbeck%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20could%20store%20the%20most%20recently%20highlighted%20point%20in%20a%20public%20variable%2C%20and%20create%20a%20%22reset%22%20procedure%20that%20resets%20this%20point.%20You%20can%20then%20call%20this%20from%20the%20Worksheet_SelectionChange%20event%20procedure%20and%20from%20the%20Chart_Select%20event%20procedure%2C%20in%20the%20latter%20case%20before%20highlighting%20a%20new%20point%20if%20applicable.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2357559%22%20slang%3D%22en-US%22%3ERe%3A%20In%20VBA%2C%20how%20can%20I%20tell%20when%20a%20chart%20series%20point%20becomes%20no%20longer%20selected%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2357559%22%20slang%3D%22en-US%22%3EThe%20problem%20is%20that%20there%20is%20no%20single%20event%20for%20a%20selection%20change.%20There%20are%20two%2C%20one%20for%20chart%20and%20one%20for%20the%20sheet.%20Which%20means%20the%20reset%20procedure%20must%20be%20called%20from%20two%20different%20places.%20I%20stopped%20using%20global%20variables%20in%20event-driven%20code%20because%20I'm%20not%20experienced%20enough%20to%20know%20how%20to%20handle%20initialization%20in%20an%20event-driven%20application.%20So%20I%20would%20probably%20have%20reset%20search%20the%20series.%20That%20was%20my%20original%20%22solution%22%20but%20I%20don't%20like%20it.%20It's%20ugly.%3C%2FLINGO-BODY%3E
Contributor

When the user selects a point in a chart series (it's a scatter plot), it triggers an EventChart_Select event. In the event code, I change the appearance of the point so if the user looks away from the screen and looks back again, they can still see the point they have selected.

 

When the point is no longer selected, I want to change its appearance back to "normal." Unfortunately, there is no single event that I'm aware of (I'm relatively new to VBA) where this can be done.  If the selection changes because a cell on the worksheet selected, it triggers a Worksheet_SelectionChange event.  If the selection changes because something else on the chart is selected, it triggers another EventChart_Select event.  So if I want to change the appearance of the point, I need to add code in both places. It feels like there should be a better way.  Is there? 

2 Replies

@perkin_warbeck 

You could store the most recently highlighted point in a public variable, and create a "reset" procedure that resets this point. You can then call this from the Worksheet_SelectionChange event procedure and from the Chart_Select event procedure, in the latter case before highlighting a new point if applicable.

The problem is that there is no single event for a selection change. There are two, one for chart and one for the sheet. Which means the reset procedure must be called from two different places. I stopped using global variables in event-driven code because I'm not experienced enough to know how to handle initialization in an event-driven application. So I would probably have reset search the series. That was my original "solution" but I don't like it. It's ugly.