TextBox control stops displaying linked cell content
 

Using Excel 2000

I have created a report sheet that must be filled in on a monthly
basis. There is one column per month. One of the cell is a comment box.
As columns will be hidden/displayed at each month change and for
presentation purposes, I have added a textbox control on the sheet (
not moved nor resized ).

The linked cell reference of the textbox comes from a dynamic name that
returns the reference of comment cell of the reporting month.

I have created the following local named range
comment = OFFSET('Sheet1'!$A$1,0,'Sheet1'!mth_offset)

In the linked cell property of the text box
linked cell = 'Sheet1'!comment

mth_offset is another local named range linked to a spinbutton. As I
spin up and down, the text box correctly displays the content of the
cells in row 1, as you would expect from the 'comment' formula.

The strange thing is that if as soon as I edit the content of the text
box, the dynamic link stops working, i.e. the textbox no longer updates
its content as I spin up/.down.

I have found a workaround: in the spin button change event procedure, I
add the code: "TextBox1.LinkedCell = TextBox1.LinkedCell". With that
the text box updates correctly.

Has anyone faced this problem ?
Is there a workaround that does not rely on code ? - getting users to
activate macros can be a pain.

Thanks for your help.
Regards