Cannot understand how to write a loop to create an array to fill a listbox that would be able to revise the original Table

Question

Excel VBA - Excel 2016 with no ActiveX controls. I am a baby/newbie dealing with government firewalls, and although I have 2016 excel, it appears the permissions do not exceed 2010 vba tools. It has been very frustrating, as I have learned what tools have been blocked from use.

I am a little advanced. Took some courses with Leila Gharani. And stalk YouTube like you would not believe. I know how to create a listbox. Right now, I am developing a VERY SIMPLE inventory management system. That must include inventory reports, pivot tables and charts, etc.

I have created a form that is a combination data entry and listbox in one. Listbox shows all transactions to date from all locations and for all products. I even figured out how to ensure the most recent entries show at the top of the listbox. This form can allow the user to enter new data or edit the current data within the Master Table. I also have a very basic form that only allows the addition of new transactions (no listbox).

My design has gone over very well. With one catch. The users want me to change combination entry for, such that the listbox will update when the person chooses both a location and product to enter into a transaction. They want the listbox to provide a complete list of transactions for the product based on the location selected. They feel this will help prevent redundant entries and allow for revisions as entries are being made if necessary.

I managed to create a listbox form that met half the request (it filters the transactions to only those associated with the product and location selected). I made it using a new worksheet where I have embedded an Excel Advanced Filter with Multiple Criteria. I learned this from a VERY OLD (2015) Video tutorial that dealt with creating a multiple criteria advanced filter in one video, then in the next video they show how to run the same filters from a userform. Key details are that the listbox has to be populated from the results table created on a new worksheet. As such, I cannot do the second part of the task which would be to allow the user to select one of the transactions (if necessary) and make any changes required to the master data before entering any new data.

So I changed tactics, I created a new column in the original Master Data, that would update when the user selects a location and product from the comboboxes in the entry form. It will indicate TRUE for any row that matches the requirements and FALSE for no match.

I figured then I could do an easy loop to populate the listbox with the desired columns AND the original row columns "may" be able to be retained (somehow???). That is again where I got stuck. I need the listbox to contain 6 columns. And that does not include the column with the true and false. I know I need to use loops and variables (possibly arrays). I searched hard to see if there were similar questions, and there were some, but they don't use the same vba language I have to use, and I was getting confused about whether the code worked or not. Whether it truly returned columns or just one line with data separated by tabs.. And whether or not it could be selected and the entry revised if necessary.

I hope this is detailed enough for you to provide some guidance or links to posts that may help me in my endeavor. I have not shared any code because each time I hit a wall in regards to what the next step should be. I will not be downloading any files, and I truly want to learn how to do this myself.

Thanks for your patience and help.

Answer 1

I'm sorry if I'm mistaken to get what you want.

Anyway, maybe the code below can give you a little help to what you want.

Dim rw() As Variant
Dim idx As Integer

Sub PopulateListBox()
Set Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))

If Application.CountA(Rng) = 1 Then
Set Rng2 = Rng
Else
Set Rng2 = Rng.SpecialCells(xlCellTypeVisible)
End If

With ListBox1
    .Clear
    .ColumnCount = 6
    .ColumnWidths = "20,60,40,40,40,40"
End With

For Each Cell In Rng2
    With ListBox1
        .AddItem Cell.Value
        .List(.ListCount - 1, 1) = Cell.Offset(0, 1).Value
        .List(.ListCount - 1, 2) = Cell.Offset(0, 2).Value
        .List(.ListCount - 1, 3) = Cell.Offset(0, 3).Value
        .List(.ListCount - 1, 4) = Cell.Offset(0, 4).Value
        .List(.ListCount - 1, 5) = Cell.Offset(0, 5).Value
    End With
Next Cell
End Sub

Private Sub CommandButton1_Click()
If ActiveSheet.AutoFilterMode Then
    If ActiveSheet.FilterMode = True Then
    ActiveSheet.ShowAllData
    End If
Else
ActiveSheet.Cells.AutoFilter
End If

Set Rng = Range("A1", Range("A" & Rows.Count).End(xlUp))

If TextBox1.Value <> "" Then
Rng.AutoFilter Field:=2, Criteria1:=TextBox1.Value, Operator:=xlAnd
End If

If TextBox2.Value <> "" Then
Rng.AutoFilter Field:=3, Criteria1:=TextBox2.Value, Operator:=xlAnd
End If

Call PopulateListBox

End Sub

Sub RowNum()
Set Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
If Application.CountA(Rng) = 1 Then
Set Rng2 = Rng
Else
Set Rng2 = Rng.SpecialCells(xlCellTypeVisible)
End If

For Each Cell In Rng2
ReDim Preserve rw(p)
rw(p) = Cell.Row
p = p + 1
Next

With ListBox1
 For i = 0 To .ListCount - 1
 If .Selected(i) Then
 idx = i
 Exit For
 End If
 Next i
End With
End Sub

Private Sub ListBox1_Click()
Call RowNum
Range(Cells(rw(idx), 1), Cells(rw(idx), 6)).Select
TextBox4.Value = Cells(rw(idx), 1).Value
TextBox5.Value = Cells(rw(idx), 2).Value
TextBox6.Value = Cells(rw(idx), 3).Value
End Sub

Private Sub TextBox4_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If (KeyCode = vbKeyReturn) Then
Call RowNum
Cells(rw(idx), 1).Value = "'" & TextBox4.Value
ListBox1.List(idx, 0) = Cells(rw(idx), 1).Value
End If
End Sub

Private Sub TextBox5_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If (KeyCode = vbKeyReturn) Then
Call RowNum
Cells(rw(idx), 2).Value = StrConv(TextBox5.Text, vbProperCase)
ListBox1.List(idx, 1) = Cells(rw(idx), 2).Value
End If
End Sub

Private Sub TextBox6_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If (KeyCode = vbKeyReturn) Then
Call RowNum
Cells(rw(idx), 3).Value = StrConv(TextBox6.Text, vbProperCase)
ListBox1.List(idx, 2) = Cells(rw(idx), 3).Value
End If
End Sub

Private Sub UserForm_Initialize()
If ActiveSheet.AutoFilterMode Then
    If ActiveSheet.FilterMode = True Then
    ActiveSheet.ShowAllData
    End If
Else
ActiveSheet.Cells.AutoFilter
End If
Call PopulateListBox
End Sub