Auto predict Combobox

We can give you a answer when you have enclosed your code with 'code tags' as required by rulle #2.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

Thanks torachan for advising how I put in the code tags.
Looking forward to learning from others

I had a brief look - more thought needs to be applied to establish what you are trying to achieve.
The 'Client' validation is an O.K. approach as you are just ensuring single elements apply.
However using data validation on individual elements of a multi-column/multi-choice table is not very productive.
The table needs ordering by priority sort (key column first followed by filter order).
Using data entry UserForm/VBA would allow you to produce an application that would be free of on-sheet formula and far easier to maintain.
Attached is a file I use to demonstrate dependent comboboxes (only the relevant data to combo1 passes to combo2 etc., etc.,)
Just a small tip when creating & populating tables, do not extend them beyond the last row/column of data, always separate each table by at least one blank column.(this prevents any remote likelihood of one table interacting with its neighbor - especially when you are filtering or sorting a table.
The method of overlaying data validation with combos is more of a novelty than of any practical use.
torachan.

Thanks for your reply and hints!! I haven't used user-forms before so an interesting concept. I am also very new to vba (this is my first attempt)

The reason I was interested in the combobox was to be mainly to able to autopredict the answer based on the assigned data validation list. As the number of options increases it was taking more time to scroll and select the correct option. Also the font was small to read.

The 2 columns I would ideally like auto-predict and larger font is the "Client" and " Description" column as there can be long data validation lists. I agree I don't need the autopredict on the "Item" column as it is a dependent list and there are only a few options.

Is there a way to get user-forms to auto-predict from a set list (that is more extensive that what is in the table where the data is being added)? In your example, the user form options are based off the same table only and I'm not sure how to use it to add new data to the table.

Thanks!

A demo quickly thrown together incorporating your validation table.
Just use the comboboxes in order (top to bottom) then 'SUBMIT' the data.
That is just a small illustration of how clean UserForm data entry is (note: no on sheet formula to maintain)
Your validation tables are 'elastic' (add/remove data the code will know the size of the tables).
torachan.

Thanks for your answer. I do like how it is set out!

follow-up, new file attached and annotated code listing.
while testing after submission I was presented with a 'fluke' I had not encountered/anticipated in using Excel for 35 years (spreadsheets for 50+ years).
The method I use for cascading combos entails the use of splitting arrays, in one of the columns of data the user had used 'commas'.
The nett outcome had me 'scratching extremities' for what appeared to be an eternity before the 'penny dropped'.
So proof that one is never too old to learn. So use anything as punctuation as long as it is NOT a 'comma', I have replaced them with '-'.
torachan

Thanks torachan for the effort you have put in! I was eagerly awaiting your post and now will go study it!

updated file - adds funder to label caption for reference.
type in first combo should bring up valid names - funder ref will clear if client is not valid.

I have attempted to work the vba code into my spreadsheet. The red background column headings are what is included in the vba code. I am hoping to combine the vba code for the userform and my pre-exisiting excel formulas (otherwise I would need a 30 item userform).
Can the userform vba run alongside the excel formulas in the cells that are left blank in the vba?

It is coming up with error 9 - out of range which I haven't worked out where it's coming from. But I am hoping I am on the right track and not gotten myself bogged?

you are making the process far too complex - there is no need for a single formula on your sheets.
the default data is already present - it is simple process to filter that - there is no need for 30 more items on the userform
coding will perform the operation all that is needed is an illustration of what needs to be saved where - put headings on you columns and dummy data in the cell to show what you need.
putting it 'politely' your tables are a mess, they need to be ordered - no blanks - no missing rows - no missing columns.
the error is caused by trying to load a table that does not exist - you have altered all the referencing and you have no sheet3 in your file - look at my original.
If you want me to take a second look, the first step would be to extend the headings of my Client_Treatment sheet so that I know what you want to record, once I have that I can show how it is possible to record your data without using formula or extra fields on the userform.

Fair call re the complexity and mess! After playing around with the code and simplifying my spreadsheet the code is working well. Thankyou!!

My only question now is:
Is there a way to link the label "Funding Reference" to automatically populate ComboBox1 FUNDING?
If I am asking others to manually populate the FUNDING combobox, I can see errors occurring with the wrong option selected.

Or if I change ComboBox1 FUNDING to a label as there is only one correct answer, is there a way I can link the new label to ComboBox2 DESCRIPTION? There will still be multiples possibilities here.

Thanks!!

If your default list that loads ComboBox1 is setup as per my example file there is noway that anyone making choices using the series of ComboBoxes can make a wrong choice.
In the file attached I have overlayered the comboboxes with a label - this prevents anything been typed into the combobox so the choice is only that from the default list.
If still unclear post your file and I will edit it to demonstrate as required.
torachan.

Thanks. I see what you are doing. I'm not so much worried that they will write the wrong thing. But they will select the wrong thing from the list. I also want to automate as much as I can to make it easy.4.jpg

see if the attached does what you want.
there is a full width label covering the funding combobox.
the value from this combobox forms the start value for loading the filtered list to the next combobox.
as there are payer in the client list that have no funder cross reference an error message box appears and no entry can be made until corrections made.
torachan.

Perfect!!! Thankyou