Add search function textbox in userform

Hello, I have a userform that I enter work order numbers in and seem to have a little problem on how to exactly set this up for how I need it to work. This is probably simple for an experienced person but I am just learning. Basically what I have is 1 textbox for the work order like: "A12345". The info is finished and submitted to my worksheet. I have this working for finding exact matches. What happens many times is I need to add a work order number to the same row, so tomorrow the user might update from the form and search the current "A12345" and then want to add "A12346". So now cell A1 looks like this: A12345 A12346 The problem with my code now is there is no way to not allow a duplicate to be created for A12345 now that the string has changed. How can I prevent this? If I use the "LookAt:=xlWhole" .... the form will not find A12345

I hope explained this correctly. Here are the pieces of code for this. Thanks for your time and help!

Please Login or Register  to view this content.

You really need to post a sample sheet with sample data to explain what you mean.

Time Form.xlsHello royUK, Thanks for the reply... I have attached the file. I think I originally got the base of the form from something you posted. What I want to do is be able to create "A12345" and then later update and add "A12346" and have the "cmbAdd" find that there is already "A12345" within that cell and I cannot create another one with that name. My job numbers will always start with "A". Thanks for your help.

Hi Mallycat... thanks for the reply. I have attached the file in reply to royUK. Please take a look and hopefully someone can help me resolve this. Thanks for your help!

Jobs might be the same as new ones that have come in and started a process, so the process times will stay together. This is the only reason that new numbers will be added to existing ones. Sometimes I can have up to 4 or 5 "A" numbers in one cell.

I would like to help you, but I really don't understand what you are trying to do. Here are my observations.
1. You have a user form that takes time stamps at various stages of a process.
2. When you close the form, it writes the time stamps into your sheet.

So you seem to be saying that you want to go back and add a separate reference number to a previous set of time stamps? Is that right? Why do you want to do that - this doesn't make sense to me. Why wouldn't you create a new row of data? What is the relationship between the second A number and the time stamps, and why do you want to add the second A number to the existing time stamps. If you could explain that, it would help.

Matt

Hi Mallycat, thank you for your time and patience. To answer your questions the best way I can, yes that is true... The time stamps just enter to the sheet for separate process steps. I do want to go back and add numbers to the first numbers when it is necessary. The reason I do not want a new row is only because there are jobs added to the entire process where many of these steps have already been completed, so these "add-ons" will jump in at various points in time. If there is a new row, and the user searches for the added A Number, the form will fill with blank info and they will be confused. Hopefully I have explained it well enough. So this is the reason that I do not want a user to be able to create a new A number. I am quite new to this Excel programming, but it seems that the "Find A Number" code works quite well and will find multiple strings in the cell, it just doesn't work the other way and prevent one of those from being created. Thank you for your help!

Hello jaslake, thanks for your help. I tried this code but I am too new to really understand what it will do differently. Do I need to format each A Number with "*" at the beginning and end of the string?

Hi jaslake,

I am only trying to prevent like it shows in this sheet. row1 = A12345 A12346 row2 = A12345 I want to prevent row 2 from being created because it alreadys exists. Since I do not understand the programming part of it enough yet, the search works great for finding two A numbers of the same, it will find both A12345 numbers and list them, so I am trying to figure out how that can be combined with searching the same way and preventing it from being created. I hope I am making sense.

Hi jaslake, I think I must have confused you with what I said. The listbox and "find" feature work great without problems. My problem is that my code doesn't work when I press "Create A Number". So this method still lets me create A12345 in row 2 when it already exists in row 1. That is the part I don't understand myself, how is the code written to find 2 of A12345, but can't find it when I am trying create a new one of the same and tell me "That already exists" ?

Hi jaslake... you nailed it. This works exactly how I need it, I cannot create another A number that exists in one of the cells. Thanks very much for your time and help.... GREATLY appreciated.