searchable dropdown blocking blocking invalid data

Hi,

I created searchable dropdown in different columns. Currently I only use Application.Calculate in VBA. The dropdown is working but the macro should also alert If somebody fills invalid data into the active.cell. The alert should "Please select from valid data from dropdown list".

Can somebody help me to solve this problem?

Thank you,
BR,
Balazs

Can you attach your file? Please read the yellow banner at the top of the thread.

Hi,

I created the searchable dropdown list based on youtube video called "searchable drop down list in excel multiple cell"

This is a really good solution but one feature is missing. I want to block If somebody writes invalid data to the activecell within the dropdown column (e.g. The user filles "Devel" into the cell but the valid data is "Developer". In this case macro should pops up a message "Please select valid data from dropdown list"). So If the user start writing in the cell, the macro should not allow the user to leave the cell till valid data is not entered in cell (The user should be able to select from dropdown list or can enter the valid data which matches one of the dorpdown fields).

Thank you very much for your help,

BR,
Balazs

Well, without seeing your file I can't test it.

Hi Akuini, Sorry for the delay. Please find attached the file. I should implement this feature in A, E, J column in sheet "Sercha Dropdown in column A,E,J". Can you help?

This is a really good solution but one feature is missing. I want to block If somebody writes invalid data to the activecell within the dropdown column ... In this case macro should pops up a message "Please select valid data from dropdown list")....

1. Both feature are in "Error alert" tab in data validation, do you mean it doesn't work on your data validation set up?

2. Your file is password protected, I can't check the data validation.

Sorry, password is "Nemzetisport89".

"The Show error alert after invalid data is inserted" in data validation is turned off otherwise searchable dropdown function is not working. The feature is working but I want to block the user to leave invalid data in the cell when they are typing data in the dropdown cell. For example If the user wants to fill "A5" cell and they type "Project", they must fill the whole project name or select from dropdown list (e.g. must type or select Project2 or Project3). The macro should restrain the user to leave "Project" in the cell because this name is not in the dropdown list. In this case a message should remind the user to type valid data or select a name from dropdown list.

Ok, try this:
1. I created 3 named range:

Please Login or Register  to view this content.

2. I put this code in sheet "Sercha Dropdown in column A,E,J" code module.

Please Login or Register  to view this content.

The file:

I can't reproduce the error, but in the mean time try changing "Union" to "Application.Union"

Please Login or Register  to view this content.

if the problem persists, I will try something else.

I have changed the code in the excel but the erro still appears. I am enclosing the snip about the error.

If you don't have any data below the table in sheet "Sercha Dropdown in column A,E,J", try replacing this:

Please Login or Register  to view this content.

with this:

Please Login or Register  to view this content.

Note: 10000 is the possible maximum row of the table, you might change that to suit.

Now I get "Error number 50290: Method 'intersect' of object'_Global' failed"

Hm, I still can't reproduce the error, so it's hard to debug. So I think this approach isn't suitable for your project.

As an alternative, in case you're interested.
I've developed a searchable combobox that can function as your searchable data validation, and maybe even better.
You can try it. Let me know if you're interested in this method.

It would be better if you start a new thread because it will attract more helpers to try to find a solution to your problem and also this is a new problem.

Hi Aukini, I have started a new thread but nobody replied. Do not you have any idea how I can solve this issue? I really appreciate your support.

https://www.excelforum.com/excel-pro...ml#post5267606