Microsoft Excel - Worksheet UDF not updating - Asked By John Wirth on 16-Nov-12 07:39 AM
I have written a UDF to show the text of a cell comment. It works fine when I enter the UDF into a cell, but when I change the cell comment, the returned value from the function does not update. If I click into the cell containing the UDF and 're-enter' by hitting return, it then updates. I need it to uodate whenever the comment is changed/ added/ deleted.
If Not Rng1.COMMENT Is Nothing Then
Cmnt = Rng1.COMMENT.Text
Else: Cmnt = ""
John D replied to John Wirth on 18-Nov-12 08:48 AM
The only way to do it is to add an event macro that will trigger recalculation.
But I don't think it's a good idea because you will have this macro running every time you click on a cell.
I don't know what you have in that Workbook but if it's full of formula, it's going to be a nightmare.
It's your choice.
John D replied to John Wirth on 18-Nov-12 02:13 PM
If it don't work just add Application.Volatile to your UDF, first line
John Wirth replied to John D on 20-Nov-12 04:56 AM
Thanks, I tried that but it makes no difference.
John D replied to John Wirth on 20-Nov-12 08:19 AM
I'm using XL03 and with a sample file 4 comments, it works fine.
Obviously I don't have anything else on that sheet, that could be the difference.
I could post a link to the file if you're interested.
John Wirth replied to John D on 26-Nov-12 03:07 AM
That would be great of you could. I am using calculate, but I would prefer to use Application.Volatile instead.
John D replied to John Wirth on 26-Nov-12 02:06 PM
See link below, it's just a sample file with 4 comment cell.
It's not stable with just volatile.