Searching for and returning values from a Listbox within a Userform

[garden_gnome]

See attached file for specifics!
MFR List Exp2.xls

I have a UserForm from which I am doing 2 things: First it is used to enter new lines of data onto the worksheet, secondly I need to use the form as a search tool to look for existing data.
I have most of the code functioning for the UserForm, but I am struggling with the ListBox and getting the "searched" information into the proper boxes on the form.

GOAL:
I would like to be able to do a search on any one field, and (if possible) a combination of fields. When the search is completed, I need to be able to dblClick on any line within the listBox, and return all of the fields in the listBox to their appropriate spots on the UserForm.
The intent of this is to search for a value within the worksheet (i.e. MFR_ID) and return all of the values associated with whatever the user selects to the UserForm. I would then modify any lines needed and then add that NEW line of data into the WS.

If you have any questions regarding this question, the goal, or anything else, please let me know!

Anything to help this out would be GREATLY appreciated!
Also, since I am trying to learn VBA, could you supply a quick explanation for any code added. (NOT REQUIRED)
I am trying to familiarize myself with the code and its functions.
All I do at the moment is copy/paste code from other peoples projects, tweak them, and hope they work. This is the most code writing I have done thus far.

Thank you again!

[garden_gnome]

Bump no response

[jaslake]

Hi garden_gnome
There's a Data Base Download available at this site that does all you describe http://www.excel-it.com/vba_examples.htm
I've personally adapted the sample to several of my projects. Take a look...if you need help adapting, let me know.

[garden_gnome]

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

[jaslake]

Hi Cory

The two code changes suggested by BigBas appear to work for me. What issue are you having?

[jaslake]

Hi Cory
Play with the attached...let me know what it's not doing that you'd like. I've changed the code for only the Search Button and for the ListBox Double Click. I left your old UserForm but it can be deleted.
You can search Mfg ID, Mfg ID Old, Mfg Name or Corp Name. Let me know of issues.

Sorry...I just realized I didn't change the Amend code. You want to take a stab at that or should I?

[jaslake]

Hi Cory
The attached has the revised Amend Button code. Let me know of issues.

[garden_gnome]

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

[jaslake]

Hi Cory
This is the code change that needs to be made

Private Sub cmdSearch_Click()
Dim f As Integer
Dim FirstAddress As String
Dim strFind As String    'what to find
Dim ctrl As Control
Dim cnt As Long
Dim LR As Long
Dim LC As Long
Dim rFilter As Long
Dim strFindID As String
Dim rng As Range
Dim c As Range
Dim rSource As String
Application.ScreenUpdating = False

    cnt = 0
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "TextBox" And Not ctrl.Name = "TxtComments" _
                And Not ctrl.Name = "txtRecRow" Then
            If Not ctrl.Text = "" Then
                strFindID = ctrl.Name
                strFind = ctrl.Text
                cnt = cnt + 1
            End If
        End If
    Next ctrl
    If cnt = 0 Then
        MsgBox "Please select one search criteria"
        Exit Sub
    End If

    If cnt > 1 Then
        MsgBox "You've selected more than one search criteria"
        Exit Sub
    End If

   LR = ws.Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious).Row

[jaslake]

Hi Cory
This appeared to work for me...

I did see that the dblClick is not putting things in the proper place

However, your sample records are quite similar so I may have missed it. Please site specific examples so I can follow up.

I'll look at this

I think with mine I was able to look for a partial name

And this

Is it possible now to add proper listbox headers

ListBox headers is not "native" using "AddItem" but I may have found another way...we'll see.

[jaslake]

Hi Cory

This LR SHOULD NOT change

With Sheets("Sheet2")
                            LR = .Cells.Find("*", .Cells(Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                                    SearchDirection:=xlPrevious).Row
                            LC = .Cells.Find(What:="*", After:=[A2], _
                                    SearchOrder:=xlByColumns, _
                                    SearchDirection:=xlPrevious).Column
                            rSource = "Sheet2!" & .Range(.Cells(3, 1), .Cells(LR, LC)).Address(False, False)
                        End With

[garden_gnome]

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:

                            With Me.ListBox1
                                .AddItem c.Value
                                .List(.ListCount - 1, 0) = Range("B" & c.Row).Value
                                .List(.ListCount - 1, 1) = Range("A" & c.Row).Value
                                .List(.ListCount - 1, 2) = Range("c" & c.Row).Value
                                .List(.ListCount - 1, 3) = Range("D" & c.Row).Value
                                .List(.ListCount - 1, 4) = Range("E" & c.Row).Value
                                .List(.ListCount - 1, 5) = Range("F" & c.Row).Value
                                .List(.ListCount - 1, 6) = c.Row
                                .ColumnWidths = "1.1 in;2.5 in;1.1 in;2.75 in;1.25 in;5 in;0 in"
                            End With

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

[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).

[jaslake]

Hi Cory
In the attached I've made the following changes

Only other broken button is the DELETE button

I think the only thing left to do is the Header Row

I think with mine I was able to look for a partial name

The Header Row issue is resolved by coping the Filtered data to another location (Sheet1) and using that sheet as the Row Source for the ListBox.

The Partial Name issue is resolved by changing these lines of code

Set c = .Find(strFind, LookIn:=xlValues, lookat:=xlPart)

rng.AutoFilter Field:=rFilter, Criteria1:=strFind & "*"  'What to Filter For

I've tested pretty thoroughly but let me know of issues.

[garden_gnome]

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

[jaslake]

Hi Cory

How are you calling the sort code...just doing it manually for the moment?

Never mind...I see it's called from the Amend and Add Buttons.

[jaslake]

Hi Cory

You work on "breaking it"...see if you can. Then, we'll explore this

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

I'm not at all certain what you're looking for but we'll figure it out.

As for this

If there was any way I could re-pay you, I would

Style points are always good...click on the star in the lower left...that's how you pay...if you wish.

[garden_gnome]

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

[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.

[garden_gnome]

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:

Sub SortData()
Application.ScreenUpdating = False
'Sort MFR Data
    ActiveWorkbook.Worksheets("MFR_List").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("MFR_List").Sort.SortFields.Add Key:=Range( _
        "B2:B65000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("MFR_List").Sort.SortFields.Add Key:=Range( _
        "E2:E65000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("MFR_List").Sort.SortFields.Add Key:=Range( _
        "A2:A65000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("MFR_List").Sort
        .SetRange Range("A1:F65000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Application.ScreenUpdating = True
End Sub

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:

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:

Dim wb As Workbook
Set wb = "MFR Userform Test Book v.X.xls"
Dim ws As Worksheet
Set ws = "MFR_List"

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

[garden_gnome]

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. :-)

[BigBas]

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:

                            LC = .Cells.Find(What:="*", After:=[A2], _
                                    SearchOrder:=xlByColumns, _
                                    SearchDirection:=xlPrevious).Column

It formerly read:

                            LC = .Cells.Find(What:="*", After:=[A2], _
                                    SearchOrder:=xlByColumns, _
                                    SearchDirection:=xlPrevious).Column - 1

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.

[garden_gnome]

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

[chipo]

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

[jaslake]

Hi chipo

Welcome to the Forum!!
You'll need to start your own Thread...see Forum Rule #2

[BigBas]

Hi garden_gnome,

Sorry I missed the second request.

THe following line of code should be amended.

Original:

    LR = Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious).Row

Correction:

    LR = ws.Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious).Row

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

[garden_gnome]

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

[BigBas]

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?

[garden_gnome]

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

[BigBas]

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

Try:

    LR = ws.Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious).Row

Let us know if it works.

[garden_gnome]

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

Sub SortData()
Application.ScreenUpdating = False
    'Dim ws As Worksheet
    'Set ws = Worksheets("MFR_List")
    'Sort MFR Data
        With ws
            Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("E2") _
                        , Order2:=xlDescending, Key3:=Range("A2"), Order3:=xlAscending, Header:= _
                        xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
                        xlSortNormal
        End With
Application.ScreenUpdating = True
End Sub

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

[BigBas]

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:

        With ws
            .UsedRange.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("E2") _
                        , Order2:=xlDescending, Key3:=Range("A2"), Order3:=xlAscending, Header:= _
                        xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
                        xlSortNormal
        End With