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.

 Function Cmnt(Rng1)


If Not Rng1.COMMENT Is Nothing Then
Cmnt = Rng1.COMMENT.Text
Else: Cmnt = ""
End If


End Function
John D replied to John Wirth on 18-Nov-12 08:48 AM
Hi
The only way to do it is to add an event macro that will trigger recalculation.
 PrivateSubWorksheet_SelectionChangeByValTargetAsRange)
Calculate
End Sub

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
Hi
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.
http://cjoint.com/?3KAudPwbMrj
It's not stable with just volatile.