How to sync All Textbox1 in Workbook?

Question

My workbook has five sheets with text boxes. Sheet1, Sheet2, ......, and Sheet5.

And the code below is on every sheet.

Private Sub TextBox1_Change()

If Len(TextBox1.Value) = 0 Then
ActiveSheet.AutoFilterMode = False

Else
  If ActiveSheet.AutoFilterMode = True Then
  ActiveSheet.AutoFilterMode = False

End If

ActiveSheet.Range("A2:C" & Rows.Count).AutoFilter field:=1, Criteria1:="*" & TextBox1.Value & "*"
End If

End Sub

I would like to synchronize the TEXTBOXES on all sheets.

For example, if I type text in TEXTBOX1 of Sheet1, the same text will be entered in TEXTBOX1 of all other sheets.

And I also want to know how to clear TEXTBOX on every sheet at once.

Answer 1

1) put the following code in new Module of your project

Public dontDoThat As Boolean ' a public variable, visible throughout all your project you'll use to give way to synchronizing activity

Option Explicit

Sub Synchronize(txt As String, shtName As String)
    dontDoThat = True ' set your public variable to True and prevent subsequent TextBox1_Change() events to run it again

    Dim sht As Variant
    For Each sht In Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5")
        If sht <> shtName Then Worksheets(sht).TextBox1.Text = txt 
    Next

    dontDoThat = False ' set your public variable to False and allow subsequent TextBox1_Change() events to run it 
End Sub

2) change your TextBox1_Change() event in all your sheets as follows

Private Sub TextBox1_Change()
    If Not dontDoThat Then Synchronize Me.TextBox1.Text, Me.Name 'call Synchronize() only if your public variable allows it to

    ...
    (rest of your previous code follows)
    ...

End Sub

To clear all textboxes just clear one of them