is there any way to make data validation doesn't contain empty or duplicates items

[Mussala]

Hi guys

I have three data validations in OUTPUT sheet in columns B,C,D , linked with columns B,C,D for INVOICE sheet like this

=INVOICE !$B$2:$B$100,=INVOICE !$C$2:$C$100,=INVOICE !$D$2:$D$100

what I look for when shows any data validation should not show any empty and any duplicates items , also I want populate the items into data validation dynamically without increase the range manually when I need add more items more than row 100 for each column .

thanks

 

[Akuini]

It looks like the formula in data validation is the problem. The formula must be a List type, such as "=A1:A6"
Are you sure the data validation in those cell works?
Try a simple formula first, such as "=ATS1:ATS6"

 

[Mussala]

Are you sure the data validation in those cell works?

yes it works , it links with list in Table not in range .
based on your suggestion I can't change because create the list by code automatically without interfer from me .

 

[Akuini]

yes it works , it links with list in Table not in range .
based on your suggestion I can't change because create the list by code automatically without interfer from me .

Your formula refers to a string not a range.
Sorry, the add-in only works on data validation formula that can be evaluated as a range.
In post #1 you said that the data validation formula is like this:
=INVOICE !$B$2:$B$100
Does the add-in work on the cell with that formula?

 

[Mussala]

In post #1 you said that the data validation formula is like this:
=INVOICE !$B$2:$B$100
Does the add-in work on the cell with that formula?

sorry for that !

open many files and work on another code make me so confused

to come back your suggestion about the code , now it shows the form and works perfectly , but I have question

is there any way to make your form inside the clicked cell to show as in part of the cell instead of move the form in places are not relating of clicked cell ?

I mean should show as in right location in target cell as data validation in each cell .

also if there is form contains combo box I think no need data validation because the user maybe forget double click into cell then the problem still continues.

I know your form depends on data validation is existed, but I hope you find way like hide data validation when select cell as if data validation is not existed from the basic .

 

[Akuini]

is there any way to make your form inside the clicked cell to show as in part of the cell instead of move the form in places are not relating of clicked cell ?

Try this:

1. Open VBA window (press ALT+F11)
2. Open VBAProject Search_deList_v2.1.xlam
3. Open xDAV_1 code window
4. In "Public Sub GetPointCoordinates(ByVal cellrange As Range, ByRef pointcoordinates As pointcoordinatestype)", comment or remove this line:

Set cellrange = cellrange.MergeArea.Offset(, 1)

Question:
Does the shortcut Alt+Right works now?

 

[Mussala]

1. In "Public Sub GetPointCoordinates(ByVal cellrange As Range, ByRef pointcoordinates As pointcoordinatestype)", comment or remove this line:

It doesn't work after delete it . the same case.

Question:
Does the shortcut Alt+Right works now?

no I have to click inside the cell.

 

[Akuini]

Hm..,, wait, before you deleted the line where the userform appear?
It should be at the next cell of the clicked cell. Like this:

I double-clicked the cell (with text "back to the jury") then userform appeared at the next cell.

 

[Mussala]

in reality I use data from right to left sheet based on my language , doesn't work for my case.
but if I use left to right based on English then your suggestion works.
and the combo box can't autofit based on clicked cell?

 

[Akuini]

Ok, sorry I'm not familiar with right to left setting, so I can't amend the code to suit.
And I wonder if this right to left setting has something to do with the Alt+Right shortcut, I mean can you try by pressing Alt+Left instead of Alt+Right?

 

[Mussala]

Ok, sorry I'm not familiar with right to left setting, so I can't amend the code to suit.

don't worry

And I wonder if this right to left setting has something to do with the Alt+Right shortcut, I mean can you try by pressing Alt+Left instead of Alt+Right?

my apologies!
I thought when you said Alt+Right . I misunderstood
Right means right click . but now understood right arrow in keyword.
now it works whether the sheet is right to left or left to right by using Alt+Right
thanks very much for your tool and times .