Ask Your Question

Is there still no easy way to make a cell a checkbox?

asked 2021-02-23 08:35:55 +0100

soupuser gravatar image

updated 2021-02-23 08:36:33 +0100

I know there is the form way, but if you have several rows with dozens of boxes each, and maybe have to move stuff around, then that method is really slow and clunky.

In Google Docs I simply insert a checkbox into a cell and then that cell behaves like it has a boolean value depending on the status of said checkmark.

I can copy and paste said row like any other row including any checkmarks it might contain. Further more if I add a row above or below I automatically get in the new row also a checkmark. I even can autofill by dragging the row.

If I want something similar in LibreOffice I need to assign the cell it affects (or is there a way to tell it "current" / anchored cell?) manually. Which is slow. If I would add the checkbox via toolbar I also would have to remove the text "checkbox" afterwards and align the box with the cell via context. But I can't even access the cell below as it blocks the cell from being clicked. On top the horizontal alignment "center" doesn't work. ... and you see the value of the cell unless you set the cell text color to the background color.

So, is there a way as easy as in Google Docs?

edit retag flag offensive close merge delete


Let's think together what exactly you are looking for. Do you need an easy way to create an input form that is not easy for the user? Or do you want to make life as easy as possible for the end user, but do not see the boundary after which the computer will cease to be an assistant and become a burden? After all, your quest is about UI styling, right? There are certain standards for input forms, this has been developed over the years, users are already accustomed to this. Any innovation in this area very rarely leads to success, much more often it is a bad program.

How many dropdowns can you insert into one form?

JohnSUN gravatar imageJohnSUN ( 2021-02-23 09:27:30 +0100 )edit

I know the form function in Libre has a different goal in mind, and has really useful applications, but if you want to use quick and easy checkboxes into cells and work with those. There is no better way than Google Spreadsheets has done it. If you have ever used it and then tried to get to the same result in Libre you know what I mean.

soupuser gravatar imagesoupuser ( 2021-02-23 10:01:11 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2021-02-23 10:50:45 +0100

JohnSUN gravatar image

updated 2021-02-24 14:59:17 +0100

I don't know if this option is much easier than copying and reproducing checkboxes in Google Spreadsheets, but it is certainly easier than pasting controls into cells.

Create a custom cell style, name it eg myCheckBox.

Set a format code that will display an empty box for negative numbers (in terms of Calс "False" = -1) and the checked checkbox for positive and zero values (I used the format code ☐;☑;☐, you can choose any other to your liking)


Write a short function - event handler:

Function onDblClick(oEvent As Variant)
    onDblClick = False
    If oEvent.CellStyle = "myCheckBox" Then 
        If IsEmpty(oEvent.value) Then
            oEvent.value = True
            oEvent.value = Not oEvent.value
        onDblClick = True
End Function

Assign this macro to the "Double Click" event



Select all the cells on the sheet that you need and style them myCheckBox.

myCheckBox DblClick.gif

edit flag offensive delete link more



...the only problem - I don't see an event Double Click. Checked openSUSE 15.2 with LibreOffice and and AOO 4.1.9, Windows 10 and LibreOffice, Windows XP and None contains that event. What do I miss?

Opaque gravatar imageOpaque ( 2021-02-24 12:19:50 +0100 )edit

@Opaque Do you mean right click on sheet tab, context menu "Sheet Events"? Which events you see? I updated my answer, added a context menu image

JohnSUN gravatar imageJohnSUN ( 2021-02-24 12:55:25 +0100 )edit

@JohnSUN - now I know that I missed "there are two places where to assign macros". And I've checked (according to the event link provided in your answer) Tools - Customize - Events only, but not Sheet specific events. Thanks a lot for clarifying.

Opaque gravatar imageOpaque ( 2021-02-24 13:03:50 +0100 )edit

@Opaque I hope you noticed that in my format code the checkboxes are set the other way around, right? It is assumed that this is the form "Mark items for deletion" - here the user marks "Yes, delete" (checkbox ON), and the program interprets this as "Do not leave this item" (FALSE) Just an example of adapting a user interface for human convenience

JohnSUN gravatar imageJohnSUN ( 2021-02-24 13:11:40 +0100 )edit

Really class solution! Clean, quick and flexible.

Przemo gravatar imagePrzemo ( 2021-02-24 14:23:03 +0100 )edit

Thanks a lot! Really a masterpiece. Too bad it is such a round about way to get there, and it isn't in by default. Only problem I have now is that I didn't notice I was in the wrong macro and I managed to screw up the anaphraus one ...

soupuser gravatar imagesoupuser ( 2021-02-24 16:47:28 +0100 )edit

Works like a charm, though I replaced

If IsEmpty(oEvent.value) Then
    oEvent.value = True
    oEvent.value = Not oEvent.value

which assumes that the user never enters an invalid value manually and breaks if he does with

If IsEmpty(oEvent.value) Then
    oEvent.value = True
Elseif oEvent.value = True Then
    oEvent.value = False
Elseif oEvent.value = False Then
    oEvent.value = True
    oEvent.value = False

Which checks and replaces invalid values as well (though I'm sure there is a more elegant way to do that)

soupuser gravatar imagesoupuser ( 2021-02-27 14:26:06 +0100 )edit

@soupuser You are absolutely right - the user never enters a valid value. Indeed, event handling cannot be as simple as I have shown. It is even more difficult than you have shown. For example, a user can draw a shape on a sheet, simply because it seems beautiful to him. And then he double-clicks on it ...

JohnSUN gravatar imageJohnSUN ( 2021-02-27 15:57:12 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2021-02-23 08:35:55 +0100

Seen: 70 times

Last updated: Feb 24