Smarter Search Process

Try this (attached file).

• It searches as you type.
• It accepts wildcards;
  • ? (question mark): Any single character; "Bl?ck" finds either "Black" or "Block"
  • * (asterisk): Any number of characters; "N*h" finds "Noah", "North"
• You can set\change the Availability from the userform.

Hi VisionSmart ( and jindon and AlphaFrog )
I had been looking for something like this for some time. https://www.excelforum.com/excel-pro...ml#post4633293 - I was looking for a sort of pseudo Intellisense to pretty well do like AlphaFrog has done

The solution from AlphaFrog is a great start for me now to perfect what I am doing.

( I could not make any sense of the Dataform link jindon gave ( I have German Excel 2007 so things are possible organised differently ) )

In the absence of that solution I had done something else, that might be another solution to consider, or at least to add as a solution to this Thread as the title could bring a lot of people here looking for better / smarter search processes

My Solution ( adapted a bit to the OPs File )

The “names” I am typically looking for could be a lot longer, be badly spelled, and contain extra distinguishing info from similar names. ( They are actually food products so might include the manufacturers name, the food type, flavour etc. )

So the way my solution works is that you
_1. type in the column range what you are looking for. This might sound a bit strange, but
_1a) I ways pushed for space in the spreadsheet
and
_1b) I might, at any one time, be scrolled down the worksheet, so any buttons like AlphaFrog gave did not “follow me” as I scrolled up and down a very deep spreadsheet.
( _1c) After the search is finished any original entry which I wrote over is put back in, so no worries about loosing data in your column to be searched )
_ 2. After this the code selects the cell where it finds the word you were looking for. ( In your case I have made it select the cell two columns to the right, as you asked for )

A worked example of the code I have done for you:
Say I am looking in your list for Zachariah Elston
Using Excel 2007 32 bit

Row\Col	A	B	C	D	E
767		Yasmin
768		Zachariah Elston
769		Zachary

Worksheet: Sheet1
My code lets you write the whole name if you are sure. ( I am not usually sure and it sounds like you are not either. …)
My code also allows you to type up to three words separated by any number of spaces. The first word you need to be sure about. The other two if you get wrong it is not too important
The way my code works is that it goes through initially looking for all three words. If it finds them it selects the cell it finds them in and asks you if it is OK. If you say yes it selects the cell two columns to the right. If you say no it keeps looking.
If it can’t match all three words then it tries to match the first two ( If you only give it two words then the code initially starts trying to find those two words in a cell )
If it can’t match the two words it goes on to look for just the first word.

So take my example. I might be fairly sure that my surname Had _ Elst _ in it. Say I am not sure how to spell the first name. I expect it has _ Za _ in it. Say I think also it might have _ ari _ in it.

So I type in any row in your column B range some version like this
___ elst __ za ___ ari
Elst_Za_Ari.JPG http://imgur.com/3QHcBOW

Hit enter, and away you go!!
This is what you should see
ZakElstonFinded.JPG http://imgur.com/T8RNUSR
_..........................................

Code is here:
https://www.excelforum.com/developme...ml#post4663993

Alan

@ Doc.AElstein,

I think AlphaFrog has done very well with his search Example Workbook. As you quite correctly noted, it can be a laborious task to scroll down a long Column of Data to check who has selected what.

The quick solution to this problem is to add a filter and instantly have the answers at your fingertips!

I have added this small bit of code to AlphaFrog's example;

Option Explicit
Sub Filtr()
Dim LR As Long

LR = Sheet1.Cells(Rows.Count, 2).End(xlUp).Row

    If Sheet1.Range("K5") = "" Then
     Sheet1.Columns("G:I").Clear
      Exit Sub
    End If
    
    Sheet1.Columns("G:I").Clear
    Sheet1.AutoFilterMode = False
    Range("D3").Select
    Sheet1.Range("$D$2:$D$" & LR).AutoFilter Field:=1, Criteria1:=Range("K5").Value2, _
        Operator:=xlAnd
    Sheet1.Range("B2:D" & LR).Copy Sheet1.Range("G2")
    
    Sheet1.Range("$D$2:$D$" & LR).AutoFilter Field:=1
    Application.CutCopyMode = False
    Sheet1.AutoFilterMode = False
    Sheet1.Range("K5").Select

End Sub
Sub ClearFiltr()

    Sheet1.Range("K5").ClearContents
     Sheet1.Columns("G:I").Clear

End Sub

and moved things around a little, as shown in the attached sample Workbook.

Just maybe, you guys would like to give it a try.

Regards.

Hi Winon,
Yes, the solution from Alpha Frog was very helpful to me: I know nothing about User Forms, and his solution I was able to modify fairly easily, and so was able to “leapfrog” into a working solution for my application
I already have it working provisionally in my file. ( Download from here: https://app.box.com/s/fpztob9pcp92fl6hh81zgpzumw9ntcp0 ) . But I only need to search for values in one column (G). I click on cell G1, ( as an alternative to a button ), and Alpha Frog’s UserForm springs up. I start typing in the _ “Search for” __ box on the UserForm the Food group heading I want from Column G , ( My Food groups are grouped into 50 row sections ). I then select from the List box if I find the one I my searching for. The _ Yes _ and _ No _ button codes I have modified to both select the Heading, and, further, the _ Yes _ button also opens ( expands the outline level of ) the 50 row section Group. ( I have the sections grouped in an effort to be able to scroll quicker to find stuff http://www.mrexcel.com/forum/excel-q...uped-rows.html ) , - This new solution now adds more flexibility again . I had been looking for nice neat solution like this for some time,. ( The _ Call _ button code I have modified to close the 50 row section Group )

I have no idea about Filtering unfortunately. Currently I do not need it.
But thanks for adding a solution to this “Smarter search”. I expect I will probably come back and reference your solution later. - I might try to modify the search to look in external closed data Files with much more data, some of which might be very similar. Maybe then some sort of filtering will be useful. I tried out your File. I see what it is doing.
Thanks again
Alan






Edit: I used .xlsb for the attached File just to get it to upload ( limit is around 9.77 MB for .xlsb as apposed to 1 MB for .xlsm ( My file is .xlsm isnormally saved as .xlsm and is around 2MB ) ) . To get all codes to work you may need to resave after download as .xlsm . ( or use the link to the box.net File sharing given above for download of the .xlsm File )


EDIT 2: Thanks, Winon, for the Rep

Hello Doc.AElstein,

Thank you for your feedback, and sample Workbook.

You have done an excellent job tweaking AlphaFrog's sample to cater for your exact needs. Well done!

Yeah, I suppose my version may be helpful under different requirements.

Thank you for the "Rep" tack, I appreciate it!

Kind Regards,

W

In case anyone is interested, or for future reference - here is a modification I made to Winon's to make it a bit more generally applicable.
there are just some named ranges to setup or select from the setup userform.

Hi scottiex,
I think I see what you are doing, ( although Filters UserForms and Named ranges I am not too familiar with ). … It would appear that “box” things in your UserForm have a Text/Value Property of what is written in them. That value is taken by your code as the address you give to some named ranges. Then you use those names in place of a range actual address string string in the relevant Range(address string) bits in Winon’s code.
Thanks for that
_.._______________


The thing about the original code that I still find incredibly useful is the following: It appears that in the Text Box thing in a UserForm ( the _ Search For _ box in Alpha Frogs UserForm ) there is an Events code which springs in each time you change the text in the box but you do not have to hit Enter as you do in the case of a normal worksheet change Events code. That is the half the key to this “pseudo intellisense“ or “multi line autofill/autocomplete” working.
The other half of the key to it working so well is the possibility of using Wild cards in the VBA Range.Find method.
So what the UserForm does every time a character is added to the string in the _ Search For _ box, is to trigger a _ Sub TextBox_Change() _ Events code. In that code there is a Range.Find ( and subsequent Range.FindNext). These have as What:= to search for as _ “Text_Box_value” & “*” . All found Strings in the range are then added to a list box.
The results of all this is the classic intellisense of a drop down list of suggestions , (that typically reduce in number as you type further characters). And just like intellisense you can click on a suggestion. In my code version I then go to / select and reveal the row containing the selected word
I had been looking for something like this for ages
Threads / Blog posts / discussion Forums on attempts at a pseudo intellisense never hit on this way from Alpha Frog.
( https://www.excelforum.com/excel-pro...ellisense.html
https://www.excelforum.com/excel-pro...ml#post4632958
https://www.excelforum.com/the-water...ml#post4636789 )

I use a slightly modified search as I am looking for a Food product name that might be long with lots of words in it in a single cell. So my search string is ”*” & Text_Box_value” & “*”. This will catch a key word I might be looking for anywhere in the long text. I also use xlformulas as this seems to work if my rows are hidden/ grouped, ( as they might be ).
( _ rng.Find(what:="*" & Me.TextBox1.Text & "*", After:= rngSearch.Item(1), LookIn:=xlFormulas, Lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False) _ )

I see some great further possibilities here: You could add some routine that at each search after the current searching then repeats with a few runs with a few wild card ? (question mark),
or
extend the code to do something like mine, which after seeing a space in the given string, followed by another word , searches for a match with both words. The end result of that is you have a form of an instantaneous Google search that will catch strings close to what you are looking for.

Alan

Originally Posted by Doc.AElstein

I use a slightly modified search as I am looking for a Food product name that might be long with lots of words in it in a single cell. So my search string is ”*” & Text_Box_value” & “*”. This will catch a key word I might be looking for anywhere in the long text. I also use xlformulas as this seems to work if my rows are hidden/ grouped, ( as they might be ).
( _ rng.Find(what:="*" & Me.TextBox1.Text & "*", After:= rngSearch.Item(1), LookIn:=xlFormulas, Lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False) _ )

This has turned into the thread that never ends.

A minor syntax point, instead of using ”*” & Text_Box_value” & “*”, you could just change the argument Lookat:=xlPart

Also, you can omit the After: argument and it defaults to searching from the start of the range.

rng.Find(What:=Me.TextBox1.Text, LookIn:=xlFormulas, Lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False)

Hi AlphaFrog

Originally Posted by AlphaFrog

This has turned into the thread that never ends.....

it is a great solution from you, in my opinion. As I mentioned, this sort of question was asked before, and up until now I had seen no solution. Possibly it is obvious when you know your way around UserForms, but no one had caught it previously , as far as I could tell.
_....

Originally Posted by AlphaFrog

...syntax point, .....Also.....,

Thanks, .. I tend to over do it often with using arguments even when they are the default, ( as well as using named arguments, lots of ‘comments etc. ). It is just a personal preference to help remind me what is going on.
I use the _ Lookat:=xlPart _ as well in my codes sometimes . Once again I often do things like this just to remind me what was going on, ( I think also here I was experimenting a bit still as I posted – I am playing around currently with the final form )

Thanks again for sharing your solution, it is really very useful to me
Alan

Hi AlphaFrog,
Could I ask a quick last follow up question please ?_…
_.. - How is something like this working: ?

            With Me.ListBox1
             .AddItem Found.Row 'Row number
             .List(.ListCount - 1, 1) = Found.Value 'Food Group Name
             .List(.ListCount - 1, 2) = Found.Offset(0, 1).Value  'Kcal
            End With

Here is a typical search result from my version of your code using lines like above , ( along with code lines earlier of
__ With Me.ListBox1
___ .ColumnCount = 3 'Row number????, Name, Kcal
___ .ColumnWidths = "20;50;40"
__ End With
) :
Namej_JogMargerSearch.JPG Screen Shot: http://imgur.com/s06pg3q
Namej_JogMargerSearch.JPG

I use _ Me.ListBox1.Value _ later in my to get the row number. ( For example , in the image example it gets me the row where _ “JogMagerer” _ is. )

I am slightly confused ??
All these three things , _ .AddItem _ .List(.ListCount - 1, 2) = _ .List(.ListCount - 1, 1) = _ , seem to be adding to the list box.
But I do not see the row number ( added by .AddItem ) held in the List box.
( In addition I see a first number and I am not quite sure what that number is ??? )
Is this telling me that by doing _ .Add xxxx _ , I get a shown ( item ??? ) number and a corresponding “name” _ xxxx _ . The “name” , _ xxxx _ , is not shown but then I can get it returned from the .Value , if I have the corresponding item highlighted , as in my screen shot.

( Some further experiments are suggesting to me that .Value will sometimes return me the string value in the first column. – If I assign an Array to a List Box, then I get no ( item ??? ) number shown, but the .Value appears to return me the value in the first column of the highlighted row in the ListBox. In your code, .Value is giving me what was .Added

I have not been able to find a clear documentation on this.
So I am just looking for confirmation that I have got it right. ( and I am wondering where that ( item ?? ) number comes from )

Thanks again
Alan.





( Current File: “ProAktuellex8600x2.xlsm” Download: https://app.box.com/s/of7p2hlnezf6qrahhyl9ugf0ca75ux7d ( Click on cell G1 to set off UserForm Search Food Group code to kick in ) )

Originally Posted by Doc.AElstein

The thing about the original code that I still find incredibly useful is the following: It appears that in the Text Box thing in a UserForm ( the _ Search For _ box in Alpha Frogs UserForm ) there is an Events code which springs in each time you change the text in the box but you do not have to hit Enter as you do in the case of a normal worksheet change Events code.

wandering a little off topic but I have this sort of thing attached to a hotkey (called from an addin).
I can press the hotkey and either define a list or select an item from predefined list using a userform like this.
It is like data validation but it doesn't need to be set up for the (data validated) range in advance and of course the list shrinks as I type until there is only one option (and then it just enters it in the active cell).

Apologies to AlphaFrog on contributing to it being the "thread that is solved but never ends."

Hi scottiex,
Yeh, I keep thinking of adaptations and uses of this “real-time pseudo intellisense search” stuff to speed up me getting at stuff in long lists of mine all over the place.. .. like buttons to click on to select a different data file … I might even think of changing the .Find to some other thing like a Match working with wild cards on a closed Workbook range reference…etc.. etc… possibilities are endless…
( Adding a solution to a Solved thread is all good ( and allowed )

_.................................
_._________________________

Hi AlphaFrog,

Originally Posted by AlphaFrog

first column .. is the row number but your column width .. too narrow ….

[embarrassed]Ah, how stupid of me [/embarrassed]
I guess the _ : _ when I look again was not a colon , but rather just the start of the missing number characters….


So further experimenting:-

it appears things all start here at “base” 0 .. so we have three columns _ 0 _ 1 _ 2

It appears I must do an _ .Add _
That _ .Add _ will increase the .ListCount by 1
( If I wish, I may give an item with the _ .Add _ , and that will go in the first column, column number 0 .
If I choose to give an item , I can also choose to decide at which index ( row in the list box ) that it goes ( up to the ListBox “rows” ( index) so far _ .Added ) .
If I do not give an index , then it will default to the current one being _ .Added ).
I can put things into any place in the list box ( up to the item number .Added so far ) , including the first column, using the _ .List(y, x) thingy _ .
If at each loop I want to add things into the ListBox “columns” after the _ .Add _ , into the same ListBox “row” _ .Added , then I will need to subtract 1 as the .ListCount was increased by 1 by the _ .Add
)
( A bit unusual for an Index to start at 0, but never mind, it is all a bit perverse ** )

So all these do the same, ( with some bits being redundant ) ..

            With Me.ListBox1
             .AddItem pvargitem:="Anyfink To be overwritten at the next line", pvargindex:=.ListCount
             .List(.ListCount - 1, 0) = Found.Row
             .List(.ListCount - 1, 1) = Found.Value 'Name
             .List(.ListCount - 1, 2) = Found.Offset(0, 1).Value  'Kcal
            End With

            With Me.ListBox1
             .AddItem pvargindex:=.ListCount
             .List(.ListCount - 1, 0) = Found.Row
             .List(.ListCount - 1, 1) = Found.Value 'Name
             .List(.ListCount - 1, 2) = Found.Offset(0, 1).Value  'Kcal
            End With

            With Me.ListBox1
             .AddItem
             .List(.ListCount - 1, 0) = Found.Row
             .List(.ListCount - 1, 1) = Found.Value 'Name
             .List(.ListCount - 1, 2) = Found.Offset(0, 1).Value  'Kcal
            End With

            With Me.ListBox1
             .AddItem Found.Row
             .List(.ListCount - 1, 1) = Found.Value 'Name
             .List(.ListCount - 1, 2) = Found.Offset(0, 1).Value  'Kcal
            End With

I am still guessing that the .Value returns the value in the first “column” in the list box at the “row” in the list box which I have selected.

I think I have it sussed. Just a strange perverse way of doing things, - I guess that is where the named arguments come from
PerverseArgumentindex:=
PerverseArgumentitem:=

Alan

Sounds like you got it. The .List is just a zero-based 2-D array.

Originally Posted by Doc.AElstein

I am still guessing that the .Value returns the value in the first “column” in the list box at the “row” in the list box which I have selected.

The ListBox1.Value property returns the value from the selected row of the Bound column. By default, the Bound column is column 0 (the 1st column). You could change the Bound column with the .BoundColumn property.

ListBox1.BoundColumn = 1 'The value from second column will be returned from the .Value property

@ scottiex,
Hi scottiex,
There is a Rule about Hijacking a Thread ( Rule 2: https://www.excelforum.com/forum-rul...rum-rules.html ), and also when you reach that “daunting career height” of a Forum Expert, :-) , you can select something from a drop down list (CannedReplies2Hijack.JPG http://imgur.com/ZCANICW ) to punish and whip an un expecting OP into shape :-) - Unfortunately your post does not comply with Rule 2 ……. Do not post a question in the thread of another member -- start your own thread. ….. If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread. …..Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too. ….. My follow up questions can be interoperated as Hijacking. Often such questions simultaneously both further and improve the content and add a solution. As I mentioned adding a solution even to a Solved thread is allowed. It will sometimes just be down to how a Moderator might interoperate something as Hijacking or Adding a Solution. … _...
_...Just my opinion, as someone that takes the time to lurk a lot, … is that there is an amazing wealth of info that, sadly , gets more and more hidden under the increasing number of Threads , many of which duplicate previous efforts. So if I see a good descriptive Thread title, where the Thread content is very relevant / similar to what I am working on, then, at the risk of Hijacking, I will ask a follow up questions and / or contribute my solutions. This is a personal quest to both further and share knowledge whilst helping both offset the loss of previous efforts and alternative solutions and reducing the clutter caused by yet another thread.
Like anything , it is often down to opinions on these things. :-)
( Also, for selfish reasons, I prefer to do longer and fewer posts, as it makes it easier for me to find my own stuff for future reference )
If I had been the OP I would have, ( as I did when I originally tried to an Alpha Frog type solution ) , have “hijacked” this thread… https://www.excelforum.com/excel-pro...ellisense.html . The Thread was never Solved, … a few prominent people tried, … and if you read the first Post, the OP is asking for exactly for what I also wanted, and it is exactly what Alpha Frog gave in this Thread. ( The Threads belong together under the Title “VBA Psuedo Intellisense Smarter Search Process” … but you could spend years consolidating duplicate solutions in Forums.. )
_............___________________
_.____________________________________
@ AlphaFrog
Hi Alpha Frog

Originally Posted by AlphaFrog

... The .List is just a zero-based 2-D array.
...ListBox1.Value property returns value from the selected row of the Bound column. By default, the Bound column is column 0 (the 1st column). .. change the Bound column with the .BoundColumn property.

ListBox1.BoundColumn = 1 'The value from second column will be returned from the .Value property

That clears that up nicely, thanks. I think I finally have it sussed now.
_.................

It was Mean that the truncating made it appear that I had colons, _ : _ , but I should have done more experimenting and caught that. .. but there are often so many variations to consider in these things….

I also should have guessed as you had the first column “hidden” with a width of 0. I expect I will use that idea a lot. It will be interesting to see what limits I have in List Boxes , rows and columns, etc..
This solution has opened a lot of interesting possibilities for me. I think the use of the UserForm in my File was something I should have looked at earlier. ( The UserForm I already had was given to me and I never really understood it .. )
Thanks again
Alan

Originally Posted by scottiex

Apologies to AlphaFrog on contributing to it being the "thread that is solved but never ends."

No need to apologize. I was just making an observation. You guys have provided good solutions as well.

You're welcome. Thanks for the feedback.

My solution is not original. I've seen other so called real-time searches like this, though I don't recall where.

Can you paste your whole procedure?

Hi AlphaFrog,
Thanks for coming back again.

It might be easier to see from the files I _..
_ … uploaded ( as .xlsb to fit in, but should be saved as .xlsm for all codes to work )
or
_. … gave the file share site ( box.net ) link to … File Share Link again: https://app.box.com/s/fpztob9pcp92fl6hh81zgpzumw9ntcp0

The file is a bit big and messy as I have been working and adding to it for a couple of years now…..and currently I am experimenting with your search codes. I am probably not doing things the most efficiently as I am experimenting a lot just now_...
All my codes would need about 20 post to fit them all in !!! ..
_......

But here goes, the bits relevant to the current issues.. .. my current versions are something like this:

I have two UserForms, (ufResults and UFrog) , which I treat as Classes, ( as I was reading up and that seemed like a good idea ). So in a normal module, “Globies” I have this:

Public fm As ufResults
Public fmSrch As UFrog ' AlphaFrog  https://www.excelforum.com/excel-programming-vba-macros/1186516-smarter-search-process.html#post4660761   

In “Sheet1” Worksheet code module of my main File I have , amongst a lot of other stuff , a Worksheet_SelectionChange code, part of which currently causes your UserForm to come up when I select cell G1 . This works simply because cell G1 has this value in it “Gruppieren Food Group” _...... In the Worksheet_SelectionChange code a section looks like this:

    ElseIf InStr(1, Target.Value, "Gruppieren", vbBinaryCompare) > 0 Or InStr(1, Target.Value, "Food Group", vbBinaryCompare) > 0 Then
     Set fmSrch = New UFrog
     fmSrch.Show vbModeless ' False ' modal:=vbModeless, so can do other things
    Else

The rest that is relevant is really just my current adaptations of your code as I explained in Post #8. ( There is probably a few redundant bits in it, as I am still experimenting with things, hence my general questions… )

Private Sub UserForm_Initialize()
 Me.Frame2.Enabled = False
 Me.OptionButton3.Enabled = False
 Me.OptionButton4.Enabled = False
 Me.OptionButton5.Enabled = False
    With Me.ListBox1
     .ColumnCount = 3    'Row number????, Name, Kcal
     .ColumnWidths = "20;130;40"
    End With
End Sub
Private Sub TextBox1_Change() 'Find
Dim FirstFound As String
 Me.ListBox1.Clear
 Me.Frame2.Enabled = False
 Me.OptionButton3.Enabled = False
 Me.OptionButton4.Enabled = False
 Me.OptionButton5.Enabled = False
 Me.OptionButton3.Value = False
 Me.OptionButton4.Value = False
 Me.OptionButton5.Value = False
Dim rngSearch As Range: Set rngSearch = Worksheets("Sheet1").Range("G71:G17221")
    If Me.TextBox1.Value <> "" Then
    Dim Found As Range: Set Found = rngSearch.Find(what:="*" & Me.TextBox1.Text & "*", After:=rngSearch.Item(1), LookIn:=xlFormulas, Lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False)
    If Not Found Is Nothing Then
        FirstFound = Found.Address
        Do
            With Me.ListBox1
             .AddItem Found.Row 'Row number
             .List(.ListCount - 1, 1) = Found.Value 'Name
             .List(.ListCount - 1, 2) = Found.Offset(0, 1).Value  'Kcal
            End With
         Set Found = rngSearch.FindNext(After:=Found)
        Loop Until Found.Address = FirstFound
    End If
End If
End Sub
Private Sub ListBox1_Click()
 Me.Frame2.Enabled = True
 Me.OptionButton3.Enabled = True
 Me.OptionButton4.Enabled = True
 Me.OptionButton5.Enabled = True
    Select Case Me.ListBox1.List(Me.ListBox1.ListIndex, 2)
     Case "Yes": Me.OptionButton3 = True
     Case "No": Me.OptionButton4 = True
     Case "Call": Me.OptionButton5 = True
     Case Else: Me.OptionButton3 = False: Me.OptionButton4 = False: Me.OptionButton5 = False
    End Select
End Sub
Private Sub OptionButton3_Click() 'Yes                                      'Me.ListBox1.List(Me.ListBox1.ListIndex, 2) = "Yes"
 ActiveWindow.Panes(3).Activate ' Typically I have an upper and lower pane. the lower is where most things go on, but I like to keep the headings viewable in pane 1
 Worksheets("Sheet1").Range("G" & Me.ListBox1.Value).Select ' Because Evants are enabled the selection cause a code to spring in and open the 50 row section          '  Value = "Yes"
 Let Application.EnableEvents = False ' The following is just mucking about to try to get all the section viewable
 Worksheets("Sheet1").Range("G" & Me.ListBox1.Value + 20).Select            'Value = "No"
 Worksheets("Sheet1").Range("G" & Me.ListBox1.Value).Select                 'Value = "No"
 Let Application.EnableEvents = True
End Sub
Private Sub OptionButton4_Click() 'No
 'Me.ListBox1.List(Me.ListBox1.ListIndex, 2) = "No"
 Let Application.EnableEvents = False ' Because this is false, the following does not open the selected 50 row section
 ActiveWindow.Panes(3).Activate
 Worksheets("Sheet1").Range("F" & Me.ListBox1.Value + 20).Select            'Value = "No"
 Worksheets("Sheet1").Range("F" & Me.ListBox1.Value).Select                 'Value = "No"
 Let Application.EnableEvents = True
End Sub
Private Sub OptionButton5_Click() 'Call Close . This code section simpply allows mme to call a code to close the current 50 row section
 Me.ListBox1.List(Me.ListBox1.ListIndex, 2) = "Close" ' "Call"
 Application.EnableEvents = False
 Call Tabelle2.OpenClseSectionPrdFnd((Me.ListBox1.Value - 50), False) ' -50 as value required is top row, -  Me.ListBox1.Value appears to be   .AddItem Found.Row 'Row number
 Application.EnableEvents = True
 'Range("D" & Me.ListBox1.Value).Value = "Call"
End Sub
Private Sub CommandButton2_Click()
 Unload Me
End Sub

With those codes, if you hit cell G1, and then write in _ “jog” _ in _ “Search for Food Group” _ box, then you see this
Post_17.JPG http://imgur.com/0Eo97JK
Post_17.JPG

Currently I am just not 100% clear how the three lines

             .AddItem Found.Row 'Row number
             .List(.ListCount - 1, 1) = Found.Value 'Name
             .List(.ListCount - 1, 2) = Found.Offset(0, 1).Value  'Kcal

relate to those three seen columns in the List Box. Mostly it is that first _ number: _ which looks weird/ new to me. ( In my other userForm I assign an Array to the list box, and I do not see any first column with funny numbers in it). I can see where the second two columns are coming from. And I am guessing that somewhere a list of the _ .Added _ is held, and when you click on the relavent word, then you get given the _ .Added _ thing ( row number in this case ) via the _ .Value on the ListBox

I guess I am a bit confused with
_ ListBox.Value
and
_ Listbox.List(x, y )
and
_ what that first _ Number: _ has got to do with anything

Alan

The first column in the listbox is the row number but your column width is too narrow to see all the digits.

Hi,
I have been developing the solution here quite a bit for my own project, since getting a good start from the solution in this Thread.

So I am just adding some solutions to this Thread for future reference.
The main advancement and development to the current solutions involve using external data files ( closed Excel data files in this case ).
Because of this I have a few solutions, some of which are a bit funky at this stage, but they may serve as a good reference point for the future.
The detailed solutions I have posted over in the Development Sub Forum. I will be continuing that but won’t post follow ups about it here as I will be optimising it in a direction a bit away from the simple search to include importing data based on the search. It might be of interest to someone with a similar project. The main theme I guess of what I am trying to do is get at data efficiently held in simple tables as an alternative to looking at it in more advanced database ways.

I will try to just summarise the initial smart search ideas here I have .

Some of the solutions are coming out very inefficient just now, but they involve ways of importing data from external sources which I an considering in parallel and may therefore be a lot more efficient in total later.

I have few solutions, in order of in sensibility or “irrationality” lol…
_1_ Full “pseudo like database theory and relational databases” closed external workbook referencing code
_ 2_ Slightly improved or saner version or 1 , the difference being I only do the closed range reference once, to bring the values into a temporary range, as I go on to do with all the next codes…

_3_ Simplified “normal” version of _2_
_3b)_ I add my entries into the UserForm in one go having built an Array of the entire search results
_3b(ii) This is Code 3b) but using Evaluate(“ “) on Excel Formulas in place of , as in 1_2_3_3b)_ , pasting in, and retrieving result from, an arbitrary cell.
_3c) A VBA Application.Match and VBA.Application.Index is used as an alternative to the Excel spreadsheet functions.

_4_ Range.Find after initial closed range reference values import
This last one probably ends up doing something fairly similar to the original codes from this Thread. I mainly do this and the next, _5_ , to have a comparison to see how badly my weird codes have messed up the performance.

_5_ Range.Find / Range.FindNext
Very close to original Codes from this Thread

_6_ Very simple VBA Arrays loop and compare for match at each row ( no use of worksheet functions in the searching)
_7_ An improved version of _6_
_..................
The first three have one main distinguishing characteristic to that of 4 and 5.
This distinguishing characteristic is the use of (Excel) Match ( in conjunction with Excel Index) in place of (VBA) Find / FindNext
The reason for me thinking about this is that one way to search an external closed excel data file is to get the integer position along ( down a list ) of the matching to search value from using the spreadsheet Match Function in a cell with a closed workbook reference as its LookUp to be Searched range, and then use this found position as the “row” coordinate along with a column coordinate of 1 in the spreadsheet Index Function to return in a cell the actual matching value from the closed workbook range ( list ).
_........

Here brief summary of all and links to the more detailed explanations, codes and files

Code 1) “Irrational Database”
From about here: https://www.excelforum.com/developme...ml#post4668839
I am not sure what I was smoking when I wrote this code. But it must have been good
I tried to make it look like a sort of “Pseudo Excel DataBase SQL command driven web scrapping” code. The parallels and analogues are somewhat dubious and abstract. You probably need to understand all that sort of stuff to even see remotely the analogies, and if you do understand all that, then you will probably thinks it is just a load of old bollox.
It’s a total mixed up mess, with, for example, strings being split then rejoined, loads of unnecessary variables, and pseudo functions. It is a good example of Obfuscating like database theory and relational databases

Code 2)
From about here: https://www.excelforum.com/developme...ml#post4669900
The last code, Code 1) , is , as expected very slow for a large closed file. One obvious inefficiency is that of, at each match attempt , the closed reference range is brought in. I did this as I am hoping some time in the future to be able to break into the XML cache data in the same way that Excel does itself when the it attempts a use of a closed reference range and no longer finds that Workbook.
As an immediate attempt at something close to this, the first Obfuscation code, code 1_ is modified slightly such that the .Opening to be layed open OOEPDB.conn object changes the reference to an internal temporary range filled from the initial Full range.
This is done within the function way method in the .Opening to be layed open OOEPDB.conn object Class wiring and therefore requires a new version of the … Application Programs Interface directly linked library provided by me ( Alan ), Obfuscating Overkill ’Explicit Pedantry DataBase …. code object

( The code makes a temporary worksheet for the range. I call it “XML_OOEPDB” because I am trying to make the parallel that this could be the XML file Excel has already from the last brought in closed Workbook reference. I just have not figured out how to break into and use that yet. All the following codes use this temporary worksheet, mainly for the brought in range to be searched )

Code 3) ‘s
These are all more sane codes doing away with the weird obfuscation stuff from above. So really it is just looking at the idea of using Match to return multiple values as an alternative to VBA Range.Find / Rang.FindNext.
_3)
From about here: https://www.excelforum.com/developme...ml#post4670779
A sane version of _2_ , ( the code imports the external to be searched range once at the beginning).
3b)
Here: https://www.excelforum.com/developme...ml#post4670971
The only difference to the above code, _3_ , is that I fill an Array with all found values first before adding that to the List Box 1 in one go, as an alternative to the other codes which add a row at a time. - I don’t have too much experience with filling List boxes and I am not sure if like in filling a worksheet one row at a time it “slams on the breaks” ???.
3c)
Here: https://www.excelforum.com/developme...ml#post4671018
This is code 3b) but using equivalent VBA Application.Match and VBA Application.Index formulas.
3b(ii)
Here: https://www.excelforum.com/developme...ml#post4671858
This is code 3b) but using Evaluate(“ “) instead of pasting the Excel Match and Index into a cell
Code 4)
Here: https://www.excelforum.com/developme...ml#post4671061
Code 4) is code 3c) with VBA Application.Match and VBA Application.Index replaced with VBA Range.Find
Code 5)
Here: https://www.excelforum.com/developme...ml#post4671509
This comes very close to the original codes in this Thread which use the Range.Find followed by the Range.FindNext
Code 6)
Here: https://www.excelforum.com/developme...ml#post4671802
Simple VBA Looping code
Code 7)
Here: https://www.excelforum.com/developme...ml#post4672791
The basic concept is that the code differs from the simple VBA looping code of Code 6) after the first search. ( The first search is done on the first single entered letter ).
The difference is that the next search is done only on the Array of those matches of the last found rows. This makes the searches for increasing character strings very efficient.

_.__________________________

I stated to do some comparisons of the codes with some speed tests here: https://www.excelforum.com/developme...ml#post4671944
More details and discussions of those are given in the above Post and proceeding to that Post Posts. Briefly, several quirks of VBA Functions along with the fairly good performance of a simple VBA looping code made the choice of best code initially somewhere between a simple VBA looping code and a version of code 3). After a bit more thought and experiments on other codes , with some more quirks in worksheet functions, I decide to go for Code 7. More details in the referenced Posts..

Alan

_._________________________________


P.s.1. Testing codes
To try out any of my codes in the Files uploaded at the various posts in the Development sub Forum you go about it like this.
Download any of the .xlsb Files, but resave them always as “ProAktuellex8600x2.xlsm”.
Make sure the first worksheet is selected. If any pop up comes up asking if you “want to initialise”, then just answer “No”
You will need to change, or add a reference to a closed data workbook, ( and BTW the codes will work just as well if the data workbook is open ). You will need to edit this code section in the UserForm, UFrog.

   Let rngRefs(1, 1) = "Pro": Let rngRefs(1, 2) = "='H:\ALERMK2014Marz2016\NeueBlancoAb27.01.2014\AbJan2016\[ProAktuellex8600x2.xlsm]Sheet1'!$G$71:$G$17221" ' "=" & "'" & ThisWorkbook.Path & "\" & "[ProAktuellex8600x2.xlsm]Sheet1'!$G$71:$G$17221"
   Let rngRefs(2, 1) = "McCafe": Let rngRefs(2, 2) = "='I:\McDonnaldsBurgerKing\[McCafe.xls]Tabelle1'!$A$21:$A$101"
   Let rngRefs(3, 1) = "DBSept16": Let rngRefs(3, 2) = "='H:\Excel0202015Jan2016\ExcelForum\wbSheetMakerClsdWbADOMsQueery\MsQueeryADO\[DBSept2016 - first AlanSucces.xlsx]Tabelle1'!$A$21:$A$8810"

You might want to edit out this MsgBox as well which I used to do some simple speed tests

 MsgBox prompt:=VBA.Timer – StTme

( To get a closed workbook range reference easily see Ps.2 below )

So with “ProAktuellex8600x2.xlsm” open , in Worksheet “Sheet1”,
_1- select cell G1, then
_2 – select a closed workbook closed reference, then
_3 – type in characters
UFrogSearch.JPG : http://imgur.com/b9MLpRk


P.s.2 Get Closed Workbook reference
One thing that might put people off using this idea is the long and tricky syntax in the closed reference path and file name string. But it is actually very simple to get.
As example: Take the second closed path reference where I demo with a fairly simple data file ( Nutritional values for the Food products at MacDonald’s Coffee bars ), a search of a column ( The Food Product names/ string description) , in that closed Excel data file.
Here is the File: “McCafe.xls” https://app.box.com/s/dl70iogr8ae0bmbjokemzx5x7u1s6w5b
When I first added it was in some obscure place. You can save it anywhere.
So just open the main file and the data file.
Go into any spare sheet in the main workbook , ( or any other spare workbook ( Or make one, - you can delete it later) ) .
In any cell type in _ = _ . Now go to ( select) the data worksheet and select the entire range to be searched through Now hit Enter.
( Usually some error will be given in the cell, but that is not important).
Select the cell and in the formula bar you should see something of this form:
__ =[McCafe.xls]Tabelle1!$A$21:$A$101
This is a reference to an open Workbook, in this case, “McCafe.xls”
Now close workbook “McCafe.xls”.
Excel automatically corrects the formula for a closed workbook reference, so that the formula in the formula should now have a form similar to this
__='I:\McDonnaldsBurgerKing\[McCafe.xls]Tabelle1'!$A$21:$A$101
Highlight and copy that formula from the formula bar, and paste that into the code part which fills the second column in the second List Box, ListBox2: ( In Sub UserForm_Initialize() ):

  Dim rngRefs(1 To 3, 1 To 2) As String ' Search Rng
   Let rngRefs(1, 1) = "Pro": Let rngRefs(1, 2) = "=" & "'" & ThisWorkbook.Path & "\" & "[ProAktuellex8600x2.xlsm]Sheet1'!$G$71:$G$17221"
   Let rngRefs(2, 1) = "McCafe": Let rngRefs(2, 2) = "='I:\McDonnaldsBurgerKing\[McCafe.xls]Tabelle1'!$A$21:$A$101"
   Let .List = rngRefs()
   .Selected(0) = True ' Make the first entry selected

( You can now delete the temporary cell which you used if you wish )

Here is another link I use with a more tricky path, but you get it simply using the above technique

 ='H:\Excel0202015Jan2016\ExcelForum\wbSheetMakerClsdWbADOMsQueery\MsQueeryADO\[DBSept2016 - first AlanSucces.xlsx]Tabelle1'!$A$21:$A$8810

Here is the File
“DBSept2016 - first AlanSucces.xlsx” https://app.box.com/s/j4ssuud642l1dr5tp689a080y7ustl7t

By the way, those are the two files I use in the speed tests that I am doing over at the Development Sub Forum