Dynamic Search? If that's what it is

[jamieleeuk]

Hi,

I have a spreadsheet which will be continuously updated as people come and go and as the company expands. Currently the data resides in range A7:I1100 (but this will probably expand to I2000 in no time at all so would want the formaula / VB code to use the I2000 reference).

The spreadsheet is accessible by anyone but only readable, and 9 times out of 10 they are looking just for their name and in order to do that they would have to do CTRL+F (which some won't know) or use the auto filter to search department then office in order to find their details.

The names are in column B7 onwards and are by surname IE Jackson, Michael.

What I want is for the user to be able to type the surname into a cell and as the user types the letters to the surname, I want the results to change letter by letter (sorry, i'm struggilng to explain it) IE

If the user started out by typining "J" it would filter out all other names and their rows other than the ones beginning with J, then the user would type "a" and it would return only the surnames and rows beginning with "Ja" and so on.

THanks

 

[Case_Germany]

Hi,

a possibility is over filters (VBA). I think it's better, I give you an example file. If you use the out-commentated code line not only for the initial letter is searched, but in the text. Give it a try:

Address Filter

Case_Germany

 

[jamieleeuk]

That's bloody fantastic! I only need to use the one textbox but knowing how to use this will make for some interesting additions to some of my spreadsheets.

I notice there's a button which turns off the autofilter though instead of this being built into the actual textbox change event. I have added a bit of VBA to try and show all of the data when the textbox is blank.

If TextBox1.Text = "" Then
Selection.AutoFilter = False
Else
Selection.AutoFilter Field:=2, Criteria1:="=" & Me.TextBox1 & "*", Operator:=xlAnd
End If

This works and filters by the user's unput but when the user deletes the text from the box to make it blank, it give me a Debug error on the "Selection.Autofilter = False" line... any advice?

 

[jamieleeuk]

I have sorted it thanks -

activesheet.showalldata

Thanks for help.

 

[jamieleeuk]

A further issue with this which I am coming stuck with.

My spreadsheet is accessed via a hyperlink on an intranet.

The textbox isn't allowing people to enter anything into it as by default, our IT systems are set to Macro security HIGH.

Is there any way to bypass this without the user having to go to tools > options > security > macro security > Low?

As this will be accessed by over 1000 people at any given time I would rather I just put a note on to use CTRL+F and search that way instead of asking each of them to change the security level as half of them don't know their RAM from their ARM

 

[Case_Germany]

Hi,

Is there any way to bypass this without the user having to go to tools > options > security > macro security > Low?

Yes - Start the file over a EXE or over VBS. Thus simply in VB (not VBA) a EXE program, which starts this file - that's it.

Case_Germany