+ Reply to Thread
Results 1 to 25 of 25

Userform pulling data but Next and Previous buttons do not function - Any thoughts?

  1. #1
    Forum Contributor
    Join Date
    09-10-2008
    Location
    Phoenix, AZ
    MS-Off Ver
    Office 365
    Posts
    985

    Userform pulling data but Next and Previous buttons do not function - Any thoughts?

    Hello,

    I have a Userform that the User enters a Error code or Keyword and it is supposed to search the sheet and show the results in the Userform.

    It seems to be a good code but it is not pulling any results.

    Any thoughts would be great!

    Best Regards,

    John
    Attached Files Attached Files
    Last edited by JJFletcher; 06-22-2016 at 07:21 AM.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Userform not pulling data from sheet when search button is selected - Any thoughts?

    Hi JJ,

    This looks like a problem with "Scope" of modules. When you put code behind a worksheet, it is limited to work on that sheet alone. If you put the code in a MODULE it can then work on any worksheet and/or userform. Try pulling the code behind you worksheet out into a new MODULE to see what happens. You will need to Insert a new MODULE using the VBA window to do this.

    http://www.contextures.com/xlvba01.html
    http://www.cpearson.com/excel/Scope.aspx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Contributor
    Join Date
    09-10-2008
    Location
    Phoenix, AZ
    MS-Off Ver
    Office 365
    Posts
    985

    Re: Userform not pulling data from sheet when search button is selected - Any thoughts?

    Created the Module and called it from the Command Button from the form and received the following error: "Object Required"

    Please Login or Register  to view this content.
    I am uploading the new Workbook for review:

    Best Regards,

    John
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    09-10-2008
    Location
    Phoenix, AZ
    MS-Off Ver
    Office 365
    Posts
    985

    Re: Userform not pulling data from sheet when search button is selected - Any thoughts?

    Is this Search code Valid for this Userform?

    Please Login or Register  to view this content.
    Even with this change I still get the "Object Required" error...

    Best Regards,

    John
    Last edited by JJFletcher; 06-18-2016 at 04:26 PM. Reason: corrected upload code

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Userform not pulling data from sheet when search button is selected - Any thoughts?

    Hi JJ,

    I think you need what userform the textboxes are in, in your code.. Something like:

    Userform1.TextBox6.Text = Sheets("COMPLETE").Range("D" & row_number) 'Error Code

  6. #6
    Forum Contributor
    Join Date
    09-10-2008
    Location
    Phoenix, AZ
    MS-Off Ver
    Office 365
    Posts
    985

    Re: Userform not pulling data from sheet when search button is selected - Any thoughts?

    Hi MarvinP,

    Modified the code - I no longer get an error - but the form does NOTHING...

    Please Login or Register  to view this content.
    Best Regards,

    John
    Attached Files Attached Files

  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Userform not pulling data from sheet when search button is selected - Any thoughts?

    Hi John,

    Try this code using For Next construct in the attached.
    Userform Search pull data from Sheet to Userform.xlsm

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Userform not pulling data from sheet when search button is selected - Any thoughts?

    Not sure about the keyword thing but this works for me when entering an error code.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  9. #9
    Forum Contributor
    Join Date
    09-10-2008
    Location
    Phoenix, AZ
    MS-Off Ver
    Office 365
    Posts
    985

    Re: Userform not pulling data from sheet when search button is selected - Any thoughts?

    Thanks you for your reply norie and MarvinP,

    MarvinP, Thank you for getting this to me - it's great... the data results being provided is the error code - can the code be modified so that any word or number in the range from B2 to G1000 regardless of the subject can be pulled - as long as it is there in the columns it will pull? IE: The word educate ...

    John

  10. #10
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Userform not pulling data from sheet when search button is selected - Any thoughts?

    Hey John,

    Try this:
    Please Login or Register  to view this content.
    I think the case of the search string needs to be the same. You can use UCase for both if you need to worry about the case (upper or lower) of words.

  11. #11
    Forum Contributor
    Join Date
    09-10-2008
    Location
    Phoenix, AZ
    MS-Off Ver
    Office 365
    Posts
    985

    Re: Userform not pulling data from sheet when search button is selected - Any thoughts?

    MarvinP,

    This is much better... Thanks... Is it possible to make the search pull data regardless if it is Upper or lower case - I tried to pull the word "important" with the (I) being lower case and it pulled the record with error code BA01 because it was a match... Can the search also be coded to allow if someone uses a capital "I" instead... for an example?

    Is it also possible to show multiple results in the same search?

    Best Regards,

    John

  12. #12
    Forum Contributor
    Join Date
    09-10-2008
    Location
    Phoenix, AZ
    MS-Off Ver
    Office 365
    Posts
    985

    Re: Userform not pulling data from sheet when search button is selected - Any thoughts?

    Is it possible to add a next button to show the next matching results?

    John

  13. #13
    Forum Contributor
    Join Date
    09-10-2008
    Location
    Phoenix, AZ
    MS-Off Ver
    Office 365
    Posts
    985

    Re: Userform not pulling data from sheet when search button is selected - Any thoughts?

    Here is the modified Sheet up to this moment.

    Trying to work the code so that multiple result will be displayed and if that is not possible is it possible to have a next button to scroll through the results... The other issue is that the search in this code is case sensitive - should allow for the user to type any case and get a match would be awesome

    Best Regards,

    John
    Attached Files Attached Files

  14. #14
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Userform not pulling data from sheet when search button is selected - Any thoughts?

    Hey John,

    This will fix the case problem

    If InStr(UCase(Cells(RowCtr, ColCtr).Text), UCase(UserForm1.TextBox1.Text)) > 0 Then

    BUT...

    If you might have multiple records/rows that fit the search term it might be better to not have a userform at all. Look to advanced filters with an apteryx in front and behind the search term.

    http://www.contextures.com/xladvfilter01.html

    Now that you've seen how hard VBA is using Userforms I'd suggest straight Advanced Filters are closer to what you are asking for now.

    Perhaps a double click on a record would show it in the user form, after you did an advanced filter in the table..

  15. #15
    Forum Contributor
    Join Date
    09-10-2008
    Location
    Phoenix, AZ
    MS-Off Ver
    Office 365
    Posts
    985

    Re: Userform not pulling data from sheet when search button is selected - Any thoughts?

    Thanks MarvinP - I will look into that for sure...

    One last thing... I have noticed that the code is pulling the last record or row found with the search word rather then all of them - can that be modified or is that a difficult issue?

    John

  16. #16
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Userform not pulling data from sheet when search button is selected - Any thoughts?

    Hey John,

    Instead of using the "Instr" construct you might use a Find() construct and find next in his VBA code. Now, how do you want to display the found data? One at a time or concatenate it into the userform's text boxes. All the code needed to support Userforms is stress. Drop back to not needing a userform and simply use filters.

    Look at
    https://msdn.microsoft.com/en-us/lib...ffice.11).aspx
    if you must.

  17. #17
    Forum Contributor
    Join Date
    09-10-2008
    Location
    Phoenix, AZ
    MS-Off Ver
    Office 365
    Posts
    985

    Re: Userform not pulling data from sheet when search button is selected - Any thoughts?

    hey Marvin,

    Thanks so much for working on this with me - Awesome is not enough of a word to describe... I am currently looking at the Filters suggestion now - have set up the Criteria and the Extract features - this is difficult for sure - especially since I have never done this before with filters... Trying to drudge along... I will let you know how I fare! I am having a hard time of it so far...

    John

  18. #18
    Forum Contributor
    Join Date
    09-10-2008
    Location
    Phoenix, AZ
    MS-Off Ver
    Office 365
    Posts
    985

    Re: Userform not pulling data from sheet when search button is selected - Any thoughts?

    Trying to combine Filters - Stuck on how it works with the search parameters? Trouble I am having is understanding how a user performs a search for a keyword and it provides the results like a Userform does...

    Would prefer to have the userform show the data one at a time and the user can select the next button to advance though them.

    John
    Last edited by JJFletcher; 06-18-2016 at 07:50 PM.

  19. #19
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Userform not pulling data from sheet when search button is selected - Any thoughts?

    Hey John,

    I just remembered another answer to your problem. Excel used to have a tool called the "Form ..." or "Data Form" that does what I think you want. It is still in all excel products and you can add it to your QAT (Quick Access Toolbar). It has the ability to filter your records and do a next and back. Read about it at:
    http://spreadsheets.about.com/od/dat..._data_form.htm

    If this isn't good enough then look at :
    http://spreadsheetpage.com/index.php/dataform/home/
    http://spreadsheetpage.com/index.php/dataform/features/

    Why reinvent the wheel when it is already in Excel??
    Last edited by MarvinP; 06-18-2016 at 08:31 PM.

  20. #20
    Forum Contributor
    Join Date
    09-10-2008
    Location
    Phoenix, AZ
    MS-Off Ver
    Office 365
    Posts
    985

    Re: Userform not pulling data from sheet when search button is selected - Any thoughts?

    MarvinP,

    Thanks so much for pointing that feature out - did not know that it was available - The Search feature with the Criteria is flawed - very flawed

    I do not like it - there is no clear cut search feature with it

    John
    Last edited by JJFletcher; 06-18-2016 at 08:51 PM.

  21. #21
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Userform not pulling data from sheet when search button is selected - Any thoughts?

    You CAN search using it. Click on the "Criteria" button and fill in any/all search terms. Then next and previous.

    https://support.office.com/en-us/art...c-a8ce70609374

    Also John, if you have liked the help, click on the "Add Reputation" star below any of my answers and I will move up in stature on this site.
    Last edited by MarvinP; 06-18-2016 at 08:58 PM.

  22. #22
    Forum Contributor
    Join Date
    09-10-2008
    Location
    Phoenix, AZ
    MS-Off Ver
    Office 365
    Posts
    985

    Re: Userform not pulling data from sheet when search button is selected - Any thoughts?

    Hi MarvinP,

    I have no problems providing you the "Rep"... you are Awesome to say the least...

    However this Form Add-In suggested does not provide the search capabilities needed and I will need to I guess as you put it - "Re Invent the wheel"... This Add-In is not adequate enough for my needs.

    Best Regards,

    Jon

  23. #23
    Forum Contributor
    Join Date
    09-10-2008
    Location
    Phoenix, AZ
    MS-Off Ver
    Office 365
    Posts
    985

    Re: Userform not pulling data from sheet when search button is selected - Any thoughts?

    Thanks MarvinP

    The code is pulling the last record or row found with the search word rather then all of them - can that be modified or is that a difficult issue? You asked me - Now, how do you want to display the found data? One at a time or concatenate it into the UserForm's text boxes. I think One at a time with Prev and Next Buttons... I have added the UserForm for review - I am unable to get the Prev and Next buttons to function.

    Best Regards,

    John
    Attached Files Attached Files

  24. #24
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Userform not pulling data from sheet when search button is selected - Any thoughts?

    Hi John,

    Did you look at the built in "Data Forms" tool already in Excel? Did you click on the "Criteria" button to see it searches. Did you read the link from MS Support on wildcard characters and how to improve searches? Did you see the Prev and Next buttons on the Data Forms dialog?

    OK - see the video to see how easy/hard it is to do what you want.
    http://www.onlinepclearning.com/add-...orm-excel-vba/

    I'm standing by my point about using the wheel that is already in Excel to do what I think you want.

  25. #25
    Forum Contributor
    Join Date
    09-10-2008
    Location
    Phoenix, AZ
    MS-Off Ver
    Office 365
    Posts
    985

    Re: Userform not pulling data from sheet when search button is selected - Any thoughts?

    I would like to proceed with this project if that is ok... Possibly there are others that have a solution...

    I am posting up the latest version for review... I have deleted the module and placed the entire code into the Userform (Code)

    when running the next or previous button I get:

    Compile error:

    Sub or Function not defined

    and the word "Data" is highlighted

    Please Login or Register  to view this content.
    John
    Attached Files Attached Files

+ 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. Replies: 1
    Last Post: 05-12-2016, 03:36 AM
  2. Problem with pulling data to my userform from Excel sheet
    By Roampie in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-07-2015, 10:14 AM
  3. Userform, Search, copy an paste data from a userform database to another sheet.
    By masro in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-15-2015, 02:42 PM
  4. Replies: 0
    Last Post: 02-27-2014, 04:43 PM
  5. how to copy data after userform button selected
    By simeonmein in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-11-2013, 12:37 PM
  6. How to delete the last row on the selected sheet from userform button?
    By Shacker in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-12-2013, 09:04 AM
  7. Do a search, display results in a userform with the ability to copy selected data.
    By blkmagik in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-05-2011, 04:40 PM

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