Finding multiple entries

[anandvh]

Hi,

I am developing an inventory application wherein the users have to enter the items issued. Each entry can have upto 30 items being issued.

Due to the pecularities of our system, I cannot allow the user to enter the same item multiple times. Say Item "Orange" has been entered in row 1. Then I cannot have the user entering "Orange" again in the remaining rows 2 to 30.

For your information the check can be based on the item code which in column A. Also I do not need the check to take place on-line (that is as soon as an item code is duplicated - although this would greatly enhance the utility), but can also be done when the "Save" is selected.

Currently I am using the countif function hidden in another column to do this, but I find that this is not a very elegant method.

Any help will be greatly appreciated.

Anand

[Steffen Thomsen]

Put this in the sheets code module, whenever a user inputs something in a cell that appears elsewhere in same row, the user will be promted to type in a different value.

Please Login or Register  to view this content.

[smuzoen]

I would use a worksheet_change event - place this code into the sheet where values are entered - I am assuming the values are going into Column A

Please Login or Register  to view this content.

Anthony
“Confidence never comes from having all the answers; it comes from being open to all the questions.”
PS: Remember to mark your questions as Solved once you are satisfied and rate the answer(s) questions.”

[anandvh]

Thanks Steffen Thomsen and smuzoen for your prompt responses. Steffen, your code worked well. The only issue I faced was that the cursor appears in the same position. If this can be moved up one row, then the solution would work really well. I added

Please Login or Register  to view this content.

after the

Please Login or Register  to view this content.

line. Got the code to work just the way I wanted

Sumzoen : Also tried out your code. Had a couple of issues. The error message does not go away once the second instance has been deleted. Pressing enter again deletes the first instance.

Thanks to both of you, my application is now getting slicker thanks to all your contributions. Appreciate your contributions.

Anand

[anandvh]

I was trying out the code. Worked fine when the working with individual cells. But if I use this on merged cells, the statement

Please Login or Register  to view this content.

does not work. I get an error that merged cells cannot be deleted. Any solution to this ?

[Steffen Thomsen]

Yes, dont use merged cells

merged cells tend to mess up code.

[snb]

I'd prefer to give the user a list of valid items to choose from (you can use validationlist to do so).
It's not a nice thing to suggest the user did something 'wrong'.
Besides input will slow down drastically when errror messages are being displayed.
You should prevent that whenever possible.

[anandvh]

Thanks Steffen - I am redoing the layout of the worksheet without merging cells.

snb - thanks for the input. My first choice was to provide a validation list. However two issues prevented this. First, the inventory codes with the descriptions are stored in another worksheet used for masters. Second, a validation list would only provide the item codes which confuses the user if the descriptions are not displayed alongside. I could not find a solution wherein I display the code and description in a drop down box and then pick out only the code into the cell.

Any suggestions would be greatly appreciated.