Ticked check boxes that create a new list - VBA?

[Jovannivk]

Hi all,

I've been struggling with creating a sheet that enables the user to hand-pick the account codes per checkboxes he wants to include in his sheet for further analysis. I was hoping one of you guys could help me a hand.

The data will for example look like this:

Column 1	Column 2	Column 3
Check box 1	1583-4410	LC Account-in-Transit 1
Check box 2	1583-4440	LC Account-in-Transit 4
Check box 3	1610-1010	Tenant - Accounts Receivable - (curr) 1
Check box 4	1610-1020	Tenant - Accounts Receivable - (curr) 2

Now, let's say the user ticks boxes 1,2, and 3. The most important data here is the data from column 2, the account codes. Now I can work with two ways.

Option 1:
We can put this data into one cell, let's say (1583-4410^1583-4440^1610-1010). From here on I can extract the data and I'll know what to do. However, this would be double the work for VBA if someone could answer option 2.

Option 2:
For the data where the check boxes are ticked, it would create a new list. For example:

Column 4	Column 5
1583-4410	LC Account-in-Transit 1
1583-4440	LC Account-in-Transit 4
1610-1010	Tenant - Accounts Receivable - (curr) 1

If anyone has a solution to this, I'd highly appreciate it. I appreciate you all. Thanks in advance.

[snb]

In a dynamic table the user can check/uncheck the items to be filtered in column 2, resulting in exactly your desired result.
So 'your checkboxes' have been builtin in Excel itself; no need to create them, nor to create a new list.

[Jovannivk]

In a dynamic table the user can check/uncheck the items to be filtered in column 2, resulting in exactly your desired result.
So 'your checkboxes' have been builtin in Excel itself; no need to create them, nor to create a new list.

Unfortunately, this wouldn't work. I wouldn't only need a table as an overview. I'd need a new list of data (without rows inbetween), because I use VBA to combine these account-codes with the units the user inserted as input.

However, checkboxes are not necessary indeed, perhaps only looks nicer.. This could also be done with a "Yes/No", I just do not know how to create a new list from this input

Include?	Account Code	Account Description
Yes	1583-4410	LC Account-in-Transit 1
Yes	1583-4440	LC Account-in-Transit 4
Yes	1610-1010	Tenant - Accounts Receivable - (curr) 1
No	1610-1020	Tenant - Accounts Receivable - (curr) 2

The same idea applies here. I need to find out somehow how to make a new table with the following output:

Account-code	Account Description
1583-4410	LC Account-in-Transit 1
1583-4440	LC Account-in-Transit 4
1610-1010	Tenant - Accounts Receivable - (curr) 1

[snb]

Post a sample file.
Are you famliiar with dynamic tables (Listobjects in VBA) ?
You can simply copy any filtered table to a new location.

in VBA

Sub M_snb()
  with sheet1.listobjects(1).range
    .autofilter 2, array("1583-4410","1583-4440","1610-1010"),7
    .copy sheet2.cells(1)
    .autofilter
  end with
End Sub

[Jovannivk]

Post a sample file.
Are you famliiar with dynamic tables (Listobjects in VBA) ?
You can simply copy any filtered table to a new location.

in VBA

Sub M_snb()
  with sheet1.listobjects(1).range
    .autofilter 2, array("1583-4410","1583-4440","1610-1010"),7
    .copy sheet2.cells(1)
    .autofilter
  end with
End Sub

I've managed to make a checklist with the yes/no options. Thanks for the idea though! For the ones interested:

I created an input table for the user like this:

Include?	Account Code	Account Description
Yes	1583-4410	LC Account-in-Transit 1
Yes	1583-4440	LC Account-in-Transit 4
Yes	1610-1010	Tenant - Accounts Receivable - (curr) 1
No	1610-1020	Tenant - Accounts Receivable - (curr) 2

With the following code I was able to export this from the input table (D17) to the export table (J17, with criteria table (d14):

Include?
Yes

I hid these two rows and now it will always filter on yes.

Code is below:

sub export()
Dim rgData As Range, rgCriteria As Range, rgOutput As Range
Worksheets("Accounts").Range("J17:M2000").ClearContents
Set rgData = ThisWorkbook.Worksheets("Accounts").Range("D17").CurrentRegion
    Set rgCriteria = ThisWorkbook.Worksheets("Accounts").Range("D14").CurrentRegion
    Set rgOutput = ThisWorkbook.Worksheets("Accounts").Range("J17")
rgData.AdvancedFilter xlFilterCopy, rgCriteria, rgOutput
end sub

Thanks guys.