Hi all,
I am currently recreating a document control spreadsheet and wanting to add some search function through the use of macros and VBA.
I have used the "Display All Matches from Search in Userform ListBox? guide from this website but I am wanting to go a little further with it and everything that I have tried will not work.
The extra steps that i would like to add are as follows,
1. Column heads from my worksheet page.
2. the ability to select a result from the listbox and then click another bottom to show the data from each column in its own textbox. The idea of this is to have limited information in the search field but more information in the show form.
i.e on the search field, the document name, number version number and document status will be shown . When you select the document you want and then click the show button you will be shown the same data as the search field but additional information such as the document reviewer, date of review, validity of the document, where the document belongs.
I have added pictures of what I am trying to explain.
Thank you for any help that you can provide.
Cheers
Hi Matthew,
What do you mean with: "1. Column heads from my worksheet page."
See if the attached search form helps, where you can select the column where the search will be performed.
Hey Catalin,
Apologies for my poorly written explanation of what I am trying to do.
The column headers refer to the headers on the list box and having them the same as the column headers on the worksheet. I have uploaded a video to this reply which should explain this in a clearer manner.
The Video also shows what I am trying to do in relation to the search and display functions. The attached picture is what I would like to display on the second userform. Thank you for your help.
Hi Matthew,
List column headings should be avoided, it is usually used when your list refers to a sheet range that HAS headers, this way the list reads headers from sheet.
You can do what I did in the form I sent, simply put bordered labels ABOVE the list, because in your case the list data does not come from sheet, it is written by code..
Hey Catalin,
That makes perfect sense. Thankyou heaps.
Would you happen to know hoe I could create another userform to show all of the data for a document that is selected from the initial userform?
Thank you for your help.
Cheerse
I think you want to know how to use the document selected to populate another form, not how to create a form, you seem to know how to create one.
Basically, when you populate that initial form with all results data, each entry from the search results list should have the sheet row information in a hidden list column (column width for the last column is 0).
Use then the List Double Click event to open that second detailed form , you will just have to pass the row number where the document info is located to a function that takes that row number and populates the form fields.
To get the selected entry from a list use:
'safety line:
If ListBox1.ListIndex =-1 Then Exit Sub ' no row selected
Dim Rw as Long
Rw=CLng(ListBox1.List(ListBox1.ListIndex,3))
As microsoft says:
The ListIndex property contains an index of the selected row in a list. Values of ListIndex range from -1 to one less than the total number of rows in a list (that is, ListCount - 1). When no rows are selected, ListIndex returns -1.
When the user selects a row in a ListBox or ComboBox, the system sets the ListIndex value. The ListIndex value of the first row in a list is 0, the value of the second row is 1, and so on.
Hey Catalin,
Thank you for you help and guidance with this coding. I do apologies for how useless I have been.
I have tried many different ways to get the code you sent through to work but I keep getting a syntax error.
I have attached a demo version of the main document to this reply which contains all of the code and user forms.
To extract the row index from column 4 of the results list, you have to store it first there, when you search and populate the results list.
Then this code will work:
If Results.ListIndex = -1 Then Exit Sub ' no row selected
Dim Rw As Long, Wks As Worksheet
Set Wks = ThisWorkbook.Worksheets("Forms")
Rw = Results.List(Results.ListIndex, 3)
DocumentDetails.TextBox2.Value = Wks.Cells(Rw, "A")
DocumentDetails.TextBox6.Value = Wks.Cells(Rw, "C")
DocumentDetails.TextBox3.Value = Wks.Cells(Rw, "B")
'continue with the rest of the textboxes needed
DocumentDetails.Show
End Sub
Hey Catalin,
You are amazing!!
Thank you heaps for your knowledge and experience with helping me solve this coding.
Cheers
Matt 🙂
