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