Excel 2003 search functionality ?

FloppyPoppy · 6 Dec 2016 at 08:38

Hey there guys, Have been in a new job for a little while now and have decided it's that time to make myself a spreadsheet to make my own job easier. This is just to benefit myself and not the company.

I have previously done some stuff in MS Access with similar results however wanted to keep it as simple as possible and stick in excel. The PC i have at work is to say the least.. an artefact of a age gone past. And is currently operating with XP & Excel 2003.

Here is a snip of my sheet so you have an idea of what i am doing.

Now at the top of this sheet (where it specifies space reserved for search functionality) I would love to have a input box and a search button.

Something like this.

What i want to do with this is be able to type in the product code ( will only ever be the product code ) and search, upon pressing the search button i want to see only the product row matching the code i have put in.

I am really not a fan of this style of conditional formatting

As i still find myself searching for what i wanted to find in the first place. Yet it seems to be one of the only methods i can find for 2003.

Do any of you Excel masters have any advice for me? I have been trying to do this for a couple of days now and have near enough given up but the function would be tremendously useful especially when the sheet is fully populated.

I would really appreciate any advice i receive, Sorry if i reply slowly, i work nights so hours are skew-whiff

WillyNelson · 6 Dec 2016 at 13:51

Why not simply put a filter across the whole range and then just search in the filter drop-down on the product code column. Otherwise you're just reinventing the wheel.

Also if you use the Highlight Cells or Top/Bottom rules in the Conditional Format menu you should be able to get a whole host of different formats to suit your needs. Only the top/bottom x values, the top/bottom x%, the highest value, only values above/below a certain value or other cell value, etc.

Grrrrr · 6 Dec 2016 at 14:49

WillyNelson - I'm not sure Excel 2003 had that feature?

It's not the best solution but something like this might help. Give the range of product codes the defined name "ProdCodeList". Right click the tab you're working on and "View Code" then paste this in:

Code:

Option Explicit

Private Sub TextBox1_Change()

Dim Rg As Range
Dim TB As String

    TB = "*" & TextBox1.Value & "*"
    
    Range("ProdCodeList").EntireRow.Hidden = True
    
    For Each Rg In Range("ProdCodeList")
        If UCase(Rg.Value) Like UCase(TB) Then
            Rg.EntireRow.Hidden = False
        End If
    Next

End Sub

Tested on Excel 2013

WillyNelson · 6 Dec 2016 at 15:20

Grrrrr said:
WillyNelson - I'm not sure Excel 2003 had that feature?

Which, the filter or Conditional Format? :confused:

tom_e · 6 Dec 2016 at 15:25

I'm pretty sure we were filtering Excel spreadsheets at school well over a decade ago on Office 97.

FloppyPoppy · 6 Dec 2016 at 17:21

A fair point regarding filtering. I should have mentioned that I have tried that and it has one draw back, you can't search in it and the text is like font size 3 so completely illegible thanks grrrr will give it a go

Grrrrr · 6 Dec 2016 at 20:57

WillyNelson said:
Which, the filter or Conditional Format?

The search box in the filter is what I meant

. I seem to remember Excel 2007 not having the search box.

james.miller · 8 Dec 2016 at 07:46

harry5522 said:
A fair point regarding filtering. I should have mentioned that I have tried that and it has one draw back, you can't search in it and the text is like font size 3 so completely illegible thanks grrrr will give it a go

The text is that size because you have the zoom level set low (it scales with zoom). set the zoom back to 100% and change the font sizes instead. Dialogs will be the correct size then, and it can help with performance as well.

As for searching, is Ctrl+F not enough?

FloppyPoppy · 21 Dec 2016 at 04:13

Sorry for the late update, you are right James that the text size is that size due to the zoom however we currently have a tiny resolution monitor so to get any useful amount of information into one page it needs zooming out sadly.

Ctrl+f whilst it does work it merely goes to the box containing that information, as there is a relatively large amount of information on the screen it would simply be easier to not see the other stuff.

Grrrrr, I have finally found a moment to implement and test your code and after a few small changes and workarounds with the older system it works an treat! I will get a copy of what I changed for you in the interest of knowledge.

Can't thank you enough for the assistance, top bloke!