update VBA code when copying sheets

[Eat More Bacon]

I have a textbox that I want to display the info in B42.

B42 contains a textjoin formula, which means it changes when I input info in another cell. If I link the textbox to b42, it does not automatically update. Not sure why.

So I copied this neat macro and assigned it to a button, so whenever I want to update the textbox, I click on the button and run this macro.

>>> use code - tags <<<

Sub Do_It()

With ActiveSheet

.Shapes("TextBox 2").TextFrame.Characters.Text = .Range("B42")

End With

End Sub

Now my problem is that when I copy the sheet, the copied sheet textbox is named "textbox 9" and the macro doesn't work.
What are my options?
Thanks!

 

[vletm]

Eat More Bacon
You could change that TextBox 2 name.

 

[Eat More Bacon]

Is that my best option? change the VBA code each time I make a copy of the sheet?

 

[vletm]

Eat More Bacon
Please, reread #2 again
... You could change that TextBox 2 name eg to Eat More Vegetables
no need to modify code.
Ps. You seems to skip that my solution to Your previous thread.

 

[Eat More Bacon]

Oh! Sorry, I did misunderstand. Thanks!

 

[p45cal]

If this is a textbox, you should be able to select it, then in the formula bar enter the formula:
=$B$42
then
1 It should update on every change of that cell
2. It doesn't matter what the text box is called, it will copy properly
3. so no vba code necessary

 

[vletm]

p45cal
=$B$42 works,
.... but cell B42's value is longer than 255 characters.

 

[Eat More Bacon]

Yes, cell b42 value is longer than 255 characters.
Also, If I link a text box to a cell, it seems like I cannot do any formatting to it?