Trigger a macro or worksheet event with a UDF?

Attached is an interactive chart I’m working on – to change the chart on the “Interactive Dashboard” sheet, all you have to do is mouse over any small triangle on row 6 between Col B – G. I have 9 stocks assigned to five different categories – the category count is displayed on row four.

My problem is that while Col E actually only should have two diamonds on the chart, the chart is plotting the first 5 lines on the horizontal access. I can solve this problem by filtering Col H on the “Graph” worksheet for blanks. I have a worksheet_change macro to do just that – if you put your cursor in cell H2 of the "Graph" worksheet and hit enter, you can see that macro works.

My question is how can I trigger those two lines of code from the UDF? I’ve tried adding those lines to the UDF, Calling them from the UDF and even using the worksheet_change event. None of those work – even though the UDF changes cell H2, the worksheet_change event does not fire.

Does anyone have any ideas? Appreciate any suggestions. Thanks!
aquixano

Hi there,

I haven't yet looked at your workbook, but as far as I know, the ONLY thing that a UDF can do is to change the value of the cell in which it is entered - it can affect nothing other than that

Regards,

Greg M

Thanks Greg. The UDF does change the value of a cell in the workbook - do you have any idea why that doesn't trigger the Worksheet_Change Event?

Hi again,

The Worksheet_Change event is triggered when a new value is ENTERED in a cell, but not when the cell value changes as a result of worksheet recalculation.

Regards,

Greg M

Thanks for the suggestions. I’m running Excel 2010 and from what I’ve been able to find, the calculate event isn’t available before 2013.

Greg – Are you making a distinction with “Entered” that it has to be keyboard input (vs. changing the cell value thorough a macro)? The UDF is changing the cell value (not just recalculating it) – the same action I’ve used in Excel macros that trigger worksheet change events. Perhaps UDFs disable Events (alternatively I wonder if I need to specify an attribute to allow Events when creating the UDF). I’ll keep searching – appreciate any other thoughts.

@humdingaling – very nice! On first glance it looks like your approach works – I need to spend a little more time testing to make sure the change didn’t affect anything unintentionally, but thank you for your effort. Also a new trick for me – I don’t use charts often and didn’t know about the NA() trick.

@MrShorty – the link you provided re: Worksheet.Calculate says that it’s for 2013 and later and under “other versions” nothing is listed. I checked a couple other sites and didn’t find anything that worked with 2010 – you’re right of course, subsequently to my post I did find some sample code for the worksheet.calculate that worked in 2010. My exposure to the worksheet events is somewhat new so I really don’t know how much there is beyond selectionChange/Change/follow hyperlink & now calculate.

Thanks to both of you for your assistance.