Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 185
  • Last Modified:

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:
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_21396852.html#13867053
===========================================
0
Fernando
Asked:
Fernando
1 Solution
 
[ fanpages ]IT Services ConsultantCommented:
Sorry... did you actually ask for any help here?

"Tweaking search for lazy users"... erm, what precisely do you wish to tweak?

Thanks for your clarification.

BFN,

fp.
0
 
PePiCommented:
i am baffled as well
0
 
FernandoAuthor Commented:
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!
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
FernandoAuthor Commented:
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...
:)
0
 
DreamMasterCommented:
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.

0
 
dmangCommented:
Test for 6 numerics (format "111111x")

Private Sub TextBox2_Change()
   
    If Len(TextBox2.Text) = 6 Then
        If IsNumeric(TextBox2.Text) Then
            TextBox2.Text = TextBox2.Text & "*"   'or a varfiable instead
                                                                    'you could even launch your search
                                                                    'at this point
        End If
    End If

End Sub

I am not sure what you are asking for including the rule.
0
 
FernandoAuthor Commented:
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!)
0
 
DreamMasterCommented:
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.
0
 
FernandoAuthor Commented:
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?
0
 
DreamMasterCommented:
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.
0
 
FernandoAuthor Commented:
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...

:(
0
 
DreamMasterCommented:
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.
0
 
FernandoAuthor Commented:
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
0
 
DreamMasterCommented:
Nothing was shown in your Immediate window??

That kinda baffles me...

Regards,
Max.
0
 
FernandoAuthor Commented:
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 http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_21430917.html for more details

Cheers
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now