Custom Worksheet Function doesn't update

[jeffersii]

Hi all-

Relatively new to Excel. Searched the forums but couldn't find this issue:

I wrote a custom function in VBA that loops through all the worksheets in the workbook and puts each one's name into a array. (Similar to the "Table of Contents" problem on another thread).

I then paste this custom function into a bunch of cells, Ctrl-Shift-Enter, and it nicely displays each worksheet's name in the consecutive cells.

My problem - and this is an issue of just generally understanding how these VBA functions work - is that when I change one of the worksheet names manually, the cell with that sheet's name doesn't update. However, if I re-paste the function into a bunch of cells and Ctrl-Shift-Enter again, it will show the new worksheet name (while the old worksheet name is still in the other set of cells).

My question: why doesn't this auto-recalculate like most Excel functions do (automatic calc is on in the Options, BTW), and how can I make it do it? Code below, using Excel 2000 (my company hasn't upgraded):

Please Login or Register  to view this content.

Thanks for any wisdom!!

[DonkeyOte]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here


Also see: http://www.decisionmodels.com/calcsecretsj.htm

On an aside you may want to revise the approach from a Function to a Workbook orientated event...

[jeffersii]

Oops, thanks Donkey. I corrected.