The article work around is trying to fix a problem I’m trying to get. I don’t want the drop down list to auto complete but allow me to scroll the list and make a selection using the keyboard arrow keys and enter key.
My File: Currently, using the arrow keys the combo box will only scroll to the first name on the list then this name is auto select it. The only method to scroll and make a selection from the list is by using the mouse the opposite of what I want.
Type “co” and any name that has “c” and “o” are listed. The first name in the list is “Brown Company” which is auto selected when arrowing down. The only way to select a different name from the list is to enter “co” and use the mouse to make a selection. I want to scroll the complete list using my arrow keys.
The file for the question… “Searchable drop down” has several combo boxes and it is possible to arrow down in any combo box and make a selection which is the action I want. But, my text narrator program acts real strange. Either very delayed or will not speak the text. Maybe it is the Dynamic action of the combo boxes, I don’t know for sure just guessing.
Using a text narrator my process is a little slower than reading as I must allow time for the narrator to speak the text for the correct cell or list item.
Maybe it isn’t possible to enable use of arrow keys in a combo box and use a text narrator. If so, I can work with using the mouse.
Why Me (rep: 4)
Apr 29, '20 at 12:12 pm
OK. Very clear :-) Give me a few days' time to look into it.
Variatus (rep: 4889)
Apr 29, '20 at 8:19 pm
I want to make major changes to your setup and want your approval before I spend time on them. My need is for an alphabetically sorted accounts list. That list exists in your sample in Accounts!A2:A11. I want to add code to run when you enter a new name at the bottom, to sort the list and include the new item in the named range that will feed drop-downs. Please confirm that a sheet of exactly this nature actually exists in your real project and that your system doesn't require this database to have an order other than the one I want to give it. The alternative would be to keep your list where and how it is and create an automatically maintained copy of the accounts name list in a hidden sheet.
Variatus (rep: 4889)
Apr 30, '20 at 9:44 pm
Yes, a real list does exist and is sorted as you state. The current list has over 400 entries and new entries are added at the bottom then sorted in “column A”.
The reason for the summary sheet is twofold; easier to search and isolates the account information so navigating the retrieved information is easier and less of a chance for me to stray off a cell. Whereas before creating the Summary I scrolled (arrow keys) column A then arrowed right and listened to the narrated text and hope I haven’t by mistake dropped up or down a cell or I have the wrong information. And, this has happened.
Question, can whatever you are considering be transferred to a form? At some point, I want to attempt to create a form with add / search / update account information capabilities. It is possible my narrator will not work with a form such as this and the reason I want to continue the current project before attempting the form project.
Thank you very much for all your help
Why Me (rep: 4)
May 1, '20 at 9:59 am
Aah! That's very good. Then I can proceed from the assumption that the list already exists. I'm thinking of a text box which reacts to each letter you type, as you type it. It loads a list box from said list, starting from where the typed letters indicate. In the list box you can navidate with arrow keys. The pair could be on a worksheet or a form. If the Summary sheet you gave me is the "form", the text box would take the place of the current Cbx and the list box would open on the right.
Variatus (rep: 4889)
May 1, '20 at 8:17 pm
Sounds like a good plan. I enter search text then arrow to a desired account name, tap enter and the account information populates to the other fields... fantastic. Question, can additional columns of information be added easily? Also, I assume any updating i.e. adding additional information to the “Notes” field must be added on the Account sheet?
Thank you very much for all your help,
Why Me (rep: 4)
May 2, '20 at 9:22 am
I tried the search box as is and did not copy to my file. Entering the search text and how it populates is fine. However, 3 issues, I cannot scroll using the arrow keys, the search text entered does not populate the correct account names and received an error.
Question, when you search and the table populates can you use the arrow keys to scroll up or down the account name list? At first I was beginning to believe the narrator program was causing the arrow key issue but I’ve tested the file on two PC’s with different versions of Excel and still have the same issue.
Regarding forms, yes, I remember forms like you mentioned when I used Access many, many years ago.
Only need to search by one field, this will keep me out of trouble.
Reviewed the TX form videos and downloaded the files. The issue seems to be the “buttons”, the narrator will not speak the button name. I imagine the button has ALT Text field but the text has not been added. I will check this out more once this project is complete.
Why Me (rep: 4)
May 4, '20 at 2:34 pm
First, let's get my sample file to work for you. When you open the workbook click in the TextBox. Type any letter. The ListBox will update to a list starting just before the letter you typed. You say this doesn't work. How? it's very simple code and works perfectly for me. The macro will preselect the second entry in the list. I expect your narrator not to be aware of this and it doesn't matter. The idea is that you should move the cursor to the ListBox. For now you have to click. In future we can use a key. Now I expect the narrator to start reading the list. Move up and down with arrows. If you don't like the list you can go back to the TextBox (click, for now) and add more letters. Every change will modify the list. I added more names starting with G to enable a demonstration of this narrowing. You say the ListBox doesn't populate with the correct list. That seems physically impossible because it picks the first one followed by the next seven and the list in the sample is correctly sorted. You also say you "received an error". There isn't enough code to have an error, I'd say. But that's exactly what makes it intreaguing. What error? What did you do to produce it? It looks like I should have explained the concept better. Please try again.
Variatus (rep: 4889)
May 4, '20 at 8:33 pm
Sorry, I didn’t think of trying to click in the list then use the arrow keys. I thought I could just use the arrow keys. Yes, when I type “W” in the text box the list to the right does display a list. If I use my mouse and hover over each name my narrator will speak each name in the list. If I click on the list I can use my arrow keys to scroll the list but the narrator will not speak any name in the list.
When I entered “S”, the list populated with… Red, White & Yellow companies which was odd. Checked the Account list and no company started with “s” so assume that was the issue. Close / Reopened the file and entered “W” and the list displayed… red, white & yellow companies again.
At first I thought the error was caused by the need to “enable editing” in Excel and the security warning about internet files as the error did not happen again. But today, I received the error again and the security warning did not appear when opening the file. I took a screen shot which I can upload if you want. The error was Run-Time Error 13, Type Mismatch. I received the error after I entered “w” then backspaced and entered “a”. Could not Continue had to end the process.
If it makes it easier for you I can give you a link for the narrator I use so you can give it a try. I did not include it here as not sure if I should or not.
Why Me (rep: 4)
May 5, '20 at 1:01 pm
Yes, the list will start at one item before the one you indicate in the text box. If you want "S" the first item will start with R regardless of whether S exists or not. That's why the macro pre-selects the second item.
When you download a macro-enabled workbook Excel won't open it until after you acknowledge the security warning. This feature will be disabled after you save the file to your computer.
The error you encountered seems to be caused by the back-space. I must remember to fix that.
Sorry, this site only allows Excel files to be attached. You couldn't upload a screenshot here unless you embed it in an Excel workbook.
If the Narrator is free to download and use it might come in handy - but not so handy as to make it worth while stealing :-)
The big question now is whether the system can serve your purposes and meet your needs. Is it what you had in mind?
If so, how do you want to deploy it? On a worksheet? In a user form? I can shape it up as a kit that you install. But it would be slightly different depending upon its final destination.
Variatus (rep: 4889)
May 5, '20 at 9:44 pm
Error only happens when entering “a “or “z”. Doesn’t appear any other letter causes the error. The narrator is free, the company is donation based. https://www.nvaccess.org/ . Question, does this fit my needs. Yes and no… My original two goals; (Goal 1) ability to scroll the list with the keyboard arrow keys and have my narrator speak each entry so I could make a selection. (Goal 2) Enter “g” and only those entries that start with “g” are listed and enter “ga” and only those accounts that start with “ga” are listed. At this point, since the narrator will not speak when scrolling with the arrow keys the project has moved ahead but the main goal is not working. So whether we continue to work on this is really up to you. It seems really strange hovering over an account name with the mouse the narrator will say the name but use the arrow keys and the narrator will not speak the name. Plus the file you created in my previous question Searchable Dropdown” used a Dynamic combo box and the narrator would speak the names as I arrowed up/down but the speaking was much delayed or spoke the text from the wrong name. I know you must have more important projects to work on than this one so if you want to stop now I totally understand. I plan to send this test file if ok by you to a contact I have at the narrator company. Maybe they will have an explanation or maybe a fix.
Why Me (rep: 4)
May 6, '20 at 11:17 am
Yes. It looks more and more like programming for the narrator. Perhaps they can give some input. You now have two possible directions of development. You should know which one looks promising before you can continue.
Variatus (rep: 4889)
May 6, '20 at 8:07 pm
I received a reply back from the narrator company. A summary of his response….“Problem the form fields are not accessible. Another recent case had a similar issue and discussion with Microsoft, their response was don’t use form fields. If you swap those form items which aren't accessible, with regular formulas in cells, you can achieve the same results in an accessible spreadsheet. You can extract a list of unique company names from your data with a formula such as on (web site)” and you can then use a standard data validation list to populate a drop down for the user to choose from.” (I did not list the site since I don’t know if listing other sites is appropriate, if ok, let me know)
I reviewed and downloaded the file for Teach Excel “Searchable Dropdown 2”. Even though the file made Excel act strange I was able to click in the search box, enter text, scroll with the arrow keys and the narrator program worked ok. Occasionally, the narrator said “blank even though a name was listed. I don’t know what this means in conjunction with all the work you have done regarding this project but I thought I would throw it out.
Thank you
Why Me (rep: 4)
May 7, '20 at 1:03 pm
It means that we should take another look at the normal Data Validation drop-down which, I believe, we started out from. Why was it discarded? So, now they would have us reload the list when you type a letter. I suspect that the list will close when it's reloaded (no way to keep it open - probably). But when you click to open the drop-down you ought to be able to scroll down with arrow keys and the nerrator promises to call out the names. If that's an improvement over what you have, let's give it a spin.
Variatus (rep: 4889)
May 8, '20 at 12:24 am
Ok, I say let’s start spinning, I believe we will get traction sometime soon. Ideally having the ability to enter a letter(s) and the list populates with any name that starts with those letter(s) then scroll with arrow keys and listen to the narrator speak each name is the goal. But if every time I enter a letter(s) I must open/reopen the list... I can live with that. I am a keyboard user and I assume using the ALT key would open the list box. What do you need from me?
Why Me (rep: 4)
May 8, '20 at 11:27 am
Nothing for now. I'll post a second answer. However, on the medium term I would like to return to the Q&A format of this forum so that what we talk about can be followed by others who are interested in the topic.
Variatus (rep: 4889)
May 9, '20 at 12:23 am
I know I probably did something wrong but I can’t get it to work. I used the file I uploaded to TX and did the following…. 1) Summary Sheet: Removed the combo box in BE and all code. 2) Copy and pasted your code into the VBA for sheet 1 Summary sheet 3) Changed “Const ValidationCell F3 to B3. 4) Add-On box opened, clicked OK 5) Save the file but the code will not work for me. I tried enter, tab and arrow but nothing. I also checked if any additional references to F3 should be changed to B3 but only one found. Very anxious to give this a try. Thank you so much.
Why Me (rep: 4)
May 9, '20 at 1:50 pm
Good morning, Don't know what "Add-on Box" is but it shouldn't be part of my system. For the reest of it all is fine. Did you specify the sheet with the accounts list, here? Set WsList = Sheet2 ' or specify like =Worksheets("Accounts")
. After that, all you should do is start typing in B3. Nothing will or should happeneven then until you press Enter.
Variatus (rep: 4889)
May 9, '20 at 8:03 pm
I don’t’ know what I’m doing wrong but still can’t get it to work. I’ve changed the following per your instructions… Const ValidationCell to B3 and changed the line Set WsList = Worksheets (“Accounts”). I also tried the following… 1) created new file with Summary and Accounts sheets 2) Copied all the Account Name List to “Accounts” 4) Added VBA code to Module 1 5) Save as XLSM and tried again but will not work. I don’t’ know if this is the issue but in my Accounts sheet the first column is Account Names and I could not find a reference to the column name in the code. I also renamed the column to Account List but that did not work either. What am I doing wrong?
Why Me (rep: 4)
May 10, '20 at 11:57 am
Tongue in cheek, you are becoming quite proficient at this project, I'm happyo to see. On a more somber note, I apologize for the unnecessary labours I have caused you to undertake. The mistake wasn't yours. Instead, it was here:
[CODE] ValRngName = ValObject.Formula1
IsNew = CBool(Err.Number)
If Not IsNew Then
If GetAccListRange(AccListRng, ValRngName) Then
/CODE]First and last line are for orientation, the two lines in the middle are new/changed. I have also updated the entire code in my above post.
The problem you encountered resulted from there being no previous validation in the target cell. The code provided for that but the provision wasn't correctly wired up because this case never arose in my trials. Now it is.
You can set the column for the accounts list in the function AccList. Having the wrong column shouldn't be a major problem. The list should still get created but it would show data from the wrong column.
Variatus (rep: 4889)
May 10, '20 at 9:07 pm
LOL, I don’t know about being proficient but I am learning which is good and no apology required. Once we get this working then we must have a virtual drink, the first one is on me. I used the file I originally uploaded and added the new code and change F3 to B3, = worksheets(“Accounts”). I also verified Column A is named Account Names and the list was sorted. But, I get Compile Error and Syntax error. The debugger stops on… Private Function AccList(ByVal SearchFor As String, _ WsList As Worksheet) As Range. That is where being proficient ended.
Why Me (rep: 4)
May 11, '20 at 11:47 am
Yeah, we should be entitled to a drink or two once we got this going. The fault seems to be here 'Set WsList = Worksheets("Accounts")'. But to be on the safe side I am attaching my own workbook now. Sorry, I thought we wouldn't need it.
It has two versions of exactly the same code in it on the Test2 and Summary sheets. One is pointing at F3, the other at B3. Both use the the accounts list in Sheet2 (Accounts). There is no need to name the range containing the accounts list. The macro finds it in column A of the specified sheet, starting from the row set in the code (presently 2).
Variatus (rep: 4889)
May 11, '20 at 8:07 pm
I am very anxious to download the file and give it a try. But, I can’t find it listed anywhere to download under this question area. I can only find the original file I uploaded a TXL 200501 002 Combo List Box which I know is a previous version. I know I miss a lot of text and files when listening to narrated text so can you give me a location.
Why Me (rep: 4)
May 12, '20 at 9:22 am
Maybe I'll need that drink you were waving at me early than planned. It seems my attention is slipping but glad to note that your's isn't. :-) I have changed the attachment.
Variatus (rep: 4889)
May 12, '20 at 9:42 pm
Oh, my mind is on a slippery slope, just one step from forgetting everything. I had two drinks before reviewing the file. The function of the search box works well, Enter one or two letters and the list populates with the most relevant account names at the top (prefer to only show most relevant but can live with this). Can use arrow keys to scroll which is great but my narrator does not speak any names in the list while I scroll? It continuously speaks the name in the search field. For example, enter “gr” and the first is “gray company” and that name stays in the search box until another name is selected but every name scrolled says “gray company”. Also, hovering the mouse over a name that name is not spoken. It seems the only way to get the narrator to speak a name in list is for each name to become active in the search box during the scroll, if that makes sense. Ok, I don’t know how or what to do so I will leave it up to you.
Why Me (rep: 4)
May 13, '20 at 10:50 am
Big disappointment! I thought we had it nailed.
First question: The number of items in the drop-down is adjustable in my code. How many would you normally need to find the name you want?
Second: If the validation drop-down doesn't work we should make our own. Just fill 3 or 4 cells and put the cursor in the first. As you move the active cell down the Narrator would speak. Then find a way to return you to what you're doing. But that's very similar to a list box. Do you remember if the Narrator called out the selected items in a list box?
Third: If you like, you can now inform your friend at the Narrator that his gadget doesn't read the selection in a validation drop-down. It only reads the selection after it was confirmed. It seems he didn't know that.
Variatus (rep: 4889)
May 14, '20 at 3:47 am
Yea, I was disappointed as well but in my world stuff like this happens all the time but must persevere. Answers…
1) Tough question, that depends upon the letter(s). If the list was 8 or less but continued to scroll thru over that number or any number visible then 4 or 5. If can’t leave as is.
2) I reviewed all files created during this process plus those I downloaded from TX. TXL 171212 Drop-down List (1) this seemed to work, click on the box and use arrow keys and narrator worked plus hover over with mouse and narrator worked. It seemed to be a little quirky but did not try searching just clicked the dropdown arrow and scrolled with arrow and mouse.
3) Hard to say, I may update him but being blind and Using a narrator is difficult enough and this company provides a free narrator that for me has been a blessing So I won’t give him too much of a hard time about his gadget not working in all scenarios especially working in something as complicated as Excel.
What I thought would be an easy fix has turned into more of a project than I ever imagined. So I really appreciate your willingness to continue…. Thank you.
Why Me (rep: 4)
May 14, '20 at 1:40 pm
Good morning,
You didn't send me an email. Was that an oversight? You can set up an incognito account for the purpose.
I'm surprised you consider a longer list a bother. You only have 400-odd accounts and you should know whether you need to enter GRA or just G is enough. Therefore the target name should always be near the top and if it isn't you can always go back and refine the search.
TXL 171212 looks like a very old file (December 12, 2017). Could it be that your narrator mis-read the date? What I really want to know is if we tried list boxes. I remember giving you a flying combo box which was too fast for the narrator. But did we actually try a list box?
Variatus (rep: 4889)
May 14, '20 at 9:11 pm
Hi, I didn’t realize you wanted me to contact you via email, no mention in your previous post. That is true, I only have 400+ accounts to search but we are adding more all the time and I try to anticipate future growth. Yes, I understand for you it seems to be a simple task and easy to remember when searching thru over 400 accounts whether to use one or two letters but not so for me. If I had vision I wouldn’t even had asked this question in the first place. To answer your question, I don’t believe we tried a list box and yes, the “flying combo box” was hard for the narrator to function correctly but the arrow keys did work. The narrator isn’t a perfect solution for blind folks but maybe someday a really smart person will design a better solution. Yes, I understand the file I described in my previous post was an older file but I used that as an example just in case it helped you so we didn’t have to reinvent the wheel.
Why Me (rep: 4)
May 15, '20 at 12:25 pm
ok, I sent you an email.
Why Me (rep: 4)
May 16, '20 at 12:20 pm