data validation

[nanobuns]

Hi,

A pleasant day!

I need anyone's expertise.

I'm using data validation to prevent duplicate entries. I'm using the below codes. I have a form where i input the data to a cell once i click save the below function will be called.
I have multiple criteria range that needs to be satisfy.

Public Function dup() As Boolean

    Range("C15:J38").Select
    With Selection.Validation
    .Delete
      .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=countifs($C$15:$C$38,C15,$I$15:$I$38,I15,$J$15:$J$38,J15)<=1"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
      
dup = True
  
    End With
End Function

sorry i can't upload the file it exceeds the limit.

please help.

Thanks in advance.

nano

 

[Hui]

Nano

Without testing this Data validation doesn't apply retrospectively to cells contents

That is You need to have the validation added to the cells before you put the data in, not after, which is what i think you are trying to do

 

[nanobuns]

Hi Hui,

I did put it before but the problem is it won't let me input anything even its not duplicate.

Thanks,

Nano

 

[NARAYANK991]

Hi ,

I would like to ask you - if , as you say , the dup function is being called when the data entered on the form is saved to the worksheet , why do you need data validation at the cell level ?

Why can't the function do the data validation , and either prevent the data save or allow it ?

Narayan

 

[Hui]

Your existing DV Formula allows me to enter any value once, then restricts me entering a duplicate

But,

If your entering data into Columns C, I & J you only enter it into one column at a time
I think your DV formula should be separate for each column
ie: C15:C38 =countifs($C$15:$C$38,C15)
same for other columns

 

[nanobuns]

Hi Narayank,

it will call the dup function before saving to the worksheet.

nano

 

[nanobuns]

Hi Hui,

But if i'll do that once the first column has been satisfied it will stop from executing the next condition.

Nano

 

[nanobuns]

Hi,

I attached a snapshot of one of my sheet. What i want is to restrict user from inputting same dates the same SAPID.

Hope anyone can help me.

Thanks,

nano

 

[NARAYANK991]

Hi ,

I can help provided you upload your file.

I assume the SAP ID is a reference to the employee ; the validation that is required is not one of same dates ; assuming that an employee can apply for more than 1 day's leave , you need to check that there is no overlap between one set of start date & end date and another set of start date & end date for the same employee.

Narayan

 

[nanobuns]

Hi Narayan,

I can't upload the file its too big.

Anyhow for inputting a data im using a userform.

Thanks,

Nano

 

[NARAYANK991]

Hi ,

Since this forum cannot accept files which are bigger than 1 MB in size , what you can do is upload your file to a public file-sharing website such as RapidShare , SpeedyShare , DropBox , GoogleDocs , SkyDrive , get a share link from the website which will allow others to download the file , and post that link here in this thread.

Narayan

 

[Hui]

Or save the file to an Excel Binary File type *.xlsb
or Zip it up using WinZip/WinRar etc