+ Reply to Thread
Results 1 to 21 of 21

Add Search capabilities to this current Workbook

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

    Add Search capabilities to this current Workbook

    Hi All,

    Wanted to know if it's possible to add a search button to this workbook... Assuming there are 100's of people in the list, wanted to be able to do a search

    Is it possible with this type of setup?




    John
    Last edited by JJFletcher; 11-23-2014 at 02:20 AM.

  2. #2
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Add Search capabilities to this current Workbook

    here is a macro that will ask you to enter some search parameter. It can be only a part of the name. The macro will look the EmployeeData sheet for an entry that fits your search. If a match is found then it will ask you if this is the employee your are looking for. You can answer either Yes, No or Cancel. If more than one entry fits your search, the macro will loop through all of them and ask you to make a choice. The only way out of this, if you don't see the employee you want, is the Cancel button.
    If you click on Yes, the macro will put the employee number ic cell Q4 of the Dashbord sheet and you'll se his or her statistics.
    Please Login or Register  to view this content.
    Create a new button and assign this macro to it.
    Hope this helps
    Pierre Leclerc
    _______________________________________________________

    If you like the help you got,
    Click on the STAR "Add reputation" icon at the bottom.

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

    Re: Add Search capabilities to this current Workbook

    This is great! It identifies the persons in the list - once the person is identified in the search function and the user selects yes - instead of posting data on the Dashboard worksheet, can it highlight the person in the list?
    Last edited by JJFletcher; 11-22-2014 at 01:36 PM. Reason: correction in comment

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

    Re: Add Search capabilities to this current Workbook

    Does anyone else have any thoughts besides Pierre?

  5. #5
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Add Search capabilities to this current Workbook

    Actually, this is what is does but you might not see it because the selection could be in the 3rd or 4th screen of the ListBox.
    Anyway, you see the employee name in cell L9 just above the statistics.

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

    Re: Add Search capabilities to this current Workbook

    Thanks Pierre for your response - so your code cannot be modified to highlight the name in the list rather then add data to the Dashboard?

  7. #7
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Add Search capabilities to this current Workbook

    It does highlight the name in the litsbox. Check this by searching for a name in the first screen. You'll se that it will be highlighted. I just can't find how to scroll the listbox to show the selected name.

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

    Re: Add Search capabilities to this current Workbook

    Can you please upload the workbook you have added this to, I am not seeing this when I add it to the workbook

    I am getting this error

    error1a.JPG
    Last edited by JJFletcher; 11-22-2014 at 06:13 PM.

  9. #9
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Add Search capabilities to this current Workbook

    here is a new workbook.
    I changed the "FORMS Listbox" by an "ActiveX Listbox" which gives us more control.
    I was able to program the find button so that the selected employee will be shown at the first row of the listbox.
    I had to add a listbox_click event in the Dashboard sheet so that your dashboard works as it was before.
    Hope this is up to your expectation.
    Regards
    Attached Files Attached Files
    Last edited by p24leclerc; 11-22-2014 at 08:58 PM.

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

    Re: Add Search capabilities to this current Workbook

    Pierre,

    When I attempt to launch the find an employee button the above error show! - Can't find project or library

  11. #11
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Add Search capabilities to this current Workbook

    Just enter the following code at the beginning of the macro
    Please Login or Register  to view this content.
    it should work.

  12. #12
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Add Search capabilities to this current Workbook

    Just enter the following code at the beginning of the macro
    Please Login or Register  to view this content.
    it should work.

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

    Re: Add Search capabilities to this current Workbook

    I would like to Re - Address this thread.. An Obvious HOT HOT Subject...

    So I would like to go farther...

    The attached Workbook is a sample of what I am working with... Pierre's solution above worked and he has made the code work - yet in doing so, re-designing that small portion of code caused the remaining sheets to not function properly.

    Pierre's Thoughts:
    I changed the "FORMS Listbox" by an "ActiveX Listbox" which gives us more control.
    I was able to program the find button so that the selected employee will be shown at the first row of the listbox.
    I had to add a listbox_click event in the Dashboard sheet so that your dashboard works as it was before.

    I would like to work with the attached Workbook code and allow the user the ability to Search for an employee without changing the current code structure and functionality...

    HR Dashboard Modified V1.0.xlsm

    Any Thoughts?

    Best Regards,

    John

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

    Re: Add Search capabilities to this current Workbook

    I tied to purchase Commercial Services - yet the prompt I received when trying to pay is:

    "Your account is limited. Please check your Account Overview page for messages about resolving this problem"

    This is sent by [email protected]

    How do I attain the Commercial Privileges - I want to address this issue...

    John

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

    Re: Add Search capabilities to this current Workbook

    Can one of the Moderators please respond to the last question?

  16. #16
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Add Search capabilities to this current Workbook

    Hi John,
    would you please take a look at the attached workbook and see how it works for you. I did some testing and found no problem but you probably know better then me what to look at. It seems to be working just fine with the modifications I did.
    If it does not work, can you point me to the specific problem so I can take a look at it.
    Regards
    Attached Files Attached Files

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

    Re: Add Search capabilities to this current Workbook

    Pierre,

    Thanks for your feedback..

    I really want to handle this issue and am willing to use the Commercial services - yet as you can probably see I have been having issues with getting it done - I will review and get back to you!


    Best Regards,

    John

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

    Re: Add Search capabilities to this current Workbook

    I have tried to add this to another duplicate workbook and am getting a 400 error - the code works when I unprotect the sheet but get the error when protected - any thoughts?

    Regards,

    John

  19. #19
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Add Search capabilities to this current Workbook

    Unprotect the sheet at the beginning of the button macro and reprotect the sheet before ending it. You might have to manage some "Exit Sub" at some places in the code.

  20. #20
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Add Search capabilities to this current Workbook

    Hi JOhn,
    forget about my previous message. After I give it a try, the error comes from the fact that in your original workbook, the cell R4 is unlocked. This is the only cell the macro uses.
    When you added this to another duplicate workbook, cell R4 was locked by default thus the error when you run the find an employee macro.
    Unlock cell R4 prior to protect the sheet and it should work.

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

    Re: Add Search capabilities to this current Workbook

    Pierre,

    That worked - Thanks a bunch..

    I have additional questions when you have time

    Regards,

    John

+ 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. [SOLVED] Search cell value in another workbook and copy range from current to other workbook
    By SierraKilo78 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-08-2014, 05:20 PM
  2. Can I have Excel search another workbook for data and import it to my current workbook?
    By mgruber in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 09-05-2014, 07:13 PM
  3. [SOLVED] How to search in the current workbook
    By thelegazy in forum Excel Programming / VBA / Macros
    Replies: 23
    Last Post: 07-10-2013, 02:27 PM
  4. Question regarding vba search capabilities
    By lilsnoop in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-08-2009, 05:03 PM
  5. Built-in VBA search capabilities?
    By afiack in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-20-2006, 02:38 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