Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Preventing duplicate entries using a macro
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 --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Preventing duplicate entries using a macro
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 " wrote in message ... 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 --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Preventing duplicate entries using a macro
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 -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Preventing duplicate entries using a macro
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 " wrote in message ... Thank you very much for your help! The only problem is that I have to run the macro from inside of another macro I have already created. This macro (called Transfer) transfers data from Sheet 1 (InputForm) to Sheet 3 (Records). The validation macro has to be run before the macro that transfers the data, and has to be excuted when the user clicks on the "Transfer Booking Details" button on the InputForm worksheet. How can I put the new code you have told me so that it runs before the other macro? (I have attached the file I am working on so you can see what I am talking about!) Thanks again! Attachment filename: oneblokesam.xls Download attachment: http://www.excelforum.com/attachment.php?postid=416077 --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Preventing duplicate entries using a macro
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 -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Preventing duplicate entries using a macro
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 " wrote in message ... Sorry my mistake, you are right! Now I have understood where the code goes (on Sheet1), however, when I 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=416106 --- Message posted from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Preventing duplicate entries using a macro
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 --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Code macro for duplicate entries | Excel Discussion (Misc queries) | |||
Macro for checking duplicate entries | Excel Worksheet Functions | |||
Preventing Duplicate Cells | Excel Discussion (Misc queries) | |||
Preventing Duplicate Entries within a column | Excel Discussion (Misc queries) | |||
Preventing Duplicate Entries in rows | Excel Worksheet Functions |