PDA

View Full Version : Textbox search conditionally



Tom Jones
11-28-2017, 03:17 AM
Howdy,

I saw a video on NET with a userform where there were a textbox (for word search) more optionbutton and a listbox, where some data are listed, from a database.
In a frame there were 2 optionbutton from which it could be selected if the search was made after the first letter or after any letter in the cell.
Above the listbox there were other optionbuttons next to each column in the listbox. There were header columns in the database in sheet2 (the button from where user was called is in sheet1).
When writing a letter, in the textbox, search was done by the optionbutton of frame1 and only in the column where the optionbutton was checked.
When selecting any other optionbutton, the data selection is restored.
Please someone can help me with such a code?
Thank you in advance.

p45cal
11-28-2017, 11:57 AM
I saw a video on NET with a userform where The link to that video would be very useful.

Tom Jones
11-28-2017, 12:04 PM
The link to that video would be very useful.

I tried to remember where I saw that video, but I did not succeed although I searched for days on youtube and on some sites but I did not find it.
When I saw him, I did not think I would ever need that code, especially because he did not give the code in that presentation.
Could you help me with a VBA code?
Thank you.

p45cal
11-28-2017, 01:16 PM
If you can't find the video then you need to specify what you want doing, supplying file(s)/workbook(s) with sample data and as much as you can to help us help you. I'm not going to spend hours coding for what you might want. The project sounds complicated - it may be I/we only have time to give you pointers/snippets of code.

Tom Jones
11-28-2017, 02:27 PM
Help me with this and if I can't solve then I will attach a file. Thanks.

How can I combine this:

If OptionButton4 = True And OptionButton1 = True Then kCol = 1
If OptionButton4 = True And OptionButton2 = True Then kCol = 2
If OptionButton4 = True And OptionButton3 = True Then kCol = 3
db.Range.AutoFilter Field:=kCol, Criteria1:=tbSearch & "*"

with this:

If OptionButton5 = True And OptionButton1 = True Then kCol = 1
If OptionButton5 = True And OptionButton2 = True Then kCol = 2
If OptionButton5 = True And OptionButton3 = True Then kCol = 3
db.Range.AutoFilter Field:=kCol, Criteria1:="*" & tbSearch & "*"

p45cal
11-28-2017, 03:06 PM
guessing at the option button grouping, try:
Select Case True
Case OptionButton4: mySearch = tbSearch & "*"
Case OptionButton5: mySearch = "*" & tbSearch & "*"
End Select
Select Case True
Case OptionButton1: kCol = 1
Case OptionButton2: kCol = 2
Case OptionButton3: kCol = 3
End Select
Db.Range.AutoFilter Field:=kCol, Criteria1:=mySearch

Tom Jones
11-28-2017, 03:50 PM
Thank you so much.
How can I refresh searching if I change selection of optionbutton?

p45cal
11-29-2017, 04:36 AM
There are a number of option button event handlers that you could use, amongst them:
OptionButton_Change()
OptionButton_Click()
OptionButton_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
OptionButton_LostFocus()
Which one is best (probably Change) depends on your setup - I can't advise definitively working in the dark.
When there are only 2 option buttons in a group only one of them needs an event handler. When there are more than 2 in a group, they all need event handlers. The event handlers might all call the same Sub, to update the search criteria and do the search.

Tom Jones
11-30-2017, 01:01 PM
Thank you, p45cal.
Very helpful.