Duplicates from tab/space(s)

[XLVBA]

Background:

I have a workbook with a userform that we use to keep track of inventory. About 20+ people have access to open the userform and enter an asset "number". The userform then looks to sheet and if it finds a matching asset number on the sheet it will populate the other fields on the form. The user can then make changes and resubmit the information, at which point it will update the existing asset row information with the new info. If it doesn't find a matching asset number, the other fields will be blank and the user can fill in what they have and when they hit submit it will create a new row.

We have people using both Excel 2003 and 2007.

I am using the following code inside the CompAsset.text field to prevent them from using a space:

Please Login or Register  to view this content.

Problem:
Somehow, from time to time, as the user is entering in the asset information and they try to tab to the next field it actually puts a tab within the text box. Othertimes, despite the code above, they manage to get an extra space or spaces at the end of the asset. These extra spaces cause it not to find a match on the worksheet and once the user hits submit a new record is created and we end up with duplicates that usually have different information. The users often times don't realize this has happened and just assume that the record didn't previously exist.

I unfortunately can't limit the number of characters the asset textbox can have, nor can I restrict the field to only allowing numbers.

I'm not sure what else you might need to know, but any help solving this would be greatly appreciated!!

Thank you!

[JBeaucaire]

You can remove that separate code and create a CompAsset_Change macro that triggers automatically each time the CompAsset field is changed. That macro can handle all the stripping for you in realtime, making it unnecessary to do it later.

Please Login or Register  to view this content.

This actually works as they type... so pressing the spacebar will not even result in a space being seen in the textbox.

[XLVBA]

I will give this a try. After adding the option explicit some of it errored out because I had variants that weren't expressly defined, so I'll have to go back and add them in before the users can try it out.

I don't know what caused tab not to tab into the next field before but hopefully it won't matter and this will take care of it.

Thanks!

[JBeaucaire]

Option Explicit should be turned on automatically at the top of all your modules. THere's nothing more frustrating than hunting 100s of lines of code looking for a typo you missed. Option Explicit points out those errors for you.

[XLVBA]

That is a good idea. I will have to do that.
I just rolled this out, so we'll see if that helps. But it does work like it should now. Thank you!

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

[watersev]

hi, XLVBA, another option to use is:

Please Login or Register  to view this content.

[JBeaucaire]

Watersev, how does that stop typing non-alphanumeric characters? !@#$%^&*()...