Listbox columns from 2 sheets

[lifeson]

More listbox questions Im afraid :(
Attached is a demo of a workbook I am using.

How do I populate a list box from 2 sheets: :think:

Sheet "PackGroup" contains a list of various PackID's linked to a listID and the sequence they should be shown

Sheet "Pack" contains the unique information about each pack e.g pack description

Column 1 of the list box shows the packs linked to the list - works fine
Column 2 should show the description of the pack (using vlookup?) from sheet2
Column3 shows the sequence number from sheet1 - works fine.

[rory]

Can't you just add the VLOOKUP to sheet 1?

[Bob Phillips]

Private Sub UserForm_Initialize()
Dim c As String 'column to use
Dim idx As String 'lookup key
Dim ilastrow As Long
Dim aryItems
Dim sprem As String
Dim desc As String
Dim ans As String

Sheets("PackGroup").Select
'determine how many rows to check from sheet packgroup
ilastrow = Cells(Rows.Count, "A").End(xlUp).row
'Populate listbox with values
With lstAcc
ReDim aryItems(1 To ilastrow)
For i = 2 To ilastrow
If Cells(i, "A").Value = "504" Then
.AddItem Cells(i, "B").Value 'packID
On Error Resume Next
ans = Application.VLookup(Cells(i, "B").Value, Worksheets("Pack").Columns("A:B"), 2, False)
On Error GoTo 0
If ans <> "" Then .List(.ListCount - 1, 1) = ans
.List(.ListCount - 1, 2) = Cells(i, "C") 'sequence
End If
Next i
End With

End Sub

[lifeson]

Can't you just add the VLOOKUP to sheet 1?

I was aiming at keeping the size of the final file down by laying out the data like a relational database so the pack description is stored only once but the packID can be used many times :dunno

[lifeson]

Private Sub UserForm_Initialize()
Dim c As String 'column to use
Dim idx As String 'lookup key
Dim ilastrow As Long
Dim aryItems
Dim sprem As String
Dim desc As String
Dim ans As String

Sheets("PackGroup").Select
'determine how many rows to check from sheet packgroup
ilastrow = Cells(Rows.Count, "A").End(xlUp).row
'Populate listbox with values
With lstAcc
ReDim aryItems(1 To ilastrow)
For i = 2 To ilastrow
If Cells(i, "A").Value = "504" Then
.AddItem Cells(i, "B").Value 'packID
On Error Resume Next
ans = Application.VLookup(Cells(i, "B").Value, Worksheets("Pack").Columns("A:B"), 2, False)
On Error GoTo 0
If ans <> "" Then .List(.ListCount - 1, 1) = ans
.List(.ListCount - 1, 2) = Cells(i, "C") 'sequence
End If
Next i
End With

End Sub

I knew it would be something using vlookup -honest :whistle: I just didnt know how to pass the values.

I have added the code as liste and it works fine for the first form that appears but I am having a problem (theres a surprise!) with the linked form that appears for each item selected from the first form

The second form shows which materials are linked to the packs from sheet "PackMat" but seems to be out of sync

it shows the mats for the previously selected record :help

[Bob Phillips]

I am not really clear what you problem is here, but I have to say that I don't think that most of us here would have done it that way.

Independent tables are all very well and good when you have a proper database, but Excel is not a database, and works much better with flatfile layouts. I know that means repetition of data, but so what? When we work with databases, we would then use an SQL query that joins across many tables to retrieve the data that we are interested in. You could do the same with Excel, but not when the database is in the same workbook as the logic (actually this raises another best-practice, keep the data and the logic and the presentation layers separate), as you can only query against a disk file, not the file in memory. You could create a routine which emulates SQL and goes and gets all of the data into a simple m*n array, and this would be far better than the way that you are pulling in the data bit by bit as you go (better as a design technique, better for coding, and better for debugging), or yuou could de-normalise the data and flatten it.

Either way, I think a re-design would be a better approach.

[lifeson]

To be honest I half expected to hear that answer :mkay

It has all come about from our company preventing users creating forms in Access so I thought I would try and replicate the data in excel but following the access data layout.

[Bob Phillips]

Even if they prevent you creating forms in Access, you can still save the data in an Access file in your table structure and use SQL queries via ADO to get at the data. It would of course mean writing a simple app to write/delete to/from the database as well, but as I say, that is simple.

Here is a thought for you:
- do you want to keep the data in that format?
If yes, start of with my suggestion of writing a data access layer (fancy words for some macros, but pre-designed, pre-thought) that will get the data from those tables in a de-normalised format, in other words as an example you pass the PackID and then via the data access layer you retrieve an array of all the data associated with that ID that you are interested in. You then work your way through that array in the child forms. This would be easy to change to storing the data in a real database (yes I even include Access as areal database in this context), all that you would then need is to change the data access layer to get it from a database via an SQL query rather than the Excel tables.

BTW, when working with tables in Excel, it is a good idea to use defined names.

[lifeson]

That sounds well beyond my capabilities
Are there any example around you know of?
Would access need to be installed on the machine or can I just read the access.mdb file?

[Bob Phillips]

It should be relatively simple, if you sit down and work it through.

First determine what the resultant arry needs to look like. Build a simple test macro to populate it with dummy data.

Then write some code to get it from that array and use in the forms. Test it using the test data above.

Once you are happy that the access to the array is correct, then write the real code to populate it. I hesitate to say it because it will add another layer to scare you, but it would be far simpler if you used a class.

And no, you don't need Access to read/write to an mdb file.