LibreOffice Calc: Stepping through a range of values between limits in given cells

Question

In LibreOffice Calc, I am doing calculations for erosion and flood control. I have a set of data like this:

This is in the 2nd sheet, which is for my data and other "ugly" things that an end user doesn't need to see. On the first sheet, I use the Data->Validity->Cell Range to present a list of the surface types so we can pick the kind of surface we're working with. From there, I use VLOOKUP to pick the low and high coefficient to go into the next two cells. So I have this:

I picked Gravel, so it gives me the high and low values I can use for coefficients by reading that data from my table.

Next, to make things easy, I want to have an input field called "Surface Coefficient." I want to use something like a spinner or slider so we can quickly pick a value between the high and low numbers in the 4th and 5th columns in that row. For instance, here, the slider/scroll/spinner would have a lower limit of 0.5 and a higher limit of 0.7. If possible, it would increment in intervals of .01. If I changed the surface type to "Gravel," then the slider/scroll/spinner/whatever would pick between a low of 0.50 and a high of 0.70.

I have found how to make a scrollbar as a form control in a spreadsheet and link the value to a cell. I know I can specify the low and high values for that scrollbar, but only by specifying actual numbers. It won't let me specify cells as the low and high limits.

I'm okay with using a different kind of control. I basically want a control that will let me step between the low range and high range (specified in cells, not as direct numbers) in an increment I set.

Is there a way to specify cells to use for low and high limits in a scrollbar or another control that will let me do what I want?

Answer 1

Let's shortly reformulate the task based on your description of the proposed user interface.

In a cell, you need to enter numerical values with a step of 0.01. The minimum and maximum limits for this value are indicated in two cells (in the same row) to the right of the input cell. For ease of input, it is desirable to use a suitable control for selecting values with the mouse. However, manual entry of a value from the keyboard is also permitted.

To begin with, this task cannot be solved without macro programming.

First of all, let's learn how to distinguish the cell for which the macro should work from all other cells on the sheet.

The easiest way is to create a custom cell style and apply it to the desired cells on the sheet. The "cell" object has a .CellStyle property by which we can easily recognize the desired cell. Let's name our style "setValue" and at the beginning of the macro enter the description for the

   Const STYLENAME_OF_FIELD = "setValue"

Now a simple condition

   If oCell.CellStyle = STYLENAME_OF_FIELD Then

will prevent from executing code for other cells.

Okay, now let's answer the main question that you put into the title: how to limit the input to the specified values. Since the limits will change dynamically, do not use the built-in Data-Validity-Criteria mechanism. It's easier to write a subroutine that might look like this:

Sub onValueChanged(oEvent As Variant)
Dim nColumn As Long, nRow As Long
Dim minValue As Double, maxValue As Double, newValue As Double
Dim oSheet As Variant
Rem Is this Event frome single cell? 
    If oEvent.SupportsService("com.sun.star.sheet.SheetCell") Then 
Rem Does this cell have our predefined style?
        If oEvent.CellStyle = STYLENAME_OF_FIELD Then
Rem To find out the contents of neighboring cells, 
Rem you need to understand where this cell is located in our spreadsheet
            nRow = oEvent.getCellAddress().Row
            nColumn = oEvent.getCellAddress().Column
            oSheet = oEvent.getSpreadsheet()
Rem Now you get the limits easily:
            minValue = oSheet.getCellByPosition(nColumn+1, nRow).getValue()
            maxValue = oSheet.getCellByPosition(nColumn+2, nRow).getValue()
Rem The current value of the cell is not available for any event via .getValue()
            newValue = Val(Replace(oEvent.getFormula(),",","."))
Rem If the current value is out of range, return it back
            newValue = IIf (newValue > maxValue, maxValue, newValue)
            newValue = IIf (newValue < minValue, minValue, newValue)
Rem And write this value back into the cell
            If newValue <> oEvent.getValue() Then oEvent.setValue(newValue)
        EndIf 
    EndIf 
End Sub

(This will work, but I must warn you - this code is far from complete! The event can be triggered by a change not in one cell, but in the whole range of cells. Or even several different ranges. In addition, the current cell may appear at the end of the row and there will be no cells to the right of it at all. This code does not handle these situations)

Now about the control that can be trusted to enter data using the mouse.

You can try using the Scrollbar. It would be ideal for entering integer values from a not very wide range. But this is not very suitable for your task, for working with fractional values. (You asked how the scrollbar value is passed to the cell. This is done not through event handling, but through the linked cell, set on the Data tab)

A set of several buttons combined into a group may be convenient for your task. Set the Tag property of each of the buttons to a certain value, and then all of them can be controlled by one macro:

Sub onBtnClick(Optional oEvent As Variant)
Rem Each button in group work with this Sub
Dim oCell As Variant
Dim oModel As Variant
Dim DeltaFromTag As Double 
    oModel = oEvent.Source.getModel()
    DeltaFromTag = Val(oModel.Tag)
    oCell = getActiveCell()
    oCell.setValue(oCell.getValue() + DeltaFromTag)
    onValueChanged(oCell)
End Sub

Pay attention to the last line. Changing a cell value using a macro does not raise the Content Changed event. Therefore, we call the required code ourselves.

Assign this subroutine to the event handler Action Execute for each of the buttons

Several other macros control the display of a group of buttons - the code can be seen in this file. Calling the main one - onChngSelected() - assigned to the sheet event Selection changed.

Assigning sheet event handlers is easy: choose Sheet-Sheet Events from the menu, or simply right-click on the sheet's tab