VBA - Preventing duplicate entries using a macro

[princess]

I have a spreadsheet containing bookings for a cinema. There are 5
different types of seats available (Normal Adult, Child, Member,
Student and Senior Citizen) (See attached file for screenshot of
worksheet).

I need to create a macro (I am not allowed to use the Validation
function) to check that each seat type has been entered in column E
(cells E16 to E20) only once, e.g. only one entry for Member.

If duplicates have been entered, a message box should appear informing
the user of this, and then the duplicate seat types (and their
corresponding quanties in column G) should be deleted.

I need to create this using a simple Excel macro (e.g. something like
an IF statement or similar).

If anyone could help I would be extremely grateful. I have been trying
for hours with no success.

Thanks in advance!

Attachment filename: princessexcel.jpg
Download attachment: http://www.excelforum.com/attachment.php?postid=416046

 

[Bob Phillips]

Not a simple IF function, but this worksheet event code will trap the input
and validate it

It goes in the worksheet code module.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("E16:E20")) Is Nothing Then
With Target
If .Value <> "Normal Adult" And .Value <> "Child" And _
.Value <> "Member" And .Value <> "Student" And _
.Value <> "Senior Citizen" Then
MsgBox .Value & " is an invalid value"
.Value = ""
ElseIf WorksheetFunction.CountIf(Range("E16:E20"), .Value) > 1
Then
MsgBox .Value & " already used"
.Value = ""
End If
End With
End If

ws_exit:
Application.EnableEvents = True

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[princess]

Thank you very much for your help!

The only problem is that I have to run the macro from inside of anothe
macro I have already created.

This macro (called Transfer) transfers data from Sheet 1 (InputForm) t
Sheet 3 (Records).

The validation macro has to be run before the macro that transfers th
data, and has to be excuted when the user clicks on the "Transfe
Booking Details" button on the InputForm worksheet.

How can I put the new code you have told me so that it runs before th
other macro?

(I have attached the file I am working on so you can see what I a
talking about!)

Thanks again

Attachment filename: oneblokesam.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=41607

 

[Bob Phillips]

Might I suggest you modify the design?

You say that you need to run this new macro from within transfer. My
question, is why? If you do it there, the invalid data will already have
been created, and all you are doing is forcing them to go back after they
think they have finished. Not good design.

My solution checks the data as it is input and stops invalid data being
entered. Therefore, when they hit the transfer button, you can be assured
the data is valid, and so you don't need to test for it.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[princess]

Sorry my mistake, you are right!

Now I have understood where the code goes (on Sheet1), however, when
enter a duplicate type onto the worksheet, there is a VB error.

It says End With without With, even if there is a With!

(I have attached a pic of the VB window with the code and error).

Do you know why this is ocurring? Is it something I have done?

Thanks!

Attachment filename: vberror.jpg
Download attachment: http://www.excelforum.com/attachment.php?postid=41610

 

[Bob Phillips]

The problem is that one of my lines of code has somehow got split into two
lines.

This is what you have

Else
If WorksheetFunction.CountIf(Range("E16:E20"), .Value) > 1

whereas it should read

ElseIf WorksheetFunction.CountIf(Range("E16:E20"), .Value) > 1

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[princess]

Now it all works!!!

Thanks very, very much for your excellent help - you have saved me
hours of going insane and screaming at the computer!

Thanks again,
Princess