Using Checkboxes to Produce Line of Text

[washlo]

Hello,

I am trying to generate a string of text used for tagging purposes. I have a list of keywords that I will need to select different combinations of and then copy all the keywords to one line. My idea was to create a checkbox to the left of each keyword that would be used to generate a final string of text. However, this could be overly complicating things. I don’t really care what method is used (it could be an “X” in a cell), I just need to be able to select a mix of keywords and have a line with all of them generated. This will save me a TON of time for a very tedious task at work.

If anyone has any recommendations please let me know. I greatly apologize, but I know next to nothing about VBA, macros, scripts, and all that. I am willing to look over any documents, videos, or instructions though.

I will attach a screenshot and sample file with an example of what I’m hoping to achieve. The output can be in a cell or text box, or whatever is easy.

Thank you!

Sample file:
Checkbox Test.ods

[anon73440385]

Why did you delete the sample file you provided to this question? Deleting the basis of my answer may made it hard hard to follow the answer for future readers of this question.

[washlo]

Hi Opaque. I deleted the sample file because I (mistakenly) thought it didn’t upload properly. I thought the whole “fakepath” part was indicating an error on my end. I will edit my post to include it again. Thank you so much for the answer below. I’ll take a look and try to replicate it.

[anon73440385]

Hello,

please see the following modified file: CheckBox-Modiffied.ods-
What has been changed?

• Each checkbox control has been modified to have a linked cell (Right click -> Control Properties -> Tab: Data -> Option: Linked cell)
• Formula in cell H10 contains array function: {=TEXTJOIN(" ",1,IF(A$2:E$8=1,B$2:F$8))} which joins all values their cells left containing an 1 (=TRUE) into a string.
• Modified text color to white in colums A,C and E to hide value TRUE when check box linking to the cell is checked. Could be also achieved by other means (adapted check box size, using Hide all when using protected sheet)

Note(s):

• For simplicity the formula is not elaborated, since it also checks the Color|Size|Style columns for value 1 and should be probably split into three separate TEXTJOIN() calls similar to
  {=TEXTJOIN(" ";1;IF(A$2:A$8=1;B$2:B$8)) & " " & TEXTJOIN(" ";1;IF(C$2:C$8=1;D$2:D$8)) & " " & TEXTJOIN(" "; 1; IF(E$2:E$8=1;$2:F$8))}

• To anchor the check box controls To Page should be revised (I’d anchor them To Cell)

• If you add new item to columns and/or new columns (categories) the formula needs to be adapted to the new resulting range (but this seems to be obvious).

More about array functions see LibreOffice Help - Array Functions

Update (according to OPs comment)

See the following sample not using form controls but just using x to column left to the keywords:
CheckBox-Modiffied-NoCheckBoxes.ods

Hope that helps.

If the answer is correct or helped you to solve your problem, please click the check mark () next to the answer.

[washlo]

Wow, that will save a lot of effort. Thank you so much for the help!

[washlo]

@anon73440385, it seem that the checkboxes are causing issues for me. The screen flickers and the response is very slow as I add data. I thought I could replace them with an “x” and the following formula, but it’s not working for me. I read some about TEXTJOIN and IF seeing that maybe I could use SEARCH. I tried quite a few different combinations and formulas but I just can’t seem to get it to work. Always ends in “#VALUE!” Any advice?

=TEXTJOIN(" ",1,IF(A$2:E$8="x",B$2:F$8))

[anon73440385]

@washlo - 3 errors:

1. Adding data in general requires adaption of A$2:E$8 and B$2:F$8

2. Checking a checkbox produces a TRUE() ( equals a numerical 1) into the linked cell. Thus checking IF(A$2:E$8="x" makes no sense at all, the linked cell will never have an x and IF() will evaluate to FALSE and thus output will be empty string.

3. Incorrect use of array functions That’s why I provided the link to LibreOffice Help - Array Function. To correctly enter an array function you need to enter =TEXTJOIN(" ",1,IF(A$2:E$8=1,B$2:F$8)) and finalize using CTRL+SHIFT+ENTER (ENTER is wrong an will not produce the array function - please watch the curly brackets { and } appearing after having pressed CTRL+SHIFT+ENTER)

[anon73440385]

Ah - I seem to have misunderstood the x thing: You are trying to get rid of the check boxes at all? If yes - forget about item 2. in my previous comment (see the new file on Update of my answer)

[washlo]

@Opaque, it was CTRL+SHIFT+ENTER that I was missing. Thank you so much for your help. It is working fantastically now. I also learned a lot of new information about formulas trying to troubleshoot.