Add or remove items with a checkbox into a listbox

I have 10 checkboxes,

When the checkbox is checked, add the cell next to it to t a listbox in another sheet. (in the sample, the result ,must appear in sheet "relatorio", inside the yellow cells)

When the checkbox is unchecked , remove that item from the listbox.



Can anyone help me doing that ?

My recommendation is to use a Marlett checkbox rather than a checkbox control. It facilitates VBA code much better and provides a cleaner look to the sheet.

Here is the code in the sheet module for the APF Form.
The first code implements the Marlett checkbox for the applicable cells. The second code copies and deletes the list items.

Also, on the Import sheet, this formula is required to return the TRUE or FALSE value in place of the previous linked value returned from the checkboxes.

=IF('APF Form'!B7="a",TRUE,FALSE)

Please Login or Register  to view this content.

BTW, you are not using a listbox on the Relatorio sheet. A listbox is a control in the same sense a checkbox is a control. You are simply creating a list on a worksheet.

But, how i can have the results as you have done very well, only inside the yellow box (sheet9"relatorio"), and if are many boxes checked, which result in many sentences that would be out of the yellow box.

The amended code will apply a yellow fill to the target cells and a border around.

Delete the exisiting code and replace it with the code below.

Please Login or Register  to view this content.

Hello Nuno,

I added a macro to copy the data based on your first post. If this needs to be changed, let me know. This macro uses your original check boxes.

Please Login or Register  to view this content.

Hi Ross,

Thks, it is possible to use the macro with marlett boxes ?

I liked the macro, but with your macro,if more than one check boxes is checked, only one sentence appears, and i want all that are checked to appear.

I was thinking, and to appear also the letters A01 (that are in the other cells), like this : A01 - Safety bells, etc...

(see my last Excell sheet - FPA-testes)

Well, it was not what i have in mind, in the Sheet "relatorio", i want all the info only between the cells 16 and 21.
Because i need space to put pictures on the box above.
The colour was just to give you an idea of the limited space, where i wanted the info.

Please try to state *ALL* of the requirements at the beginning and clarify things like the comment on the color - otherwise, we have little choice but to assume a literal understanding and this causes wasted time and effort.

On the Relatorio sheet, you must avoid the use of merged cells in the target range (B16:B21) as they cause issues with VBA code. I've removed them, as well as turned off wrap text which is not needed. Additionally, the merged cells for the header row of this section were removed and center across selection was used.

Because you specify only the range B16:B21 as the target range, be aware you have many more selections on the APF Form sheet than you have rows to accommodate them. I have added to code to alert you to this and prevented using the selection when the Report section is full.

Here is the amended code, which worked perfectly during testing.

Please Login or Register  to view this content.

Ok, thks, sorry if i didnt explained well.

the range between the cells B16:B21, its to be fixed, but if more chkboxes are checked, i wanted to be added, like scroll or something.

( all the rest i already figured how to do, only need to solve this step and i am done )

i hope, now i could make me understand.

OK. I added a list box from the Forms Control tool box, created a named range in VBA and pointed the list box to the named range (using the "refers to" inside the formatting option of the control).

Each time an item is checked it is copied to a cell in Column-BB and the items will appear in the list box, which can be scrolled when more items appear in the list than can be shown.

Please Login or Register  to view this content.