+ Reply to Thread
Results 1 to 18 of 18

Find Next from Search Button

  1. #1
    Forum Contributor
    Join Date
    12-21-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    387

    Find Next from Search Button

    Hi,
    I have created an Excel UserForm that is working fine for Data Entry into an Excel spreadsheet.
    I have set-up a Search Button that will also Find at least the first occurence of an item and
    it populates the UserForm with the values in that entire row.
    But that Find only finds the first occurence and I can't get it to find the next.(it's start to find from first row)
    I want VBA code to perform a Next row from search button.

    I just did not know how to do it.

    Hrere is my code:

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    My English is very poor, so please be patient >_<"

    Thanks & Regards.
    hkbhansali

  2. #2
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Find Next from Search Button

    You could use the .Tag of the command button to store where to start from and use the .Find method

    Please Login or Register  to view this content.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  3. #3
    Forum Contributor
    Join Date
    12-21-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    387

    Re: Find Next from Search Button

    Hi, mikerickson
    Thanks for reply..
    but i don't know how to use the .Tag of the command button to store where ?

  4. #4
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Find Next from Search Button

    Every user form control has a .Tag property. It's just a string and does nothing. It just sits there, waiting to be read (via code) or written to (via code).

    The coder can use it to store strings that relate to that control.

    In this case, my code uses the .Find method to find a cell that contains the txtID.Text value. It then puts that cell's address in the cmdNext.Tag

    Then next time that the user presses cmdNext, it looks at the .Tag to see where to start looking for the next cell with txtID.

  5. #5
    Forum Contributor
    Join Date
    12-21-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    387

    Re: Find Next from Search Button

    Hi,mikerickson

    Code not working

    here is test sheet
    Attached Files Attached Files

  6. #6
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Find Next from Search Button

    If the code isn't working, what is it doing?

  7. #7
    Forum Contributor
    Join Date
    12-21-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    387

    Re: Find Next from Search Button

    its search id which i input in my textbox (txtid) , but when i click again next button nothing happen,
    it shoud go on next row. Ex. I input in "txtid" Text Box "LM/A/002" and Click on Find ID button,
    and it populates the UserForm with the values in that entire row.but when i click "next" button it should populate "LM/A/003".values in that entire row.

  8. #8
    Forum Contributor
    Join Date
    12-21-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    387

    Re: Find Next from Search Button

    its search id which i input in my textbox (txtid) , but when i click again next button nothing happen,
    it shoud go on next row. Ex. I input in "txtid" Text Box "LM/A/002" and Click on Find ID button,
    and it populates the UserForm with the values in that entire row.but when i click "next" button it should populate "LM/A/003".values in that entire row.


    Sorry Double Post..

  9. #9
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Find Next from Search Button

    In your column A, are there duplicates of ID's and you want the Next button to cycle through those?

    Or is each entry in column unique and you want the Next button to increment the ID that is in txtID and find that new ID?

  10. #10
    Forum Contributor
    Join Date
    12-21-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    387

    Re: Find Next from Search Button

    In my column A there are no duplicate ID's.
    I want the Next button to increment the ID that is in txtID and find that new ID.

  11. #11
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Find Next from Search Button

    With code like this, the Next button will search for the ID number in txtID.
    Pressing it again will increment the ID number in txtID, and search for that value.
    The txtID_Change routine is there to indicate that the user has entered a value into the text box and the Next button should search for that term rather than incrementing it first.
    Again the cmdNext.Tag property is used.

    Please Login or Register  to view this content.

  12. #12
    Forum Contributor
    Join Date
    12-21-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    387

    Re: Find Next from Search Button

    Hi,mikerickson,

    Thank you so much for the help. It works beautifully.

    your solution proved to be spectacular and simple.

    Much appreciated.

    Topic marked as solved and reputation added.

    Thanks again..

  13. #13
    Forum Contributor
    Join Date
    12-21-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    387

    Re: Find Next from Search Button

    sorry.. mikerickson

    I have check full code but only txtid and txtname chane routine after click on "cmdNext" button.
    other textbox like txtps,txtfly, txtadd1,....all txtboxes showing my header value only..

  14. #14
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Find Next from Search Button

    When you expand this block, you have to change the second (column) argument of Cell to match your situation.
    Each control's value should be put into a different column.
    Please Login or Register  to view this content.

  15. #15
    Forum Contributor
    Join Date
    12-21-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    387

    Re: Find Next from Search Button

    I have done each control..but i can't understand what's wrong..
    u can check file attached herewith.
    Attached Files Attached Files

  16. #16
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Find Next from Search Button

    This is code from your book

    Please Login or Register  to view this content.
    The dot . before Cells is critical.
    With the dot, Cells is relative to rngFound.EntireRow. This is call a qualified reference.

    Without the dot, the unqualified reference is to the ActiveSheet.

  17. #17
    Forum Contributor
    Join Date
    12-21-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    387

    Re: Find Next from Search Button

    Oh...
    I’m not perfect, I make mistakes.

    So Sorry..
    now it"s work perfect

  18. #18
    Forum Contributor
    Join Date
    12-21-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    387

    Re: Find Next from Search Button

    Quote Originally Posted by mikerickson View Post
    With code like this, the Next button will search for the ID number in txtID.
    Pressing it again will increment the ID number in txtID, and search for that value.
    The txtID_Change routine is there to indicate that the user has entered a value into the text box and the Next button should search for that term rather than incrementing it first.
    Again the cmdNext.Tag property is used.

    Please Login or Register  to view this content.
    Hi,Mikerickson

    Can Add Vb yesno code with msgbox "ID Not Found, Want to Create?" instead of msgbox"not Found" in above code?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Search Bar that Executes Search Based on Radio Button and Key Words
    By pomo2016 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-06-2016, 09:26 AM
  2. [SOLVED] userform search button remove search only when i type
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-08-2014, 02:32 AM
  3. Button to search for a File based off of button Text
    By ExcelnExcel in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-11-2013, 10:43 PM
  4. [SOLVED] Macro/s to create Search Bar to filter to search terms entered activated by Command Button
    By JasonRay in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-08-2013, 03:44 PM
  5. Need search button and update button vba codes
    By mayurpatil in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-09-2013, 09:36 AM
  6. [SOLVED] UserForm Search Button to find original records?
    By matrixpom in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-18-2012, 12:09 AM
  7. VBS Find/Search Button
    By LCPS in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-21-2007, 09:33 AM

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