Duplicate what is in the Textbox in other Textboxes on other sheets

Question

I have four sheets in a spreadsheet.

On each sheet there is a textbox.

When I type in the textbox on sheet1, I want the textboxes on sheet2, sheet3, and sheet4 to populate with the same value.

Answer 1

Do you want the other textboxes to be editable, or just always show the contents of the first? What happens to the other textboxes when a user edits the 2nd, 3rd, and so on (in the first case)? These questions will affect the solution... But if you just want the boxes to mimic the first, then use SheetName!CellAddress like this:

=WorksheetName!A1

Just substitute the name of the worksheet and the appropriate cell.

Answer 2

Create a named range somewhere in your workbook, then type that name into the textbox linkedcell property.

Answer 3

Here's one way: Set the LinkedCell property of the first textbox to A1. Set the LinkedCell property of every other textbox to A1 of its own sheet. Then in sheets 2-4, cell A1, put

=Sheet1!A1

Now whatever you type in the textbox on Sheet1 will be in the textboxes on sheets 2-4. If you type anything in the textboxes in 2-4, you'll break the link. But from your question, it looks like you only want to type in the first one.

Here's another way.

Private Sub TextBox1_Change()

    Dim ws As Worksheet

    For Each ws In Me.Parent.Worksheets
        ws.OLEObjects("TextBox1").Object.Text = Me.TextBox1.Text
    Next ws

End Sub

Put that in the class module for the sheet. It assumes all your textboxes are named TextBox1. Whenever you change the one on Sheet1, the ones on the other sheet get changed.

Answer 4

Here is another way:

Create a module, and place this code there. This needs to be configured for your own specific textbox names and sheet names

Public Sub UpdateTextBoxes(str As String)

    Sheets("sheet1").TextBox1.Text = str
    Sheets("sheet2").TextBox1.Text = str
    Sheets("sheet3").TextBox1.Text = str
End Sub

Then for each textbox object you wish to replicate, use this for its change event (Sheet Class Module) (Change the paramaters to suite your needs)

Private Sub TextBox1_Change()
    UpdateTextBoxes Me.TextBox1.Text
End Sub

This way you can name your controls however you see fit, and you can update the text from any of the textboxes, and they will always be consistant.