• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Functionality similar to =(formula)

S

Sinu

New Member
In excel when we type =Se, it will give a list of formulas starting with "se".

I want to replicate this feature as a autofill.
Problems: multiple columns, each having different lists for autofill (Company Name, Location, Country) and each has more than 3000 records.

Solution i've made.: on cell selection, i paste a textbox on top of the cell. and as and when user types below a listbox will get filled. thus giving the user options he can choose from.
upload_2014-8-7_13-15-23.png

there is a delay in populating the listbox, hiding and showing the text box.
Is there anyway i can replicate the functionality that excel has when a user starts typing in a cell, it'll give a dropdown list box to select from.?
 
sgmpatnaik said:
@Sinu

Welcome to Chandoo.org, Glad You Are Here

Please check this link

http://trumpexcel.com/2013/10/excel-drop-down-list-with-search-suggestions/

and

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

Hope It solves your problem, if any problem please come back

Thanks
Click to expand...


Dear @sgmpatnaik ,
Thanks for your reply. i have seen these solutions. The problem is, is there a way where i can avoid using drop down control?

Data validation doesnt provide a flexibility for data entry. Means, the user will have to type, then click on the arrow to see suggestions.

I hope you got it.
 
@Sinu

Please upload a sample file with your desire request

i will try to solve your problem other wise our Ninja's too

Thanks
 
sgmpatnaik said:
@Sinu

Please upload a sample file with your desire request

i will try to solve your problem other wise our Ninja's too

Thanks
Click to expand...

Hello Ninja brothers,
Please find attached the input file. i've removed a lot of data from the sheet.
Please check columns A14:A200. That is the column where i've used this functionality.
the listbox will appear from the 3rd letter you type in those cells.

What i'm doing here is, on selected cell change event, i'm putting a text box on top of that cell. and filling the listbox based on the contents of that listbox.

Is there any other solution where i can avoid using the text box control inside a cell and i can capture the words as i type and get autosuggestions below that cell as a list?

Please remember this file would be used for data entry and hence i want to keep it data entry user friendly. :)
 

Attachments

  • Input file.xls
    631 KB · Views: 2
Hi,
I downloaded the sample file, and it would work perfectly for me, except, it cannot be copied, i can not import my own Excel spreadsheet into it and I am not able to copy and past into mine.. how should I go to be able to replicate it into my own Excel spreadsheet.

Thanks.

Olivier
 
Olivier W. said:
Hi,
I downloaded the sample file, and it would work perfectly for me, except, it cannot be copied, i can not import my own Excel spreadsheet into it and I am not able to copy and past into mine.. how should I go to be able to replicate it into my own Excel spreadsheet.

Thanks.

Olivier
Click to expand...
Dear Olivier,
try to change the VBA in it and try again.

Regards
Sinu
 
Hi ,

I think what you want done cannot be achieved using simple VBA ; when you start entering data into a worksheet cell , you enter EDIT mode , and nothing else can happen till you exit EDIT mode by pressing the ENTER key or any of the cursor keys ( including the TAB , HOME , END , PG UP , PG DN keys ).

If you are comfortable with heavier code , then see this link for more information :

http://www.codeguru.com/vb/gen/vb_s...Level-Keyboard-Hooks-with-the-Windows-API.htm

Of course , that article deals with code using VB6 , so you will have to see if all of it works in Excel VBA.

Narayan
 
You must log in or register to reply here.
Back
Top