Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking textboxes with option boxes
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking textboxes with option boxes
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. Roger on Excel wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking textboxes with option boxes
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? -- Regards, OssieMac "Roger on Excel" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking textboxes with option boxes
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 "OssieMac" wrote: 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? -- Regards, OssieMac "Roger on Excel" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking textboxes with option boxes
Thanks Dave,
This works nicely Regards, Roger "Dave Peterson" wrote: 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. Roger on Excel wrote: 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding option boxes | New Users to Excel | |||
How do i create a value for check boxes or option boxes | Excel Discussion (Misc queries) | |||
Linking List Boxes and Textboxes | Excel Programming | |||
Problem with option boxes | Excel Programming | |||
Linking textboxes to cells with formulae | Excel Programming |