How can I link optionbutton and textbox based on selected from combobox
Solved/Closed
abdelfatah_0230
Posts
73
Registration date
Thursday July 18, 2019
Status
Member
Last seen
July 23, 2022
-
Updated on Oct 25, 2021 at 06:59 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Nov 4, 2021 at 11:47 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Nov 4, 2021 at 11:47 AM
Related:
- How to populate textbox based on combobox selection on userform in excel
- Number to words in excel - Guide
- How to take screenshot in excel - Guide
- How to change author in excel - Guide
- How to change date format in excel - Guide
- How to open excel in notepad - Guide
2 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Nov 2, 2021 at 12:42 PM
Nov 2, 2021 at 12:42 PM
Hi Abdel,
I think I checked all the boxes with the code below. There is one thing I'm not sure what you want:
You select sheet1 and enter "code" ffr-aa1 and the respective data is show in the list.
Now you select sheet2 with "code" ffr-aa1 still in the textbox1.
1. Do you want to show all the data from sheet2 and clear the textbox1 data or
2. Do you want to show the data from sheet2 filtered with "code" ffr-aa1 and leave ffr-aa1 in textbox1.
I went with option 2, since you can just clear the textbox1 to see all data.
Do let me know if you want to go with option 1 or if I missed something else.
Here is the code:
Best regards,
Trowa
I think I checked all the boxes with the code below. There is one thing I'm not sure what you want:
You select sheet1 and enter "code" ffr-aa1 and the respective data is show in the list.
Now you select sheet2 with "code" ffr-aa1 still in the textbox1.
1. Do you want to show all the data from sheet2 and clear the textbox1 data or
2. Do you want to show the data from sheet2 filtered with "code" ffr-aa1 and leave ffr-aa1 in textbox1.
I went with option 2, since you can just clear the textbox1 to see all data.
Do let me know if you want to go with option 1 or if I missed something else.
Here is the code:
Dim arr Private Sub ComboBox1_Change() Dim tVal As String If ComboBox1.Value = "SHEET1" Then With Sheets("SHEET1") Set rg = .Range("A2:H15") arr = rg End With With ListBox1 .ColumnCount = 8 .ColumnWidths = "80;120;80;80;80;80;80;80" .List = arr End With ElseIf ComboBox1.Value = "sheet2" Then With Sheets("sheet2") Set rg = .Range("A2:H15") arr = rg End With With ListBox1 .ColumnCount = 8 .ColumnWidths = "80;120;80;80;80;80;80;80" .List = arr End With End If tVal = TextBox1.Value TextBox1.Value = vbNullString TextBox1.Value = tVal End Sub Private Sub OptionButton1_Click() Dim i As Long Dim minVal As Long Dim rw() minVal = Rows.Count For i = 1 To UBound(arr) If arr(i, 7) < minVal And LCase(arr(i, 2)) = LCase(TextBox1.Value) Then minVal = arr(i, 7) Next i For i = 1 To UBound(arr) If arr(i, 7) = minVal And LCase(arr(i, 2)) = LCase(TextBox1.Value) Then ReDim Preserve rw(p) rw(p) = Application.Index(arr, i, 0) p = p + 1 End If Next i With ListBox1 If p > 1 Then .List = Application.Transpose(Application.Transpose(rw)) Else .Column = Application.Transpose(rw) End If End With End Sub Private Sub OptionButton2_Click() Dim i As Long Dim maxVal As Long Dim rw() maxVal = 0 For i = 1 To UBound(arr) If arr(i, 7) > maxVal And LCase(arr(i, 2)) = LCase(TextBox1.Value) Then maxVal = arr(i, 7) Next i For i = 1 To UBound(arr) If arr(i, 7) = maxVal And LCase(arr(i, 2)) = LCase(TextBox1.Value) Then ReDim Preserve rw(p) rw(p) = Application.Index(arr, i, 0) p = p + 1 End If Next i With ListBox1 If p > 1 Then .List = Application.Transpose(Application.Transpose(rw)) Else .Column = Application.Transpose(rw) End If End With End Sub Private Sub OptionButton3_Click() Dim tVal As String tVal = TextBox1.Value TextBox1.Value = vbNullString TextBox1.Value = tVal End Sub Private Sub textbox1_Change() Dim rw() For i = 1 To UBound(arr) If LCase(arr(i, 2)) Like "*" & LCase(TextBox1.Value) & "*" Then ReDim Preserve rw(p) rw(p) = Application.Index(arr, i, 0) p = p + 1 End If Next If p = 0 Then MsgBox "NO MATCH": Exit Sub With ListBox1 If p > 1 Then .List = Application.Transpose(Application.Transpose(rw)) Else .Column = Application.Transpose(rw) End If End With End Sub Private Sub UserForm_Activate() Dim WS As Worksheet For Each WS In Worksheets ComboBox1.AddItem (WS.Name) Next WS End Sub
Best regards,
Trowa
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Oct 25, 2021 at 12:10 PM
Oct 25, 2021 at 12:10 PM
Hi Abdel,
Could you upload your workbook for better understanding, use a site like wetransfer.com (always be careful with sensitive data) and post back the download link.
You write :
it should show data in listbox after fill the combobox ,textbox, select option ,otherwise the listbox is empty
Which contradicts:
select sheet from combobox(show all of data which contain sheet )
So which one is it?
With price high and price less you mean the max and min values, right?
What does the field reading CODE do?
How did you create the screenshots, when the results are not produced by code?
Best regards,
Trowa
Could you upload your workbook for better understanding, use a site like wetransfer.com (always be careful with sensitive data) and post back the download link.
You write :
it should show data in listbox after fill the combobox ,textbox, select option ,otherwise the listbox is empty
Which contradicts:
select sheet from combobox(show all of data which contain sheet )
So which one is it?
With price high and price less you mean the max and min values, right?
What does the field reading CODE do?
How did you create the screenshots, when the results are not produced by code?
Best regards,
Trowa
abdelfatah_0230
Posts
73
Registration date
Thursday July 18, 2019
Status
Member
Last seen
July 23, 2022
Updated on Oct 25, 2021 at 02:42 PM
Updated on Oct 25, 2021 at 02:42 PM
Hi Trowa
about this
select sheet from combobox(show all of data which contain sheet )
So which one is it?
it depends on which select the sheet . I have two sheets (sheet1,sheet2) as in the code in my file . each sheet contain data . if I select specific sheet should show data is relating for selected sheet from combobox
about this
With price high and price less you mean the max and min values, right?
yes
about this
What does the field reading CODE do?
based on column B and column 2 in lisbox as show in sheet
about this
How did you create the screenshots, when the results are not produced by code?
this imagination is in my mind . I know the code doesn't do that . and this is what I look for .
finally this is the file
https://www.dropbox.com/scl/fi/i99719pqxmh1v24mzr4qe/sss-1.xlsm?dl=0&rlkey=ma1su0ndlfwi7pwgr1ndcafzl
if it's not enough. you can ask me for provide more detailes
best regards,
abdelfatah
about this
select sheet from combobox(show all of data which contain sheet )
So which one is it?
it depends on which select the sheet . I have two sheets (sheet1,sheet2) as in the code in my file . each sheet contain data . if I select specific sheet should show data is relating for selected sheet from combobox
about this
With price high and price less you mean the max and min values, right?
yes
about this
What does the field reading CODE do?
based on column B and column 2 in lisbox as show in sheet
about this
How did you create the screenshots, when the results are not produced by code?
this imagination is in my mind . I know the code doesn't do that . and this is what I look for .
finally this is the file
https://www.dropbox.com/scl/fi/i99719pqxmh1v24mzr4qe/sss-1.xlsm?dl=0&rlkey=ma1su0ndlfwi7pwgr1ndcafzl
if it's not enough. you can ask me for provide more detailes
best regards,
abdelfatah
Updated on Nov 3, 2021 at 05:03 AM
magnificant ! about this I went with option 2, since you can just clear the textbox1 to see all data. it's ok .
every things works as what I want .I no know how thank you . actually nobody answers me for my question for many tries in many forums . I know this is not easy to follow the code line by line to understand how should work and how should mod . you achieved a great work .
many thanks for your help .
have a nice day !
Nov 4, 2021 at 11:47 AM