Searching for and returning values from a Listbox within a Userform

Bump no response

Bump no response
Per conversation with jaslake (John) I am re-posting my updated workbook and Userform.
MFR Userform Test Book.xls

I have made lots of changes and additions to this userform code, and some changes to the actual worksheet. I have the all of the code/buttons working at the moment, but only the first "Search" Button. (for MFR_ID)
Here is what I need/want help with:
1: I would have liked to do searches for any of the values in the textboxes on that form with only ONE search button. Because I am returning values to the userform, I see no other way around this at the moment. If I have to keep a search button for each box, then so be it... I just have to copy-paste-edit code for each button.
2: Due to the way the code is written (because I don't know any better) the "Amend" button will only work (correctly) if I have done a search by "MFR_ID". It is set up to work off the active cell which was set by the search. (for example) If I had code in for the other search buttons and I did a search by "Corp Name" then the active cell would be in that column. Because I have the code for the "Amend" button set up for the "MFR_ID" search, the values would be put in all the wrong spots.
I really would not like to put an "Amend" button in along with each search button!
I would like to find some code that will let me search for, return values and change values by keeping the active cell in "A". That way, no matter what my original search was on, I can transfer my values to right cells on the worksheet.

I hope this will all make sense to everyone... Try the form out...
Best of luck to everyone!
Cory

John,
Just took a small look at this... It is looking better, however I did see that the dblClick is not putting things in the proper place. Also, I think with mine I was able to look for a partial name. This is very benificial to me. I do not always know EXACTLY what I am looking for.
Is it possible now to add proper listbox headers? I tried to set it up previous, but could not get it without doing the weird filtering. Which was OK I guess.
I will have to look at this more tonight when I get some time.
Thank you again for all your effort put forth!!!

Cory

MFR Userform Test Book v2.xls
John,
I have fixed the issue with items not populating correctly with the DblClick.
You had missed a listing here: (There should have been 7 listings as you added RecRow, and due to the DblClick settings, the sequence is a little different.)
This code is from the SEARCH_CLICK CODE:

Please Login or Register  to view this content.

Only other broken button is the DELETE button. It does not move the ActiveCell to the RecRow row. I tried to get this to work, but am unfamiliar with HOW it works. I am sure it will be an easy fix for you!
I think the only thing left to do is the Header Row. I know that you can not use Row Source because the columns are bound. Not sure if there is a work around. As I had stated earlier, that (I believe) is why the filtering was set up the way it was... So that it would list the actual sheet header row, and was also set up so that you could not double click to select it. Not sure if we have to go back to this? If so, can you fit that into the code? I am not sure what to put where to get that back now that you changed much of that.
I also changed some of the listings so that maybe you can read/search/change better. Working with those MFR codes is tough... It's what I do all day long.

Other than that, this thing is looking MUCH better than I thought it would.
You have no idea how grateful I am for your help!!! I am learning much more from this forum than I could out of a book. (I always like hands on learning. :-)
Hope this helps you out as much as it has me!
Any more questions, let me know.
Cory

I have complete all that needs to be done at the moment....
Everything is now working EXACTLY the way it should!
I fixed a few things to make it work/display more the way I would prefer. I also cleaned up some code.
I will take more time to see if I can "break" it... But I think the code is all pretty solid now. :-)

MFR Userform Test Book v3.xls

There is really only one more thing I need to do with this userform....
This userform is going to be used in a "template" type sheet. The actual "database" this userform will be used against will need to open/edit/save/close in the background.
(Unless I can find a way to do that without opening it at all... Which I have NOT found a way to do yet.)
I know that I can open/close in the background, but I am not sure about the editing and saving part.
This all need to be done while keeping the file as a "Read-Only" so that people can not just openly edit it. (If possible)
Any suggestions on this??? Some simple code I am hoping....

Thank you again for all the time you have spent on this! If there was any way I could re-pay you, I would!
Cory

Ok John, I kind of broke it.....

MFR Userform Test Book v3.2.xls

Here was my goal... I renamed Sheet1 to Sheet 2 to put things in line, then I added a "Selection List" to put the "Lifecycle Phase" drop-down list. I realized that there should be no extra data on the MFR_List as those things could be deleted if someone where to delete that line.
So, I went through and made the changes from Sheet1 to Sheet2, and changed the drop-down source as appropriate. Now when I run it, it will complete the search, but when I dbl click anything, it error out. It doesn't seem to be getting the RecRow values. I am not sure where or what to change now.
Also, I noticed that when I was on the "Selection List" sheet and ran the userform, it did a search on that worksheet.... What needs to be changed so that no matter where this form is ran from (different workbook/worksheet) it will always go to the MFR_List? I think once that code is put into place, I should be able to incorporate it into other workbooks with little hassle... I THINK!
Otherwise, things are looking good!!!
Thanks,
Cory

Based on the change in Sheet2, you will have to change the following code in the cmdSearch_Click() bit of code:

The code that determines LC should read:

Please Login or Register  to view this content.

It formerly read:

Please Login or Register  to view this content.

This needs to be changed because you reduced the number of columns being used in Sheet2 when you got rid of the LifeCycle Phase column, which was included in Sheet1.

Hope this clears things up for you. And @ JasLake. Great job with the form in general. Hope I'm not stepping on toes by chiming in here.

BigBas...
Not at all a problem "chiming in" on this thread. I am open to anyone helping out.
Thanks for your fix. That works...
The only thing left is my other issue stated on my last reply.
Maybe you have suggestions?
Thanks,
Cory

Hi garden_gnome,

Sorry I missed the second request.

THe following line of code should be amended.

Original:

Please Login or Register  to view this content.

Correction:

Please Login or Register  to view this content.

Without including WS, the code is searching for the LastRow being used in the activesheet. With that said, although the code is correctly searching the MFR_List sheet, it is only searching through 6 rows (the used range in Selection sheet)

Let me know if this helps

BigBas,
That did not quite work... Adding ws does not affect anything if you are using the currently viewing the MFR_List worksheet. It also does not fix anything if you are viewing a different worksheet.
Just to refresh, here is the ultimate goal... This userform will stay within this database, however, I am going to be running it from a different workbook. I need to make sure that it is always reading/writing data to ONLY that database.
I do have a shortcut set up on the userform so that you can test it out while viewing a different worksheet. (Ctrl+Shift+A)
Thanks,
Cory

I'm trying to confirm that we're on the same page. Without including the ws that I mentioned earlier, if Selection_List worksheet is active and I press CTRL+SHIFT+A, and I search for 3M, I get 0 hits. If I make the change to include the "ws", with all else being equal, I find 40 hits. Are you not seeing the same results?

From what I understand, Excel is somewhat limited with regards to running a userform from a different workbook. Will it always be run from the same different workbook? Or can it be run from many different workbooks?

Ok, that really makes me curious as to why both of those fixes are NOT working for me. I am on Excel 2003 if that makes a difference?
I made changes to both the LC and LR, yet if I am actively on "Selection_List" it will not find ANYTHING.
Here is my file with changes...
MFR Userform Test Book v3.3.xls
As regard to the other issue... Let me stand corrected! I have the same basic thing going on in a template. However, the userform IS built into the template but its information that it is looking for is within a different workbook. That is more of a simple lookup/return values thing that I did not write.
I suppose I could do the same thing here and build the userform into all of the templates that I need to have it in, but I was hoping to keep code/size to a minimum on the template. Was going to build a simple macro to call out the userform on the database workbook. Does this make sense?
Thanks,
Cory

The first instance of LR must be preceded with WS as well.

Try:

Please Login or Register  to view this content.

Let us know if it works.

That worked guys, thank you for clarifying.
Here is the next piece that breaks....

Please Login or Register  to view this content.

The sort will work if I am on the MFR_List, but again, errors out when I am anywhere else.
I assume it's something simple... Usually is... I really need to learn how to read!
Thanks,
Cory

The problem with the sort code is the following is that it is not being called upon a range, only the spreadsheet. Make this change:

Please Login or Register  to view this content.

BigBas,
I could not get your change in code to work. So I recorded a macro sorting the data manually.
The only thing I chanded was the range. (since there will be addition later)
Here is what I now have:

Please Login or Register  to view this content.

Originally Posted by jaslake

Hi Cory
I'm going to step back...it appears you're in good hands with BigBas. If you need further help, PM me.

John,
I would prefer getting as many eyes on this as possible. Seems like I still have a lot of work ahead of me.
I am working on this for my job, so I am kind of under the gun here to get it done quickly and correctly.
As for this:

Originally Posted by jaslake

Hi Cory
Regarding the "next step".

• As I understand it, the UserForm will be in a workbook by itself (let's call it Macro_Book.xls) with no data (no MFR_List worksheet, no Selection_List worksheet and no Sheet2)
• As I understand it, these worksheets (MFR_List worksheet, Selection_List worksheet and Sheet2) will be in another workbook (let's call it Data.xls).
• If my understanding is correct, what needs to happen now.
• If my undersatnding is NOT correct (which I suspect), fix it (my understanding).

You are correct, and not correct at the same time. I have one of two scenarios to play with.
Scenario one: (My preference) I have a semi-blank workbook ( call it "Template.xls" ) that when opened will also open the workbook "Data.xls" IN THE BACKGROUND that contains the UserForm, MFR_List worksheet, Selection_List. I will add a control button and/or shortcut keys to "Template.xls" that call out to open the UserForm in "Data.xls" This "Data.xls" will be stored on a network drive where several people have access and may be using it at the same time. I would also like to keep it Read Only to keep people from modifying the data within the workbook, but I am unsure if that is possible when it is opened as Read Only. (Maybe coding in my userform can take the read only off, then re-apply it?)

Scenario two: (seams easiest) I have the "Template.xls" workbook that DOES contain the Userform code, but we will still be opening the "Data.xls" in the background to read/write data. So the code will still have to point to "Data.xls"
The "Data.xls" workbook MUST be separate of the "Template.xls" because the data within it is ~40,000 lines long. We obviously don't want to have hundreds of copies of that sitting on a server.

Purpose: People who are using "Template.xls" are filling in data with the MFR code. We will use vlookup to fill in most of those from "Data.xls", but not all of them exist, or some of them need to be changed. Que the the UserForm. With that they can add/modify that list on the fly so that once added, that vlookup will see that data. We will be automating something that someone has to do manually without any userform. Also, people have started there own lists of MFR Code, so we want to combine it and become uniform on what everyone is using.

I am fairly sure that the first scenario is very possible. It is just a matter of changing some of the code to call out explicit workbook/worksheet name.
I am not sure, but something like this:

Please Login or Register  to view this content.

However I know very little about the 'me' statements, nor if the 'sheet' statements would need changing, so I don't know how those would be affected.
I hope this gives you a little better understanding about where I am going with this.
Feel free to pm with questions if you do not want to clutter the forum.
Thank you again for all your time and effort everyone!
Cory

Originally Posted by jaslake

Hi Cory
I'm going to step back...it appears you're in good hands with BigBas. If you need further help, PM me.

Would love to be able to pm you... But you have it turned off. :-)

Hi

I am a new bee at programming in excel macro out of self interest.
I have an excel sheet with book titles, the book titles have been hyper-linked to the PDFs books with the very titles on my computer... now at this point, is were i am in need of having a search box in the sheet to search for a book title and populate the results. At the same time i should be able to click on the result and it opens the PDF book.

Would sure appreciate if anyone may help