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. :clap::hi:

[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.