Works perfectly, thank you!
With that code you cannot populate a header row in the listbox, however you should be able to select the first row in the listbox.
Are you having this problem with your sample file?
I was having the same issue with the sample file. However the row selection in the listbox is being controlled with Function MRSelected_List so I just modified that from MRSelected_List = i to MRSelected_List = i+1 and I was able to get back functionality to select all rows within the listbox. I can add Column labels to address the header issue.
With some additional testing this morning, I did find one other issue. Try selecting MD6 and then click "Edit Record". The values populating the form are for record MD4.
Because the selected row value in the listbox is not equal to the row number in the source DataTable, when the Edit Record button is clicked, the values brought back to the form are incorrect. For listbox entries MD1, MD2, and MD3 it looks okay because the rows are sequential; however, for MD6 the array is skipping two rows containing other project IDs.
Is there an easy way to correct this or do I need to have a filtered array (like you provided for the listbox) to set the row value properly?
As cross post https://www.mrexcel.com/board/…tch.1168920/#post-5680096
I need to work with this a bit more to better understand the code provided. I'm trying to learn as I go with this and I'd like to work through the code myself a bit. However, if I can't figure something out I may ask for further explanation.
On first attempt, the functionality seems to work great. However, the only issue I have is that I cannot select the first row in the listbox. I switched the list property .ColumnHeads to True and I regained selection functionality for all of the records but the headers are blank.
I tried changing the Range to .Range("A1:O1000") to include the column headers but now I get a Run-time error '1004': Application-defined or object-defined error.
Any thoughts on this?
as posted on MrExcel https://www.mrexcel.com/board/…-array-and-match.1168920/
Fluff13 I did see the cross post comment but I thought that meant another Forum within OzGrid. I'll make sure to include it for other site forums in the future.
I'm new here, give me a break lol. I'll get it down.
As noted on the other forum, I'll give this code a try shortly and provide some feedback. Thank you for your help!
Thank you royUK for adding the code tags. I read up on the forum rules a bit more after you posted.
I found a few examples of displaying specific rows but each one has had a "twist" in the approach and I could not figure out how to implement with my code.
I am attaching an example workbook as requested. Essentially, for whichever project ID is selected on the "Controls" sheet, I'd like the Material Reservation UserForm to display only those records in the listbox.
Any guidance would be greatly appreciated. Thank you.
MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question
Relatively new with VBA and doing my best to learn it. I have a userform which has a listbox. To this I would like to add some type of Match function so that the listbox only displays the rows in which a certain value is displayed. For example, the project ID is displayed in Sheet1 cell B1. In the table, the corresponding project IDs are in Sheet2 column A. I would like the listbox to only show the rows in which the value in Sheet 2 Column A match the value in Sheet 1 cell B1.
Currently the listbox displays all of the rows from the sourced table with only select columns. How can I modify this to include the Match function as described above. Open to other methods as well (i.e. filter, etc).
- Sub MRReset
- With MRForm
- .ComboBox1.Value = ""
- .ComboBox2.Value = ""
- .ComboBox3.Value = ""
- .ComboBox4.Value = ""
- .TextBox1.Value = ""
- .ComboBox6.Value = ""
- .ComboBox7.Value = ""
- .TextBox2.Value = ""
- .ComboBox9.Value = ""
- .ComboBox10.Value = ""
- .TextBox3.Value = ""
- .MRRowNumber.Value = ""
- End With
- sn = Sheets("DataTable").Range("A1:O1000").Value
- sp = Application.Index(sn, [Row(1:999)], Array(1, 7, 15))
- 'Referenced from a third party with original source: VBA for smarties: Arrays, Title 6.7.1
- End Sub