Check Box and Text

[ExcelNoob185]

Good afternoon all,

I will try to explain this as best i can forgive me if this comes across as a bit muddled.

As you will see in the image, in Column A i have a list of items, and Column B check boxes, in the Corresponding merged cells i have a formula that runs a Vlookup which works perfectly.

What i would like to do is in the merged cells J2 to N11 is have a formula that will when the relevant check box is ticked place into the box the relevant item, so for example i check 100 XS, C500 NCD it will populate into the merged cells Cover confirmed - Advised - (and the items that have been checked) now this will need to work with multiple boxes being ticked as well as individual boxes.

I hope i have made sense on this, and welcome any ideas you may have.

Thank you

 

[rpaulson]

try this

I assume your check boxed a linked to column B

Sub Check_Box()

Data = ""
For r = 2 To 29
 If UCase(Cells(r, "B")) = "TRUE" Then Data = Data & " " & Cells(r, "A")
Next r

[J2] = Data

End Sub

 

[bobsan42]

I will assume that the checkboxes are linked to the respective cells in the hidden column B. So when you check the box for 50 XS then cell B2 has a value TRUE.
then you can have a formula in the merged cell @ J2, e.g.:

=TEXTJOIN(CHAR(10),TRUE,FILTER(A2:A11,B2:B11=TRUE))

make sure you enable Text Wrap for this cell.

 

[ExcelNoob185]

try this

I assume your check boxed a linked to column B

Sub Check_Box()

Data = ""
For r = 2 To 29
 If UCase(Cells(r, "B")) = "TRUE" Then Data = Data & " " & Cells(r, "A")
Next r

[J2] = Data

End Sub

Thanks for the fast reply, you are correct that the check boxes are linked to the corresponding cells in Column B. However the VBA doesnt appear to be providing the output into the merged cells of J2 to N11

 

[ExcelNoob185]

I will assume that the checkboxes are linked to the respective cells in the hidden column B. So when you check the box for 50 XS then cell B2 has a value TRUE.
then you can have a formula in the merged cell @ J2, e.g.:

=TEXTJOIN(CHAR(10),TRUE,FILTER(A2:A11,B2:B11=TRUE))

make sure you enable Text Wrap for this cell.

Thank you very much this has worked perfectly and fits my needs. Much obliged to you

 

[bobsan42]

Thanks for the fast reply, you are correct that the check boxes are linked to the corresponding cells in Column B. However the VBA doesnt appear to be providing the output into the merged cells of J2 to N11

The code from rpaulson should also be working, I think.

 

[ExcelNoob185]

The code from rpaulson should also be working, I think.

Yeah this isnt working for some reason, ive saved the workbook as a Macro enabled but still nothing happens when i tick the relevant boxes

 

[bobsan42]

aaaah, this macro does not execute when you click the boxes.
It has to be assigned to each box, so when you click it - it fires the macro. Or you have to run it manually.

 

[rpaulson]

you can assign it to each check box, or you can add a button somewhere on you sheet and click it, or you can have it run whenever a cell un column B changes.