VBA: search function

    Hi,
    this is my first posting, so hello @ all!!
    I'm from Bavaria, Germany. Right now, I am doing an internship for my studies.



    my problem:
    I need a search procedure which shows an Input Box where you can enter a word to search for. It should work like the original Excel search (Ctrl-F), but with a simpler design, like with my own Text "Enter your Query" and a Button "Submit Query" / "Quit search".
    Is there the possibility to Highlight the Search Target? The problem hereby is that this highlighting should not be permanent. That means the user sees the target for which he searched for, the cell highlighted in a different color, etc. But as soon as the user clicks onto another cell, etc., the highlighting vanishes.....
    If there is no fitting match, there should be a MessageBox like "Sorry the Target xyz cannot be found"



    For suggestions or any help I would be very thankful.


    Jürgen

    Hi Jurgen,


    Welcome to the board :)


    Are you just loking for hints so that you can write the VBA code for yourself or do you actually want somebody to suggest an appropriate routine?


    If the former, have a look in the VBA Help files for InputBox (as you already suggested) for getting the search value and the Find method for actually locating the search value on the sheet.

    Hi Jurgen,


    The attached might get you started. It shows how to set up a userform to get the input, run a search and highlight all found cells with the target.


    For turning off the highlighting, I showed 2 options - first using a delay timer that leaves the cells highlighted for 3 seconds then changes back to blank - or second a change_event macro that changes back to blank when the user enters the cell.


    I couldn't get it to un-hihglight only when the user leaves the cell as your post asked - maybe someone else will have an idea on that.


    Hope this helps


    .....Ralph

    Thanks Derk,


    Sometimes, when I pull a code string out of the macro recorder, I forget to strip out all the unnecessary garbage that excel throws in - and I had forgotten that searchformat wasn't in 97, too.


    too much to remenber - too few brain cells :wink1:

    Hi,
    I have tried the temporary highlight solution with the 3 seconds - Timer from Ralph.
    Therefore I put a Button into one of my Worksheets which calls the Userform1. This Userform I have imported. But when I submit a query from which I know is in a cell of this worksheet, there is always the Message "Could not find..." , although the target is written in a cell of my worksheet.


    Does it only search a specific part of the worksheet and not its whole range? I also don't quite understand the code of UserForm1, e.g. the purpose of faddress or i
    Few Information about my workbook:
    Six worksheets, each with many columns (until "BU") and 150 rows. Row 2 contains about 20 Hyperlinks cells which are merged cells with range of 4 rows each. The Name of these Hyperlinks shown in the cells (not the linked file...) should be included into the search.
    In each worksheet I want a Search Button.

    But also normal cells couldn't be found...


    I don't know why this isn't working out.


    Jürgen

    Ralph's macro is looking at the whole cell for the search term. Do you want it find the term if it is just part of a cell's contents? if so, just change the xlWhole to xlPart in the macro's Find lines.

    Hi!
    I've inserted Ralph's EventChange-Procedure into my Excel-Workbook. It works good. But how can I remedy the highlighted cells if the user does not click onto the highlighted cell?
    Perhaps when the user changes the worksheet or closes the workbook, the highlighted cells of the worksheets should be normal again... does anybody know how to do this?


    Jürgen

    I have adapted the existing code and placed it into the Worksheet_Activate and Workbook_BeforeClose events.


    The cells are cleared of highlighting when the sheet is changed or the Workbook is closed

    Hi,
    one Problem left: in ThisWorkboob, you have written the following code:
    ------------------------------------------------------

    Code
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim myrng As Range
Set myrng = Sheet4.Range("A4:D18")


myrng.Interior.ColorIndex = 0
End Sub


    ------------------------------------------------------
    How can I change it so that it affects all worksheets in my workbook, not only Sheet4.Range?


    Ciao,
    Jürgen

    Why not just put it in each Worksheet


    Code
    Private Sub Worksheet_Activate()
Dim myrng As Range
Set myrng = Range("A4:D18")
myrng.Interior.ColorIndex = 0
End Sub


    Now when any worksheet is activated the range will clear.

    Re: VBA: search function


    shygirltensed8, I'm deleting your post as you should start your own New Thread if you have a question.


    However, I would urge you to read the rules you have agreed to before doing so. Then, read the text on the New Thread page you get when you click the New Thread button.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login