Smarter Search Process

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