Build ListBox using Selected Table ColumnNames via Array

[inddon]

Hello There,

I would like to see if the below requirement is possible to achieve using VBA. The requirement is to have a ListBox which is based on an Excel table. The User will pass the Table Name and the Table Column Names(x) which will be used to build the ListBox using Array.

With my little VBA knowledge, I find it a challenging one. Really look forward to see this working.

I have attached a sample workbook for your reference.

Look forward to hearing from you.

Many thanks & regards,
Don


Requirement:

------------------------------------------------------------------------------------------------
Bring Select Table Columns into ListBox using Array with Search Functionality
------------------------------------------------------------------------------------------------
The Program will have a Sub with parameters TableName and TableColumnNames to be listed in the ListBox with max 5 TableColumNames

Eg. Sub BuildListBox(TableName, ColumnName1, ColumnName2, Columnname3, ColumnName4, ColumnName5)
------------------------------------------------------------------------------------------------

Call BuildListBox( TableName = "Tbl_Currency"
ColumnName1 = "Currency Code"
ColumnName2 = "Currency Name" )
The other ColumnName parameters will have default empty value


------------------------------------
Below logic for Sub BuildListBox
------------------------------------
Step 1. Based on the sub BuildListBox parameters ColumnName(x), build Array with that many columns

Step 2. In this case: Columns to be selected from Table "Tbl_Currency" for ListBox
-Currency Code and Currency Name

Step 3. The above Step 1 selection of columns will define number of columns Array will have

Step 4. On Load UserForm1:
- Perform Step 1, 2 & 3
- Using the parameter TableName, display all Data and the Column Header in the Listbox
- Adjust the column widths

Step 5. On button "Search" with some string entered eg. "upee":
- Step 1, 2 & 3: Array is already loaded with all data
- Search Array for the "Search String Value"
- If found, display all the records which satisfy the search string in the Listbox
- eg. if Search String = "upee", then it should list all rows from Array where string = "rup"

ListBox will show 3 rows:
1. 'Indian Rupee'
2. 'Pakistani 'Rupee'
3. 'Indonesian Rupiah'

Step 6. On button pressed "OK"
- From the ListBox, it should display the values to the worksheet:
Range("FCurrencyCode) = ListBox.FirstColumn.value
Range("FCurrencyNamee) = ListBox.Second.value
- Clear the Array

Step 7. On button pressed "Cancel"
- Clear the Array

 

[Kenneth Hobson]

Rather than arrays per se, I would use a scratch sheet to do the work.

You can then use RowSource rather than List so that your header row is the first row. After copying the data to the scratch sheet, I would maybe used Advanced Filter. How to determine the columns is unclear to me. If it is just the first two columns and you want to filter based on 4th column, once filtered, the RowSource could be all the columns but maybe only show the first two columns?

As for column spacing in the listbox, that depends on your font size among other factors.

 

[inddon]

Hello Kenneth,

Thank you for your reply and your advise.

In one of my previous post, Luke also demonstrated the same approach as you had mentioned.

I went a bit further to make it more flexible. I am copying the data from the base worksheet table into a worksheet 'HelperSheet', convert it into a table and then filter it.

Till the above it works good. Now the only problem is that I cannot get the filtered table to the ListBox

I have uploaded the sample workbook for your review.

Could you please advise?

Many thanks & regards,
Don

 

[Kenneth Hobson]

I did it this way. You can probably modify your routine to use some of these concepts. Most were already used in yours.

Rename your Sub and paste this one in. I generally add a 0 to the other Sub and 1 to version 2 of the Sub and not numbers for the production version. After t works well, I would delete other versions. That way, you can easily switch which Sub is used for the control event as you design it.

Private Sub cmd_Search_Click()
  Dim ws As Worksheet
  Set ws = Worksheets("HelperSheet")
  With Worksheets("Currency")
    ws.UsedRange.Clear
    .ListObjects("Tbl_Currency").Range.AutoFilter Field:=4, Criteria1 _
        :="=*" & TB_Search.Value & "*", Operator:=xlAnd
    Intersect(.Columns("B:E"), _
      .UsedRange.SpecialCells(xlCellTypeVisible)).Copy ws.[A1]
    ListBox1.RowSource = ws.UsedRange.Address(external:=True)
    .ListObjects("Tbl_Currency").Range.AutoFilter Field:=4
  End With
End Sub

 

[inddon]

Hi Kenneth,

Thank you for your the code. It works good. I had a similar code which filtered the base table.

In real scenario, I have all the Master Data (eg. Currency, Countries, etc.) in a separate file. I do not want the code to filter the Master Data file (Currency).

In my previous sample workbook, the code copies the columns from the base table (currency) into the worksheet "HelperSheet" and does the filter on the table in the worksheet "Helpersheet"

The next step is to assign the already filtered table in the worksheet "Helpersheet" to the ListBox

eg.

Me.ListBox1.RowSource = <TableName.Filtered.range>

Is there a way to assign in the above manner or looping through the filtered table and assigning it to the ListBox?

Look forward to hearing from you.

Regards,
Don

 

[Kenneth Hobson]

Resource allows the header row. It does not allow discontinuous range. One could use a similar method by copying and pasting, filter that, then copy paste the visible range to another area on same scratch sheet.

In a few hours or tomorrow, I can work up an example using most of what you did if you like.

 

[inddon]

Resource allows the header row. It does not allow discontinuous range. One could use a similar method by copying and pasting, filter that, then copy paste the visible range to another area on same scratch sheet.

In a few hours or tomorrow, I can work up an example using most of what you did if you like.

Hi Kenneth,

That sounds a good idea, thank you. I was just thinking in the same lines (to paste the filtered table somewhere in the HelperSheet and then work it out.

You can update the last sample workbook. I will wait for your reply.

Thanks again.

Regards,
Don

 

[inddon]

Resource allows the header row. It does not allow discontinuous range. One could use a similar method by copying and pasting, filter that, then copy paste the visible range to another area on same scratch sheet.

In a few hours or tomorrow, I can work up an example using most of what you did if you like.

Hi Kenneth,

I have made the changes as suggested. Please find the attached sample workbook for your review. If this is okay or you have some other thoughts (like copy the filtered table columns to +5 columns to the right side)

Thanks again for your help

Regards,
Don