change showing data on form from sheet to multiple sheets

Hi again Halk

Correction 06 April 2024

This doesn't rely on activating the selected sheet but clears and writes to the ComboBox list instead.

In the revised attached file, the code behind ComboBox2 shows how your code could be modified to change the ID control ComboBox1 :

Private Sub ComboBox2_Change()
    If Me.ComboBox2.Value = "" Then Exit Sub
    'clear existing list and entry
    ComboBox1.RowSource = ""
    ComboBox1.Text = ""
    With Worksheets(Me.ComboBox2.Value)
        'write new list
        Me.ComboBox1.List = Application.Transpose(.Range(.Range("B2"), .Range("B2").End(xlDown)))
    End With
End Sub

(Note: The user's sheets all had IDs with the prefix FLS but this is replaced by the second letter of the sheet name for illustration purposes e.g. sheet STW uses prefix TLS)

Revision 09 April 2024

In addition to illustration above, the attached file now brings back the required data. It does so by the simple addition in bold below when an ID in CombBox 2 is selected:

Private Sub ComboBox1_Change()
Dim c As Range


    Set c = Worksheets(Me.ComboBox2.Value).Range("B:B").Find(What:=ComboBox1.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not c Is Nothing Then
        TextBox1.Value = c.Offset(, 2).Text
        TextBox2.Value = c.Offset(, 3).Text
        TextBox3.Value = c.Offset(, 4).Text


    End If

End Sub

Hope this helps.