Linking textboxes with option boxes

[Roger on Excel]

I have a userform with a column of text boxes (TextBox1, 2, 3...10) and
adjacent Check boxes (CheckBox1, 2, 3...10).

The textboxes are populated from a sheet with various items.

What I need is for the adjacent Check boxes to be checked if the textboxes
contain either the text AAAAA or BBBBB and unchecked for anything else.

Can anyone help?

Many thanks,

Roger

 

[Dave Peterson]

And contains means that the textboxes can contain: xxxxxAAAAAyyyyy
and still be checked, right?

I only used 3 textboxes/checkboxes in this sample. You'll have to add 7 more
_Change procedures:

Option Explicit
Private Sub TextBox1_Change()
Call CheckTBoxCbx(WhichOne:=1)
End Sub
Private Sub TextBox2_Change()
Call CheckTBoxCbx(WhichOne:=2)
End Sub
Private Sub TextBox3_Change()
Call CheckTBoxCbx(WhichOne:=3)
End Sub
Sub CheckTBoxCbx(WhichOne As Long)

Dim myStrings As Variant
Dim sCtr As Long
Dim TboxValue As String
Dim OnOrOff As Boolean

myStrings = Array("aaaaa", "bbbbb")

TboxValue = Me.Controls("Textbox" & WhichOne).Value

OnOrOff = False
For sCtr = LBound(myStrings) To UBound(myStrings)
If InStr(1, Me.Controls("Textbox" & WhichOne).Value, _
myStrings(sCtr), vbTextCompare) > 0 Then
'found one
OnOrOff = True
Exit For 'stop looking
End If
Next sCtr

Me.Controls("Checkbox" & WhichOne).Value = OnOrOff

End Sub

Remember to change the _change event name and pass the correct number.

 

[OssieMac]

Hi Roger,

"The textboxes are populated from a sheet with various items." Does this
mean they are populated with code?

Do you want the checkboxes updated as each textbox is updated or can they be
done in bulk like after a particular textbox is updated or with a button etc?

 

[Roger on Excel]

Hi Ossie,

Yes, I use code to update the text boxes. It would be nice to have the
checkboxes respond to the textbox changes in the userform


Dave Peterson below has a nice solution for me that does update

Many thanks,

Roger

 

[Roger on Excel]

Thanks Dave,

This works nicely

Regards,

Roger

And contains means that the textboxes can contain: xxxxxAAAAAyyyyy
and still be checked, right?

I only used 3 textboxes/checkboxes in this sample. You'll have to add 7 more
_Change procedures:

Option Explicit
Private Sub TextBox1_Change()
Call CheckTBoxCbx(WhichOne:=1)
End Sub
Private Sub TextBox2_Change()
Call CheckTBoxCbx(WhichOne:=2)
End Sub
Private Sub TextBox3_Change()
Call CheckTBoxCbx(WhichOne:=3)
End Sub
Sub CheckTBoxCbx(WhichOne As Long)

Dim myStrings As Variant
Dim sCtr As Long
Dim TboxValue As String
Dim OnOrOff As Boolean

myStrings = Array("aaaaa", "bbbbb")

TboxValue = Me.Controls("Textbox" & WhichOne).Value

OnOrOff = False
For sCtr = LBound(myStrings) To UBound(myStrings)
If InStr(1, Me.Controls("Textbox" & WhichOne).Value, _
myStrings(sCtr), vbTextCompare) > 0 Then
'found one
OnOrOff = True
Exit For 'stop looking
End If
Next sCtr

Me.Controls("Checkbox" & WhichOne).Value = OnOrOff

End Sub

Remember to change the _change event name and pass the correct number.