I have a spreadsheet with 2 pages/sheets and want to be able to automatically adjust the width of a rectangle object in page 2 based on the value of a cell in page 1. Is there any way to do this?
To set the width of a shape to a value of a cell is very simple if the shape and the cell once are identified, and the action is triggered in one or another way.
The actual questions (imo) are therefore:
- How can I (do I want to) start the update?
- In what way will (should) I identify the cell?
- In what way will (should) I identify the shape?
Please tell as abouit your related considerations.
Hi
Thanks for your interest.
The source cell is a numeric cell with values 0 to 999
When the value changes I want an object which is a rectangle on page 2, to change width corresponding to the new value of the source cell
I am used to VBA programming, is there a way to do this in Libre Calc?
I think I understood your question well and since the usage of VBA macros is encouraged by MS as a means to enforce customer lock-in, I also supposed you are coming from that DeepSpace.
Fortunately I didn’t need to roam that space for decades, and therefore I can’t tell how somebody would do your task in Excel-VBA. But whatever programming tools and/or API you are using, a routine written to update any shape’s geometry based on a cell content must get passed information about what shape should be updated, and where to get the new values from.
How did (would) you identify the cell and the shape, and how would you trigger the Sub for the update? This was my question. Everything else is very simple:
sz = myIdentifiedShape.Size
sz.Width = myIdentifiedCell.Value
myIdentifiedShape.Size = sz
Since you mention “spreadsheet”, please re-tag your question with calc rather than common to helps others with the same problem.
Thanks.
1 Like
A value can change due to an action of editing, in consequence of a recalcuation, or by an effect of running user code. LibreOffice has no means to merge this in one event. There are the sheet events
- onContentChanged raised by completed user interaction of editing
- onFormulasCalculated raised after the sheet was recalculated
we can use to trigger a related macro. User code changing a cell’s value directly doesn’t raise one of these events. If the updating macro shall be triggered, it must either be directly called by that event, or the assigned handler for onContentChanged must be called with the respective cell or range(s) as the passed argument.
BTW: I edited the subject and the tags. Hope this is accepted.
As Lupp says, it’s identifying the shape that is a little bit difficult. The rest is easy. Here is a bare-bones approach:
Sub ChangeHandler()
ChangeShapeWidth("Value", "A1", "Rectangle", 0)
End Sub
Sub ChangeShapeWidth(ValueSheetName As String, ValueCellName As String, ShapeSheetName As String, ShapeNumber As Integer)
Dim Width As Variant
Dim ValueSheet As Object
Dim ShapeSheet As Object
Dim Shape As Object
Dim Size As New com.sun.star.awt.Size
ValueSheet = ThisComponent.getSheets().getByName(ValueSheetName)
ShapeSheet = ThisComponent.getSheets().getByName(ShapeSheetName)
Width = ValueSheet.getCellRangeByName(ValueCellName).getCellByPosition(0,0).Value
Shape = ShapeSheet.getDrawPage.getByIndex(ShapeNumber)
Size = Shape.Size
Size.Width = Width
Shape.Size = Size
End Sub
It is worth noting that Lupp and I are “pulling out” Size because size is its own struct that contains Width, so you can’t just set Shape.Size.Width directly.
ChangingRectangleWidth.ods (15.3 KB)
Not sure if there is still interest in the quiestion.
Anyway I made a simple example demonstrating how automatic resizing of a shape depending on a formula result might be done as a so-called side-effect of a user-defined function.
Seer attachment:
disask74247VelutLuna.ods (12.7 KB)
.