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

[soupuser]

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?

[JohnSUN]

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.

[soupuser]

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.

[JohnSUN]

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
		Else
			oEvent.value = Not oEvent.value
		EndIf 
		onDblClick = True
	EndIf 
End Function

Assign this macro to the “Double Click” event

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

[anon73440385]

@JohnSUN

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

[JohnSUN]

@anon73440385 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

[anon73440385]

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

[JohnSUN]

@anon73440385 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

[Przemo]

Really class solution! Clean, quick and flexible.

[soupuser]

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]

Works like a charm, though I replaced

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

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
else
	oEvent.value = False
EndIf 

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

[JohnSUN]

@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 …