Pseudo VBA Intellisense...!

If you're creating a dropdown list using Data Validation, it doesn't support "autocomplete".

However, if you use a ComboBox from the Control ToolBox toolbar, one of the parameters in its "properties" is AutoWordSelect, so you can turn it on if desired to get the behavior you want.

http://www.contextures.com/xlDataVal10.html

The nearest answer would be to use a combobox instead of a textbox., comboboxes can "autocomplete" As you type it will try to suggest a word that matches the letters you are typing. It might be possible to filter the source data to change the list but hardly merits the work

Originally Posted by D_Rennie

....................here is somethink that will get you what you want or near where you want.
I hope this helps you in some way.

Hi.
. I hope this does not constitute “High jacking this Thread”. I may indeed well start a new Thread and reference this one, but initially I have an enquiry specific to one post (Post # 6) in this Thread
.
……
. I was doing a preliminary search to get as many ideas as possible before attempting myself and / or starting a Thread asking for help with regard to somehow getting a form of Intellisense to apply to a given cell or cells in a workbook.
. To date this is the only reference I have found.
. In this post a file is given which I have been examining. Initially I thought there was some form of intellisense applied to the first 2 columns. That is to say as one started typing in cells in columns A and B, then a suggestion pops up if the typed letters correspond to any words starting with similar characters in that column
. However, I then found this was true for any column in any Worksheet in that workbook, or indeed for any column in any Worksheet in any Workbook!!.
. I cannot see anything in the uploaded Workbook that demonstrates any form of cell intellisense. Possibly due to my inexperience I have overlooked something obvious.
. If someone has the time could they take a look at the File, which I present again here:
https://app.box.com/s/c7ccxjq8moubqgw9t6h1j6q5676m0eh5
. I shall try to contact the Author over P.M. in parallel, and ask if he could advise

Thanks
Alan Elston
.
. P.s.
. FYI:- A bit of background info:
. I use Excel 2007, 2010 and occasionally 2003.
. I have a basic Knowledge of excel from self teaching for about 2 years.
. I have some competence in certain areas of VBA thanks to participation in Forums for about the last 6 months in particular in the area of manipulating, handling, sorting and rearranging large amounts of data.
. I have a bit of experience in writing codes which run on the event Worksheet_Change.
. The project I am working on involves accessing (from a massive list of food stuffs and nutrients ) products to be used in diets. I am currently investigating ways therefore to get a simple quick access to any one product in a cell, possibly through some form of “quasi Intellisense”, after which a “Worksheets_Change - intercept - target thingy” would spring in and would take care of any other accessing, calculations etc. etc.
. Unfortunately I have no experience or knowledge what so ever with Drop Boxes, Combo Boxes and the such.

(. Finally, just out of interest I include an example of the sort of File I am hoping to make more rational in use, possibly with the “Quasi Intellisense” idea. The File is part of a personal project and so is not particularly user friendly or obvious in how to use it!!! At this stage I only include it in this Thread to give any one curious a very basic idea of what I am doing!!
. - Basically, currently, one Types in values per 100g in column C for the planned meal, and macros do the rest. Finally I expect (hope) to develop the file for an order of magnitude more data!!! I only include it here to give a very general overview of what I am doing:
https://app.box.com/s/7r092viqk2zm0qd6mcdudkst3pobrbk2
)

NICE,

I don't think you can hijack a 6 year old thread. I like that this has somewhat became useful.
Don't come on here very often these days. Ive had a **** day so seeing this made me smile THANKS.

A neat little bit of code that i put there. Though Roy hit the nail on the head with " It might be possible to filter the source data to change the list but hardly merits the work ".

I'm a little tired so i have missed what you are trying to do. post up what u want "IN SIMPLE TERMS Short attention span LOL" and ill have a crack at something for you.

Hi,
. thanks for the reply
Alan

Hi again D_Rennie

Originally Posted by D_Rennie

….

I'm a little tired so i have missed what you are trying to do. post up what u want "IN SIMPLE TERMS Short attention span LOL" and I’ll have a crack at something for you.

. Sorry to reply late to the above. The project has actually made me very tired as well. I really struggle to explain it in simple terms. And this is one occasion when a file rather than a Screenshot is appropriate.
. To be honest, in the meantime the complexity has worn out my main computer ## (and me!!!). But as you took the courtesy to reply I will make an attempt again to explain my problem
. Here is the file and then an attempt at a simplified screen shot and explanation.
( XL 2007 “ProAktuelleNuts.xlsm” ):
https://app.box.com/s/a2ocpslyrf28ryv243ovhtek7mulsj1x


Using Excel 2007

-	A	B	C	D	E	F	G	H	I	J
8733
8734	Mispeln					Kcal	192,895 300	54		0.2
8735						Fett	3,354 0,01
8736	55. Nashi-Birne					Eiweiß	10,575 200	52		0.2
8737						Koh	19,804 10
8738	Nekterine					Zucker	13,975 0,1	58		0.1
8739						Ballastoffe	10,538 100
8740	56. Oliven					Wasser	305,057 21	139		13
8741						kalium	1,125 2
8742	57. Orange		0.2			Natrium+	0,16 0,55	46	9	0.2
8743						Kalzium	0,254 1
8744	58. Papaya					Magnesium	0,142 0,36	32		0.1

Sheet1 Nuts

. My main problem lies in that I have a massive long list of Food Stuffs in column A of a spreadsheet.
( Aside: - When preparing a meal or diet I scroll up and down to the Food of interest and type in column C the amount of the food stuff in the meal or diet I am preparing. Once this is done various VBA codes calculate a large amount of nutrient values and add them to a totals list for the entire meal or diet. ( When opening the file a message box asks if you want to clear the last typed in values. ).).

. The file is just about usable in its current form when using a large screen and the middle scroll wheel of a mouse to scroll up and down quickly. But the food stuffs will increase considerably. And I was hoping to use the file on a small Notebook as well. ( ## Currently all I have!!!)

. So what I am asking here is for any ideas / suggestions on how to make selection of a food stuff easier / more practicable. But I expect what I am attempting to do may just be too impracticable and complicated. The reasons for me having to stick with this problem are again somewhat difficult to explain. I will attempt a reply to your PM on that one in parallel to my reply here.

Thanks Again for taking the time to come back to this old Thread.
Much appreciated.
Alan Elston.

Hi
I hit this Thread on a search a long time ago
_... I never quite understood the significance of the downloaded file in post #6, .. but it seemed a good idea what ever might have been suggested. ??? ( I never got anywhere with the combo box suggestions either )

_... At the time I think I thought probably the File , “Sheet1”, was supposed to be referring to the standard function of Excel Automatically repeating values that already exist in a column
https://support.office.com/en-us/art...2-35a236c5b5db
_... The documentation referenced there in that link above is as crap as Microsoft documentation usually is. It does not tell you the full story.

If I am not mistaken , I noticed in passing, in another Thread , that the rows over which this feature works can be controlled by the CurrentRegion associated with the column range of entries that this feature works on. As such it can be controlled to work over any column length ( depth ). This is not immediately obvious, and I expect, like me, most people encountering it for the first time believe it works only in the row immediately under the last filled cell.

_.. So just for anyone else catching this Thread on a search looking for any sort of “Excel spreadsheet pseudo Intellisense”, it might be with taking a look at this Thread, from about Post # 7
https://www.excelforum.com/the-water...ml#post4631249
( This post probably gets you to the answer quickest, https://www.excelforum.com/the-water...ml#post4632711 or start here https://www.excelforum.com/the-water...ml#post4632707 and work through the demo code given here: https://www.excelforum.com/developme...ml#post4632633 and here https://www.excelforum.com/developme...ml#post4632636 )
_..There you can see that you can arrange that it works in any row. You can do that in lots of ways, all based on controlling the CurrentRegion associated with the column range of entries that this “pseudo Intellisense" should work on.. )

Alan

( P.s. Probably worthy of a quick note in someone’s Blog, for any Bloggers out there... )

There's an add-in at https://app.box.com/shared/ono8ni9xvr that does what the OP requests, a ReadMe at https://app.box.com/shared/4qxybepxxr, and an example workbook at https://app.box.com/shared/e8dbbple34

Hi shg,
Thanks very much for the input.
I was not sure if this was something like I was interested in... but I had a look at it..

First I downloaded and opened _ “Progressive Validation Example.xlsx”.
Unfortunately I have never done anything with “ .... data validation type List .....” so it was lost on me a bit

But I had a look at it.

I downloaded your instruction word document next, _ “ReadMe.docx” _ from https://app.box.com/shared/4qxybepxxr
I did not quite understand one instruction towards the beginning: _ “....o Open a file browser window, browse to that directory, and copy the file there .....” ???

I guessed that you meant the file _ “Progressive Validation.xlam“ _ should be downloaded to this folder here:**** ( ##Edit: I guess this is possibly referring to the case of one having downloaded the file and not knowing where it has been downloaded to. ? )


Apart from that, I found the instructions quite clear, mostly easy to follow, which is a refreshing change. ( If they were numbered I would find it a bit easier to refer to when / if asking for further help on them..)

“.....Unfortunately I have never done anything with “ .... data validation type List .....” so it was lost on me a bit. .....”

But I had a go....

I did this, _.. _ ? Application.LibraryPath _ _.. in the Immediate window. It got me this _ ..

____**** _ C:\Program Files\Microsoft Office\Office12\LIBRARY _ ****

I am using Vista and XL 2007 and Google Chrome. ( ##I have my browser settings such that if I try to download anything it asks me where I want to put it ##)
When I click on this _ .. https://app.box.com/shared/ono8ni9xvr _.. and then select download, my browser asks me where it should go, so I looked for _ C:\Program Files\Microsoft Office\Office12\LIBRARY _ in the dialogue box that my browser chucked up.
I found this : _ C:\Program Files\Microsoft Office\Office12\Library _ , which I thought looked close enough.
So I selected save. But it would not let me do it. A small Pop – up said I should I should go see the Administrator. But it did not tell me which one, and I know a few, in Forums and elsewhere.
The Administrator I spoke to, is in charge of a large Supermarket Car park. He just looked very puzzled when I went to see him.
But, the pop up box also said I could save it in Folder “Elston”. So I tried that. ( and made a note of the path, which was _ “C:\Users\Elston” _ ). It downloaded.

( I took the liberty of opening it, as I have sometimes found in the past with Add-Ins that after opening them everything is done automatically anyway and I need to take no further action.
Though and behold it appeared in my VB Editor Window... AddInSHimpfGlifiedDownload.JPG http://imgur.com/xQvrodh )

Despite this, I followed your instructions anyway to _ “• Install the add-in”.
I found those instructions very clear, ( but I confess I have played around a lot with a Tool , ( from RoryA ), )
I navigated, found _ “Progressive Validation.xlam” _ , clicked OK, and it appeared in the list already checked

After this, something different happened in the file color=white]_[/color] “Progressive Validation Example.xlsx”.
DataValidationWTF.JPG http://imgur.com/xWqDQSZ
So I expect I have it up and running as it is intended to?
“.....Unfortunately I have never done anything with “ .... data validation type List .....” so it was lost on me a bit. .....”

But this solution that you have offered, I expect , I may come back to later .
I expect it could be a much better way to do what I am currently doing... So thanks very much for sharing it.

Alan


By the Way, This is what I am doing, and the current solution I have.

This is a daily Diet plan Excel File that I must daily use to help my Wife with her necessary diets:
“ProAktuellex8600x2.xlsm” https://app.box.com/s/fpztob9pcp92fl6hh81zgpzumw9ntcp0
( On opening it will ask if you want to initialise , ( which means clear it and add some entries for Daily Coffee). You can choose to do that or not )
Column A has a lot of badly organised Food products and the list is likely to grow to many thousands.
Finding Products can be very tiresome
The following solution, which I am currently using, works quite well,
I have two fairly simple Event codes, working on “Sheet1”, a _ Worksheets_Change _ and a _ Worksheet_SelectionChange.

To Find a Food Product from column A:
Type in any row in column A. ( It does not matter if an Entry is there , as the _ Worksheet_SelectionChange _ code makes a note of the original value and puts it back later )
Typically, I would just type a CharacterString that is just part of a product that I may be looking for, keeping that part short in characters, but choosing that the characters that I think there is a good chance will be in the Full Food product name or description thereof which I am looking for.
In the _ Worksheets_Change _ code I have a section doing something along the lines of a
Range.Find _ WhatILookFor:=ThatCharacterString ,_ LookingAt:=PartsOfTheWordsInTheRange
Every time it finds a match it selects it, ask if that is OK, and if I select “No” it does a
Find.Next _ thingy

It is annoyingly simple and effective. ( I expect a simple advancement could be to do a further search based on a perversion of the character string, to help should the character string not quite be correct to find what I am looking for ? This might be something someone familiar with Wild Card things might be able to help with ? )

I have a feeling a solution along what your example does could have some advantages. I expect I will come back and look at that again in the future. ( If you or anyone has the time, possibly it might be possible to show me how to apply the intended solution to my data in column A, either the entire column, or in the 50 sub sections as I have it organised, Thanks ? )

Thanks again for Add In It, shg

Alan










Repeating multiple times as I find necessary or just plain fun, no significant places here, necerssarily
6_666TheNumberOfTheBeast.JPG http://imgur.com/t2ZCTTW
6_666TheNumberOfTheBeasts.JPG http://imgur.com/WX6rLO8
http://listenonrepeat.com/watch/?v=d...r_of_the_beast

Hi
I am just passing on a link to a post where AlphaFrog has a solution that does perfectly what the OP originally requested.
https://www.excelforum.com/excel-pro...ml#post4660761
Alan