Calc - How to refer to a Group Box in a formula?

Hi. How to refer to a Group Box or single Option Button inside a group? I want to pass the output from the group box (optionally radio buttons in the group) to the given cell in my sheet.

The GroupBox is just a kind of Shape and has no relevant functionality I would know of.
You don’t need to refer to it.
The OptionButtons are what introduces the functionality, and used in a spreadsheet you again don’t need to refer to them. They deliver state information each to its LinkedCell.

OptionButtons.ods (15.3 KB)

P.S. indeed, I was in a hurry.

  1. Group boxes are merely decorative elements with an additional label.
  2. The value you can assign to a button is written as text, so my values in A1 are “1”, “2” and “3” which may require a conversion by means of VALUE if you set some restrictive option under Tools>Options>Calc>Formula [Details…]. I have chosen to never convert anything automatically. By default, strings representing integer numbers are converted on the fly. It’s always safe to use VALUE or NUMBERVALUE for explicit conversion of numeric strings.
1 Like

One explanation in addition:
@Villeroy’s example does not contain a GroupBox.
This way, I assume, he wants to state:
It’s not the GroupBox that makes a functional group of OptionButtons, but the GroupName.
This holds even for OptionButtons placed in different GroupBoxes.
It also holds independent of the question whether the shapes hosting the Buttons (and the Box) are grouped to a GroupShape (a Drawing object) or not.
Even the LinkedCells may be different for the OptionButtons of a group.

1 Like

Thank you guys. That’s what I looked for.

Hello again. I’m facing another challenge. Is it possible in Calc to make user to always choose one of the OptionBoxes in the group? I mean they cannot close the sheet without ticking all the required OptionBoxes. Could that be done either by showing a popup window or by any other suggestive way?

You can add some conditional text or conditional formatting as a warning when the required data are not given. Something like: =IF(COUNTA(A1:A5)<5;COUNTA(A1:A5)&" missing values";“OK”)

Many thanks that’s realy useful, but is there any way to show a popup window or anything cointaining that message?

Yes, with a macro. Set that macro as the event handler for Tools>Customize>Events>Document is going to be closed.

I’m not sure you can actually cancel an event from LO BASIC. But you can do something cheesy like the this macro:

Function Warning()
	Dim CellText
	CellText = ThisComponent.Sheets.getByIndex(0).getCellByPosition(0,5).getDataArray(0)(0)(0)
	If CellText <>"OK" Then MsgBox("Warning: " & CellText & ". Press Cancel in the next dialog to return to the form.")
End Function

CloseWarning.ods (13.1 KB)