Run code each time form is opened

It should be UserForm_Activate, not formname_Activate.

Public Sub UserForm_Activate() 
Dim celltxt As String

    celltxt = ActiveSheet.Range("E9").Value

    If InStr(celltxt, "SSS1B") Then
          CheckBox1.Value = True
    Else
        CheckBox1.Value = False
    End If
    
End Sub