+ Reply to Thread
Results 1 to 18 of 18

Add search function textbox in userform

  1. #1
    Forum Contributor
    Join Date
    04-03-2012
    Location
    Washington State
    MS-Off Ver
    Excel 365
    Posts
    340

    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.

  2. #2
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394

    Re: Add search function textbox in userform

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

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Add search function textbox in userform

    Possibly

    Please Login or Register  to view this content.
    For a better answer attach afile
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  4. #4
    Forum Contributor
    Join Date
    04-03-2012
    Location
    Washington State
    MS-Off Ver
    Excel 365
    Posts
    340

    Re: Add search function textbox in userform

    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.

  5. #5
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Add search function textbox in userform

    Why are you using multiple references in one cell?

  6. #6
    Forum Contributor
    Join Date
    04-03-2012
    Location
    Washington State
    MS-Off Ver
    Excel 365
    Posts
    340

    Re: Add search function textbox in userform

    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!

  7. #7
    Forum Contributor
    Join Date
    04-03-2012
    Location
    Washington State
    MS-Off Ver
    Excel 365
    Posts
    340

    Re: Add search function textbox in userform

    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.

  8. #8
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394

    Re: Add search function textbox in userform

    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

  9. #9
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Add search function textbox in userform

    Hi nu2java
    Try this
    In Your FindAll Procedure change this line of code
    Please Login or Register  to view this content.
    and this line of code
    Please Login or Register  to view this content.
    such that the code will look like this
    Please Login or Register  to view this content.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  10. #10
    Forum Contributor
    Join Date
    04-03-2012
    Location
    Washington State
    MS-Off Ver
    Excel 365
    Posts
    340

    Re: Add search function textbox in userform

    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!

  11. #11
    Forum Contributor
    Join Date
    04-03-2012
    Location
    Washington State
    MS-Off Ver
    Excel 365
    Posts
    340

    Re: Add search function textbox in userform

    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?

  12. #12
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Add search function textbox in userform

    Hi nu2java

    Not certain what you're after...try the attached.
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    04-03-2012
    Location
    Washington State
    MS-Off Ver
    Excel 365
    Posts
    340

    Re: Add search function textbox in userform

    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.

  14. #14
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Add search function textbox in userform

    Hi nu2java
    Ahhh...try this...makes your ListBox longer so you can SEE all the selected items.

    Edit...saw your post 13...didn't understand.
    Attached Files Attached Files
    Last edited by jaslake; 04-21-2012 at 10:40 PM.

  15. #15
    Forum Contributor
    Join Date
    04-03-2012
    Location
    Washington State
    MS-Off Ver
    Excel 365
    Posts
    340

    Re: Add search function textbox in userform

    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" ?

  16. #16
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Add search function textbox in userform

    Hi nu2java
    I'm still at a bit of loss at what you're trying to accomplish...see if the attached is any closer.
    Attached Files Attached Files

  17. #17
    Forum Contributor
    Join Date
    04-03-2012
    Location
    Washington State
    MS-Off Ver
    Excel 365
    Posts
    340

    Re: Add search function textbox in userform

    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.

  18. #18
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Add search function textbox in userform

    Hi nu2java

    You're welcome...glad I could help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1