Fernando
asked on
Tweaking search for lazy users! :) (Excel VB search)
Hi there,
Here is the code I am using. Provided by an absolute expert here at EE!
========================== ========== =======
...Add this to one of your modules
Public Sub GetSearchItem(Prod As String, Optional ProdID As Boolean = False)
Dim sh As Worksheet
Dim bItemFound As Boolean
Application.ScreenUpdating = False
For Each sh In Worksheets
If DisplaySearchItem(Prod, sh, ProdID) Then
bItemFound = True
Exit For
Else
bItemFound = False
End If
Next sh
If bItemFound <> True Then
MsgBox "Item " & Prod & " not found"
End If
Application.ScreenUpdating = True
End Sub
Public Function DisplaySearchItem(Prod As String, aSheet As Worksheet, Optional ProdID As Boolean = False) As Boolean
Dim c As Object
Dim c2 As Object
aSheet.Select
Range("A1").Select
Set c = Cells.Find(What:=Prod, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole)
If c Is Nothing Then
DisplaySearchItem = False
Else
c.Activate
Selection.AutoFilter Field:=c.Column, Criteria1:="=" & Prod
DisplaySearchItem = True
End If
End Function
========================== ========== ======
In the Welcome sheet (Main sheet) add two textboxes (textbox1 for Product Type Code entry and TextBox2 for Product ID entry)
Add A Command Button for user to click o do the search. Now add the following code for the command button
Private Sub CommandButton9_Click()
If TextBox2.Text <> "" Then
GetSearchItem TextBox2.Text, True
Else
If TextBox1.Text <> "" Then
GetSearchItem TextBox1.Text
End If
End If
End Sub
========================== ========== =======
Please see the following for more details:
https://www.experts-exchange.com/questions/21396852/Ability-to-search-a-workbook-for-a-product-VB-Code.html#13867053
========================== ========== =======
Here is the code I am using. Provided by an absolute expert here at EE!
==========================
...Add this to one of your modules
Public Sub GetSearchItem(Prod As String, Optional ProdID As Boolean = False)
Dim sh As Worksheet
Dim bItemFound As Boolean
Application.ScreenUpdating
For Each sh In Worksheets
If DisplaySearchItem(Prod, sh, ProdID) Then
bItemFound = True
Exit For
Else
bItemFound = False
End If
Next sh
If bItemFound <> True Then
MsgBox "Item " & Prod & " not found"
End If
Application.ScreenUpdating
End Sub
Public Function DisplaySearchItem(Prod As String, aSheet As Worksheet, Optional ProdID As Boolean = False) As Boolean
Dim c As Object
Dim c2 As Object
aSheet.Select
Range("A1").Select
Set c = Cells.Find(What:=Prod, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole)
If c Is Nothing Then
DisplaySearchItem = False
Else
c.Activate
Selection.AutoFilter Field:=c.Column, Criteria1:="=" & Prod
DisplaySearchItem = True
End If
End Function
==========================
In the Welcome sheet (Main sheet) add two textboxes (textbox1 for Product Type Code entry and TextBox2 for Product ID entry)
Add A Command Button for user to click o do the search. Now add the following code for the command button
Private Sub CommandButton9_Click()
If TextBox2.Text <> "" Then
GetSearchItem TextBox2.Text, True
Else
If TextBox1.Text <> "" Then
GetSearchItem TextBox1.Text
End If
End If
End Sub
==========================
Please see the following for more details:
https://www.experts-exchange.com/questions/21396852/Ability-to-search-a-workbook-for-a-product-VB-Code.html#13867053
==========================
i am baffled as well
ASKER
AND NOW FOR THE QUESTION...
Can I set the text fields so that as soon as a number is typed in either of the fields it will search after hitting "Enter" on the keyboard? (My users are very lazy!)
Also, I had a user sit down and test it for a minute and realised that with the product ID's, they generally know the exact number except for the last character. It works fine if we add '*' at the end of the number which is perfectly acceptable but I would asume we could somehow set it so that if 6 numerics are entered, to autoimatically add the wild-card at the end...
There are 2 types of Product ID formats, both have 7 characters. (1) '111111X' & 'AU11111' where X = any character from the alphabet, 1 = any number 0-9 & AU = AU, Ie; will stay the same (AU abreviattion for Australia)
If a search for an AU ID is made, the AU will always be entered but would be a bonus to include this "rule" but not necassary.
Any help is always appreciated!
Can I set the text fields so that as soon as a number is typed in either of the fields it will search after hitting "Enter" on the keyboard? (My users are very lazy!)
Also, I had a user sit down and test it for a minute and realised that with the product ID's, they generally know the exact number except for the last character. It works fine if we add '*' at the end of the number which is perfectly acceptable but I would asume we could somehow set it so that if 6 numerics are entered, to autoimatically add the wild-card at the end...
There are 2 types of Product ID formats, both have 7 characters. (1) '111111X' & 'AU11111' where X = any character from the alphabet, 1 = any number 0-9 & AU = AU, Ie; will stay the same (AU abreviattion for Australia)
If a search for an AU ID is made, the AU will always be entered but would be a bonus to include this "rule" but not necassary.
Any help is always appreciated!
ASKER
LOL... Geez you guys are fast... didn't even have time to ask the question! I figured I would post twice due to the length...
:)
:)
For your first question:
Private Sub TextBox2_KeyPress(KeyAscii As Integer)
If KeyAscii = 13 Then
'Do whatever you need to do here...
End If
End Sub
I need to have some more time to do the rest.. ;) Work comes first although this is a nice hobby...
Regards,
Max.
Private Sub TextBox2_KeyPress(KeyAscii
If KeyAscii = 13 Then
'Do whatever you need to do here...
End If
End Sub
I need to have some more time to do the rest.. ;) Work comes first although this is a nice hobby...
Regards,
Max.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you dmang, the code suggested works perfectly! I will reward you the points once I get the other half of this question...
DreamMaster, I tried to add your code but was not successful. Where do I add this? Before, after or somewhere within this code?
Private Sub CommandButton9_Click()
If TextBox2.Text <> "" Then
GetSearchItem TextBox2.Text, True
Else
If TextBox1.Text <> "" Then
GetSearchItem TextBox1.Text
End If
End If
End Sub
I would like to keep the button there also... Some users are keyboard users... and other are strictly mouse users if you kow what i mean...
Thank you... (I would love to be able to do what you do as a hobby as well... I have a VB book that I am starting to read but just don't have the time... I need to start somewhere... thank god for EE!)
DreamMaster, I tried to add your code but was not successful. Where do I add this? Before, after or somewhere within this code?
Private Sub CommandButton9_Click()
If TextBox2.Text <> "" Then
GetSearchItem TextBox2.Text, True
Else
If TextBox1.Text <> "" Then
GetSearchItem TextBox1.Text
End If
End If
End Sub
I would like to keep the button there also... Some users are keyboard users... and other are strictly mouse users if you kow what i mean...
Thank you... (I would love to be able to do what you do as a hobby as well... I have a VB book that I am starting to read but just don't have the time... I need to start somewhere... thank god for EE!)
burkertadmin..
The code I gave you is an event of the Textbox2 element. It will be triggered whenever a key is pressed in it...and when you hit ENTER the part "Do Whatever..." is executed.
So you could do the check for your item from there as an extra feature.
Private Sub TextBox2_KeyPress(KeyAscii As Integer)
If KeyAscii = 13 Then
If TextBox2.Text <> "" Then
GetSearchItem TextBox2.Text, True
End if
End If
End Sub
Regards,
Max.
The code I gave you is an event of the Textbox2 element. It will be triggered whenever a key is pressed in it...and when you hit ENTER the part "Do Whatever..." is executed.
So you could do the check for your item from there as an extra feature.
Private Sub TextBox2_KeyPress(KeyAscii
If KeyAscii = 13 Then
If TextBox2.Text <> "" Then
GetSearchItem TextBox2.Text, True
End if
End If
End Sub
Regards,
Max.
ASKER
When I ad either of those scripts you mentioned I get the following error:
"Compile Error: Procedure declaration does not match description of event or procedure having the same name"
What am I doing wrong?
"Compile Error: Procedure declaration does not match description of event or procedure having the same name"
What am I doing wrong?
Try in your code to on the leftdropdown select TextBox2 and on the right the event KeyPress, should be there...
Unless your TextBox2 is in fact not named that way.. ;)
Regards,
Max.
Unless your TextBox2 is in fact not named that way.. ;)
Regards,
Max.
ASKER
This is what is returned when I select as you stated:
Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
End Sub
If I add the code as follows:
Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii = 13 Then
If TextBox2.Text <> "" Then
GetSearchItem TextBox2.Text, True
End if
End If
End Sub
Nothing happens... No errors... No nothing...
:(
Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
End Sub
If I add the code as follows:
Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii = 13 Then
If TextBox2.Text <> "" Then
GetSearchItem TextBox2.Text, True
End if
End If
End Sub
Nothing happens... No errors... No nothing...
:(
Ok, lets step back a little...
Comment out the part from... If TextBox2.Text <> "" Then to End If
Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Debug.Print "[" + KeyAscii + "]"
If KeyAscii = 13 Then
Debug.Print "Enter key was pressed"
End If
End Sub
This should put in your immediate window what key was pressed and then if KeyAscii = 13 it should show the text "Enter key was pressed"
Regards,
Max.
Comment out the part from... If TextBox2.Text <> "" Then to End If
Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Debug.Print "[" + KeyAscii + "]"
If KeyAscii = 13 Then
Debug.Print "Enter key was pressed"
End If
End Sub
This should put in your immediate window what key was pressed and then if KeyAscii = 13 it should show the text "Enter key was pressed"
Regards,
Max.
ASKER
We will have to continue tomorrow. I didn't have any luck with that one either.
Thank you for your time. I will try any other suggestions tomorrow.
Cheers
Thank you for your time. I will try any other suggestions tomorrow.
Cheers
Nothing was shown in your Immediate window??
That kinda baffles me...
Regards,
Max.
That kinda baffles me...
Regards,
Max.
ASKER
Half of this question was answered by dmang. Thankyou!
THe second half I managed to figure out that KeyDwon was the function I should be using but... I am having problems where by it will crash in Excel 2002 but works fine in Excel 2003.
Please see https://www.experts-exchange.com/questions/21430917/Excel-2002-vs-2003-Error-with-workbook-when-using-2002.html for more details
Cheers
THe second half I managed to figure out that KeyDwon was the function I should be using but... I am having problems where by it will crash in Excel 2002 but works fine in Excel 2003.
Please see https://www.experts-exchange.com/questions/21430917/Excel-2002-vs-2003-Error-with-workbook-when-using-2002.html for more details
Cheers
"Tweaking search for lazy users"... erm, what precisely do you wish to tweak?
Thanks for your clarification.
BFN,
fp.