[Calc] Check boxes and radio buttons in UTF8.

Sébastien C · Post by **Sébastien C** » Fri Jun 01, 2018 12:17 am

Even if it is far from pleasing everyone, and especially to those who do not like spreadsheets anyway (...

...), there are some occasions where using a spreadsheet for a small database, often temporary, can solve a lot of problems, staying well understood that the advantage of the flexibility of a spreadsheet is always counterbalanced by the disadvantage of its lack of features for manage the data.

We all know the checkBox control’s form. And we have seen elsewhere that it is very easy to link its state to the content of a cell, which will then take the Boolean value True or False. This link is made using the dialog box specific to the checkbox control. This implies that for each checkbox, it is necessary to systematically and manually set the link to the cell. So if you are considering a checkbox per line and your small base has 200 lines, you still have to be provided with some patience...

If there is one thing that I find great in the office suite that concerns us here, it is its intrinsic (and very deep) ability to perfectly manage the UTF8. However, the more it goes, the more the standardization of the icons contained in the tables of characters is regularized, and it seems to me a pity not to make fruit of it. A very basic search on the Web tells us, for example, that the graphics of check boxes, checked or not, has been defined for ages.

In addition, the spreadsheet gives us access to the HYPERLINK function. And we know, again, that it can also be used as a call to a macro which leaves, then, to imagine that one can, in Calc, to use it like multiple “buttons’s” controls, with a lot of variables depending on the content of the cells. LibreOffice had, at one time, the inconvenience of no longer offering this feature; this regression is now corrected. The advantage over a real form’s control is huge: it resides in the adaptation of the copy and paste, or even the very effective use of the “fill’s” command.

But a checkbox is not a button; or not exactly. Let’s say that it is a clickable object but that switches from one display to another. The same goes for a radio button, which interacts with a group of its equals.

The idea is to use the function HYPERLINK but by switching its display one time out of two... The idea is also (and above all) to be careful that the copy and paste is perfectly managed, this to set up a number of checkboxes or even radio buttons much faster than with the usual form’s controls. This requires passing the call values within the URL. In this context, it is accepted that knowledge of cell references, relative or absolute, is not superfluous knowledge to set the first control. After, it’s really a story of copy and paste...

The code is understandable naturally with the example of the spreadsheet provided here. The radio buttons do not follow the UTF8 encoding but use the “Wingdings” font (usually present on M$-Window$ systems) that is needed for the demonstration. So the characters U+F0A1 (a clock in UTF8) and U+F0A4 (a writing board in UTF8) correspond as well, in “Wingdings”, respectively to the drawings of a radio button, unchecked and checked. Similarly, editing the text of the cell’s formula of formatted in “Wingdings” may be somewhat problematic... Fortunately, Calc also offers us an editing field that flouts the font, by displaying the text of the formula in a standard font...

This shows that everything is about appearance. There is no limit since we could also extend this system to more than one character as it is only the case here. The principle is always the same: dynamically modify a cell formula with a click on itself. It will be noted again that the protection on the cells does not prevent the macro from acting to modify the formulas, which, in the present case, is perfectly accommodating.

It can be considered that the help displayed by the tips on hypertext links becomes troublesome because of their length. And online help teaches us:
“

Tips are always enabled.”.

A little research on the Web and tests a little blindly allowed me to understand that it is still possible, via the dispatcher, to remove the display of tips; the macro named swichDisplayHelpTip(), delivered here, gives an illustration of this. Note that the launch switches between a display permission and its opposite. It should be noted, however, that this command affects the whole suite and permanently; as long as it is not recalled and even if the software is closed and then reopened, the setting remains active. In addition, it affects all tips, including those that are sometimes so useful as the colors pickers.

Enjoy !

Code: Select all

' ╔══════════════════════════════════════════════════════════════════════════════════╗
' ║ Demo HYPERLINK function.                                                         ║█
' ╚══════════════════════════════════════════════════════════════════════════════════╝█
'  ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀

Sub demoButton()
	msgBox "You have clicked on me !", 64, "Demo HYPERLINK function"
End Sub


' ╔══════════════════════════════════════════════════════════════════════════════════╗
' ║ Checkoxes management.                                                            ║█
' ╚══════════════════════════════════════════════════════════════════════════════════╝█
'  ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀

Sub checkBox(myURL As String)
	Dim    mySheet As Object,    myCell As Object
	Dim myCheck(1) As String, myFormula As String

	Select Case cInt(getArgumentFromURL(myURL, "myType"))
	 Case 0 : myCheck(0) = "☐" : myCheck(1) = "☒"
	 Case 1 : myCheck(0) = "☐" : myCheck(1) = "☑"
	 Case 2 : myCheck(0) = "◄" : myCheck(1) = "►"
	 Case 3 : myCheck(0) = "M" : myCheck(1) = "F"
	End Select

	  mySheet = thisComponent.sheets.getByName("testCheckBox")
	   myCell = mySheet.getCellRangeByName(getArgumentFromURL(myURL, "myCell"))
	myFormula = myCell.formula

	If mid(myFormula, (len(myFormula) - 2), 1) = myCheck(0) Then   ' Mid is here a FUNCTION.
	   mid(myFormula, (len(myFormula) - 2), 1) = myCheck(1)        ' Mid is here an INSTRUCTION.
	Else
	   mid(myFormula, (len(myFormula) - 2), 1) = myCheck(0)        ' Mid is here an INSTRUCTION.
	End If

	myCell.formula = myFormula
End Sub


' ╔══════════════════════════════════════════════════════════════════════════════════╗
' ║ Radio buttons management.                                                        ║█
' ╚══════════════════════════════════════════════════════════════════════════════════╝█
'  ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀

Sub radioButton(myURL As String)
	Dim    mySheet As Object,   myCell1 As Object, myCell2 As Object, myGroup As Object
	Dim myRadio(1) As String, myFormula As String
	Dim          i As Integer

	Select Case cInt(getArgumentFromURL(myURL, "myType"))
	 '        Unchecked                 Checked                    BE CAREFUL : the character font is WINGDINGS (not UTF8).
	 Case 0 : myRadio(0) = chr(61601) : myRadio(1) = chr(61604)    ' U+F0A1 and U+F0A4
	                                                               ' Imagine here other sort of radio buttons...
	End Select

	mySheet = thisComponent.sheets.getByName("testCheckBox")
	myGroup = mySheet.getCellRangeByName(getArgumentFromURL(myURL, "myGroup")).rangeAddress
	myCell1 = mySheet.getCellRangeByName(getArgumentFromURL(myURL, "myCell" )).rangeAddress

	With myGroup
	 For i = .startColumn To .endColumn
	    myCell2 = mySheet.getCellByPosition(i, .startRow)
	  myFormula = myCell2.formula

	  If i = myCell1.startColumn Then
	   mid(myFormula, (len(myFormula) - 2), 1) = myRadio(1)        ' Mid is here an INSTRUCTION.
	  Else
	   mid(myFormula, (len(myFormula) - 2), 1) = myRadio(0)        ' Mid is here an INSTRUCTION.
	  End If

	  myCell2.formula = myFormula
	 Next i
	End With
End Sub


' ╔══════════════════════════════════════════════════════════════════════════════════╗
' ║ Extracts the values of variables (text) passed in argument with the URL call.    ║█
' ╚══════════════════════════════════════════════════════════════════════════════════╝█
'  ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀

Function getArgumentFromURL(sURL$, sName$) As String
	On Error Goto exitErr:

	Dim iStart%, i%, l%, sArgs$, a()
 
	iStart = instr(sURL, "?")
	     l = len(sName)

	If (iStart = 0) or (l = 0) Then Exit Function

	sArgs = mid(sURL, iStart + 1)               ' sArgs behind "?".

	a() = split(sArgs, "&")

	For i = 0 To uBound(a())
	 If instr(1, a(i), sName & "=", 1) = 1 Then ' Not case sensitive.
	  getArgumentFromURL = Mid(a(i), l + 2)
	  Exit for
	 Endif
	Next i

	exitErr:                                    ' Return "".
End Function


' ╔══════════════════════════════════════════════════════════════════════════════════╗
' ║ Just a little demonstration for say that the help is not always absolutely true. ║█
' ║                                                                                  ║█
' ║ The help say: "Tips are always enabled."                                         ║█
' ║ https://help.libreoffice.org/Common/Tips_and_Extended_Tips                       ║█
' ╚══════════════════════════════════════════════════════════════════════════════════╝█
'  ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀

Sub swichDisplayHelpTip()
	Dim oDisp As Object 

	oDisp = createUnoService("com.sun.star.frame.DispatchHelper") 
	oDisp.executeDispatch(ThisComponent.CurrentController.Frame, ".uno:HelpTip", "", 0, array())
End Sub