[Solved] Increment by real value using SpinButton

[skrat]

Hi,

Is it possible to use SpinButton or NumericalField with SpinButton enabled to increment certain value by a real number?

So each mouse click should either add or take 0.1 from current cell value.

How would I do that?


Thank you for your help!

[Zizi64]

The form control elements can handle the integer numbers only. But you can use ten times larger numbers, and you can divide the value of the linked cell by 10. See the sample file.

SpinButton decimals.ods

(9.32 KiB) Downloaded 276 times

[skrat]

I thought about that but it's not something I could accept. The reason is document readability.

By readability I mean not too much unnecessary numbers. I am only interested in real number (1.6) and not integer value (16). Obvious solution to that is to hide the column/row with integer values but this leads to a massive disadvantage I can't afford: By hiding the integer values I can't change the real value directly - meaning I can't type 10000.5 into cell (but at teh same time would take way to much mouse clicks to get to that number starting from 1.6).

I thought Macro might be the solution but I can't figure out how to identify which button of the SpinButton was pressed - increasing or decreasing one.

[Zizi64]

By readability I mean not too much unnecessary numbers. I am only interested in real number (1.6) and not integer value (16).

Put the linked cell onto an another (hidden) sheet, and the result of the dividing onto the main sheet.

[Zizi64]

I thought Macro might be the solution but I can't figure out how to identify which button of the SpinButton was pressed - increasing or decreasing one.

Use the oEvent object to recognise the object that called the macro.
Examples:
viewtopic.php?f=20&t=90272&hilit=+oEvent
viewtopic.php?f=20&t=71770
viewtopic.php?f=45&t=62024

[skrat]

By readability I mean not too much unnecessary numbers. I am only interested in real number (1.6) and not integer value (16).

Put the linked cell onto an another (hidden) sheet, and the result of the dividing onto the main sheet.

I'm not sure I understand you. How is this different from your intial suggestion - to divide the value of the linked cell by 10? Isn't this effectively the same thing, only the sheet is different?

[Zizi64]

I'm not sure I understand you. How is this different from your intial suggestion - to divide the value of the linked cell by 10? Isn't this effectively the same thing, only the sheet is different?

If you not needed change the value MANUALLY, then maybe this solution will be appropriate for you: The helper cell is not shown on the main Sheet.

SpinButton decimals2.ods

(10.02 KiB) Downloaded 150 times

But: if you need to change the non-Integer type number manually too, then you must write simple macro to achieve this task.
For example the macro will divide the value of the spinbutton, and will put the result into the target cell.

And when you want ro modify more than one cell by one Spinbutton (or other Form Control Element) only, then you must use the API functions, and the "oEvent" thing described above.
For example: The macro will modify the value of the actually SELECTED cell. The macro will get the original value of the highlighted cell, and will increase/decrease it by 0.1 . The the macro will put the result value into the cell. In this case you will not use any Cell formula, and you will able modify the value of the cell manually and independently from the Form Control element.


You must study and use the API functions for this task.

[morchat]

My proposition:
Use two buttons, group them and assign them macros
As in the attached example.

2buttons.ods

(10.29 KiB) Downloaded 166 times

The SpinButton control uses only integers and only in the defined range from MIN to MAX. You can enter a greater (smaller) number than MAX (MIN) in the cell. However, using the button will adjust the value to the next possible one. For example, MAX = 100, 120 entered, the use of the "decrease" button will adjust the value to 99 if the value increase was 1

[Zizi64]

...or if you want to control more than one cells by one macro, and one pairs of buttons:

Code: Select all

REM  *****  BASIC  *****

Option Explicit

Sub IncDec_the_Selected_Cell_Value(oEvent)

 Dim oCaller as object
 Dim oDoc as object
 Dim oCellRange as object 
 Dim oCell as object 
 
 Dim sName as string
 
 Dim lColNr as long
 Dim lRowNr as long
 

	oCaller=oEvent.Source.Model
	sName=oCaller.Name
REM	Print "You pushed the button named: ", sName


	oDoc = ThisComponent
	oCellRange = oDoc.getCurrentSelection
	oCell = oCellRange.GetCellByPosition(0,0) 'When you have selected more than one cell, the left-upper cell in the selected range will be modified.
	lColNr = oCell.CellAddress.Column
	lRowNr = oCell.CellAddress.Row


	If lColNr <> 0 or lRowNr > 7 then
		MsgBox("The selected cell located outside of the acceptable cell range") 
		Exit sub
	else
		Select Case sName
			Case "Button1"
				oCell.Value = oCell.Value + 0.1
			Case "Button2"
				oCell.Value = oCell.Value - 0.1
			Case else
				MsgBox("You cilicked on a non acceptable form control element") 
				Exit sub
		End Select
	end if
End Sub

IncDec.ods

(11.49 KiB) Downloaded 172 times