Accept on condition only !

[J_J]

Hi,
I want to use Input Box's to enter data to cells C2:C, D2;D and E2:E row by
row for Sheet1. But I need to check if the info I input for cells (C2:C AND
D2 both) does not exist in the region of B4:AY14 on Sheet2 (where the rows
represents info similar to C2:C and columns represent info similar to D2
on Sheet1) !. Hope I summerized it correctly.
How can I do it?. (With and without VBA)
TIA
J_J

 

[J_J]

Hi,

Sorry about my mistakes in the previous post. I think I wasn't very
sucessful giving a

clear view of my problem. Here is a much clearer description of my excel
problem...

-----------

I want to use Input Box's to enter data to columns C2:C, D2;D and E2:E row
by
row on Sheet1 (where the info on column C, D and E will hold the data for
"period",

"activity" and "trainer" respectively).

On Sheet2's A4:A column, I have a list of all possible "tariner" values of
Sheet1.
On Sheet2's B3:AY3 range, I have a list of all possible "period" values of
Sheet1.

Lastly on range B4:AY21 of Sheet2, I use VLOOKUP to display the "activity"
cell info for

the matching "period" and "trainer" values retrieved from Sheet1.

So far so good. No problems. I can manage that.

But before actually accepting and "writing" the data into the columns of
C2:C, D2 and

E2:E as rows of new elements on Sheet1, I need to make sure that the info
I'll input on

Sheet1 for each row of cells ("period" & "activity" & "trainer" values)
there doesn't exist

another row with the same data trio.

Hope I summerized it correctly this time.

How can I do it?. (With and without VBA)
TIA

J_J

 

[mangesh_yadav]

In the module for sheet1, you can write the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column >= 3 And Target.Column <= 5 Then ' 3 to 5 stand for
to E columns

For i = 1 To Target.Row - 1

If (Cells(i, 3) = Cells(Target.Row, 3)) And (Cells(i, 4)
Cells(Target.Row, 4)) And (Cells(i, 5) = Cells(Target.Row, 5)) Then
MsgBox "Duplicate record"
End If

Next

End If

End Sub


- Manges

 

[J_J]

Thanks mangesh_yadav,
I'll try this and get back to this thread for comment.
J_J

 

[J_J]

Hi mangesh,

Thank you so much for your code. It works perfectly OK.
I need just a little bit further then this to make it even more effective.

Your code reminds the user that a "duplicate recored" has been entered.
But I think that it will be even better if the code "removes" the entered
duplicate row as well when we click pass "OK" the message box.

A second (and much more needed) improvement to your code may be this:

Can we add another "If" statement to your code so that when we input the
"period" & "activity" & "trainer" values it will also check columns 3, 4, 5
and if it finds that there is a PARTLY match (only the "period" & "activity"
data matches but "trainer" data differs) then the program will warn the user
by displaying a message box with the message "The " & activity & " lesson on
" & "period" & "is beeing used by " & <The found unmatched trainer name> ?

"Should I remove the old record and put this new data line to the sheet
(Y/N)?"
And if we enter "Y" OR "y" it will do so, but if we enter anything else it
will keep the old record and delete the lastly entered partly matching row?

Hope I have not asked too much

Regards
Sincerely
J_J

Thanks mangesh_yadav,
I'll try this and get back to this thread for comment.
J_J