Calc: Access to Cell under Checkbox/Button

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Toxic
Posts: 8
Joined: Thu Sep 07, 2017 6:46 pm

Calc: Access to Cell under Checkbox/Button

Post by Toxic »

Hi @ all,

I´m new here, so don´t be angry, if I do something wrong (board-rules), but I´m at the edge of madeness... ;)

Okay to the point:
I have a sheet with a table of a few (hundred) articles.
Now I want to have some Dates in 4 columns:
  • Online Date
  • Purchase Date
  • Payment Date
  • Delivery Date
.
I don´t want to enter some dates directly for each article, when its going online, wenn its purchased...
so I thought I place a checkbox in each cell

If user checks the option the underlying (date-)cell gets the current date and time.
(On uncheck an option dialog asks, whether to remove the date or not)

All its fine with hardcoded cell addresses, but I don´t want to write code for each checkbox seperately.
So I want to get the (reference to) checkbox/buttons underlying cell, to set the date value in that cell.

...so I could use the same code for each ckeckbox.

I´m not familiar with Macro programming, so I ask here whether someone has a pointer for me, how to solve it.
A few programming skills are available on my side :)

ThanX for any reply!
(Also suggestions are welcome, how to solve it other ways)

ToX
LibreOffice 5.3.4.2 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc: Access to Cell under Checkbox/Button

Post by Villeroy »

Your office suite comes with a database component. In the context of a database all the form controls are fully functional without any macro code.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Toxic
Posts: 8
Joined: Thu Sep 07, 2017 6:46 pm

Re: Calc: Access to Cell under Checkbox/Button

Post by Toxic »

Hi, I dont have a underlying database. I have only a (the) spreadsheet as datasource (where I want to copy in, the checkboxes).

Is there no simple way to get the cell object from the given checkBox position (x:y), which I've from the incoming event?

Code: Select all

sub setCurrentDateTime(oEvent As Object)
	set sheet = ThisComponent.Sheets(0)

	cell = sheet.GetCellByPosition(11, 8) ' I need THAT dynamically	

	set source = oEvent.Source ' the checkbox
	if oEvent.Selected then
		cell.String = now()
	else
		deleteDate = MSGBOX("Datum entfernen?", 36)
		if deleteDate = 6 then
			cell.String = "Not set!"
		else
			source.State = 1 ' reset checkbox to selected
		end if	
	end if
end sub
LibreOffice 5.3.4.2 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc: Access to Cell under Checkbox/Button

Post by Villeroy »

No, there is no simple way. You should use th database instead of the calculator.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Toxic
Posts: 8
Joined: Thu Sep 07, 2017 6:46 pm

Re: Calc: Access to Cell under Checkbox/Button

Post by Toxic »

Thats not an option :(
I only have the sheet and I want to insert the dates as it is necessary.
But simply with a click ...not to write date/time in each field when it comes up.

What´s the complicated way to do it with macro??? :)
LibreOffice 5.3.4.2 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc: Access to Cell under Checkbox/Button

Post by Villeroy »

How to enter a dates and times into a spreadsheet (Calc, Excel, Gnumeric and others):

1/ enters this month's first day
1/2 enters this year's 1st of February or 2nd of January in US context
1/2/3 enters day/month/year or month/day/year respectively
Times use a colon.

LibreOffice has shortcuts to enter the current date and the current time. Simple macros can do the same for OpenOffice.

Nevertheless, what you try to do is a perfect job for a database. Spreadsheets are not designed to handle this well.

Check boxes, date controls, list boxes, combos, all form controls are very far beyond beginners level. You have to be not only an experienced programmer but an experienced UNO programmer to handle the all structures and objects from many different modules.
UNO: Unified Network Objects, the API of this application.

Now it looks as if you are willing to do the 8th step before the first one like this guy here: viewtopic.php?f=9&t=72289


P.S. Alternative to push buttons with simple Basic code and the HYPERLINK sheet function viewtopic.php?f=45&t=46391&p=214392
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Toxic
Posts: 8
Joined: Thu Sep 07, 2017 6:46 pm

Re: Calc: Access to Cell under Checkbox/Button

Post by Toxic »

Hi again, after I thougt about the problem (over night)... ;)

I dont understand the programming logic (solution approach) of OOo's.
If I have an anchor from Object (CheckboxControl) to the cell... Why to hell I cannot gert the cell directly?

I`m programmer for java and have a few years experience with objectoriented programming.
So it could be, my thinking structure is a little bit different to OOo#s way of implementation of macro programming in spreadsheet(s)

"Impossible" doesn´t exists in my vokabulay ;) so I think I find a way to solve my prob.
My honor would never forgive me if I dodn't try it.

ThanX anyway I#ll post, if I find the solution, whenever I find one.

GreetZ
LibreOffice 5.3.4.2 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc: Access to Cell under Checkbox/Button

Post by Villeroy »

I could surely write something which gets what you want but it would take me another Sunday afternoon although I am fairly familiar with this API. From similar requests on this forum alone, the active volunteers could spend all their life time to write one macro after the other for people who need a replacement for silly VBA we would never write on our own. One of the most prominent mine field since 16 years (OpenOffice.org 1.0) is the spreadsheet-as-database-with-vba kind of problem. Another one is the generate-documents-programmatically-from-no-template kind of problem.

Why are you trying to do silly things with spreadsheets if you are a developer? A developer has a cosmos of adequate tools at hand. Since 1979 (Visicalc) spreadsheets are the same flexible numeric scratchpads where you can informally dump numeric data from keyboard, clipboard, from databases and various types of import formats. Then you can freely drag&drop, insert, filter, delete and analyse with the number of rows and columns being the only restriction. Using this scratchpad in creative ways takes less time than learning a more abstract, less visual programming language. There is always some mouse/keyboard acrobatics involved when using this software from the early 90ies.

A database form is quite the opposite concept.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Toxic
Posts: 8
Joined: Thu Sep 07, 2017 6:46 pm

Re: Calc: Access to Cell under Checkbox/Button

Post by Toxic »

You´re right, I*`m going conform with you, that VBA is a silly thing.
It would take 1 or 2 hours to write a prog in java to read the spreadsheet in, manipulate them (possibly from a seperate gui or via spring in browser). Also I can use some other tools...

But I don´t want that way ;) its a challenge for me if it pops up such a "simple problem" in every days life, so I´m caused to solve this "prob". It´s possible, I know. The effort for me is: Just do it (if you have time) - possibly some other people will run in the same dead end and it would be nice if they can use your (possibly ...no _surely_ silly) solution.

Such a stupid simple plan, to fill a cell with data on click could have many other peoples around, who have the same idea and NO (quick) solution ;)
LibreOffice 5.3.4.2 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc: Access to Cell under Checkbox/Button

Post by Villeroy »

With Calc and Base it is possible to paste data from a sheet into a database table (dBase, Postgre, MySQL, any ODBC or JDBC). Then you can manipulate your data easily by means of form controls.
Even though I used to be an "Excel/VBA wizard" back in the year 2k, the API of this office suite took me several weeks until I was able to share useful code with other users.
After spending some 15 minutes of time, I have not the faintest idea how to get the anchoring sheet cell of a shape (form control, picture, whatever). On the dev mailing list you may find someone who is able to answer this question (maybe not).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Calc: Access to Cell under Checkbox/Button

Post by Zizi64 »

Maybe this example will help you:

https://stackoverflow.com/questions/277 ... l-in-macro

Code: Select all

Sub ButtonHandler(oEvent)

  Dim sControlName$
  Dim oSheet
  Dim nCount As Long
  Dim i As Long
  Dim oPage
  Dim oShape
  Dim oAnchor

  sControlName = oEvent.source.model.Name
  oSheet = thiscomponent.currentcontroller.activesheet
  nCount = oSheet.drawpage.count
  oPage = oSheet.drawpage

  For i = 0 To nCount - 1
    oShape = oPage.getbyindex(i)
    'oControlShape = oPage.getbyindex(i).control
    If (oShape.supportsService("com.sun.star.drawing.ControlShape")) Then
      If oShape.control.Name = sControlName Then
        oAnchor = oShape.anchor
        If (oAnchor.supportsService("com.sun.star.sheet.SheetCell")) Then
          Print "Button is anchored in cell: " + oAnchor.AbsoluteName
          Exit For
        End If
      End If
    End If
  Next i
End Sub
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Calc: Access to Cell under Checkbox/Button

Post by Zizi64 »

Here is a modified sample:
Event.ods
(12.52 KiB) Downloaded 256 times
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: Calc: Access to Cell under Checkbox/Button

Post by Sébastien C »

Hello-hello
A other think, more (more, more, more) simple in my view...

Code: Select all

Sub myNow
 thisComponent.currentController.selection.value = now()
End Sub
with a button in the own sheet or in a toolbar.
Attachments
myNow.ods
(10.13 KiB) Downloaded 237 times
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
Toxic
Posts: 8
Joined: Thu Sep 07, 2017 6:46 pm

Re: Calc: Access to Cell under Checkbox/Button

Post by Toxic »

Hi Sébastien,
this was my first idea, but so I´ve to select cell first.
Thats possible but a little bit ugly.
I´ve to check whether to insert the (right) date in the right cell ..not to overwrite the wrong date ...not to write date everywhere, the selection is currently placed ;) and so on...

I prefer the "nice" way to force the selection on right positioning mouse pointer BEFORE on(over) checkbox beside/in the cell which I want to change so its more intuitive to set/remove the right date to the right cell

But thanX for your idea, its not the wrongest way to think as simple as possible, mostly my greatest prob ;)
LibreOffice 5.3.4.2 on Windows 10
Toxic
Posts: 8
Joined: Thu Sep 07, 2017 6:46 pm

Re: Calc: Access to Cell under Checkbox/Button

Post by Toxic »

@Zizi64

ThanX for your approach!!! I think I can use it temporarily. But my target is to have a completely independent (dynamically) checkbox (no naming rules!), which inserts (any) data in the underlying cell! The current date is what I need actually, but furthermore it could be pot of tea or a sheep or 10000 gallons of sea-water... or somthing like that, ...when I say to that box, "(Un)Check-It": I want to have it or not... ;)

You´re the dumb box, do it for me, from where and to where is not your problem, simply do it...

The cracking point is the underlying cell, from where I can relatively to that address, something somewhere in document

GreetZ ToX
LibreOffice 5.3.4.2 on Windows 10
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Calc: Access to Cell under Checkbox/Button

Post by Zizi64 »

But my target is to have a completely independent (dynamically) checkbox (no naming rules!),
You need use some unique property to identify the specific form control element (Name, Label, or other).

Or you can write a short subroutine for EACH form control element, that contains an unique identifier.

I do not know any other way to identify the Source and Model of the Event, and determine the Anchor (cell) of the object...


Please upload your ODF type sample file with the form control elements and with some dummy data...
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: Calc: Access to Cell under Checkbox/Button

Post by Sébastien C »

Zizi64 wrote:I do not know any other way to identify the Source and Model of the Event, and determine the Anchor (cell) of the object...
According to two code Snippets...

This recent one (in english).

And this old one (in french).

And maybe a little demonstration file ; just to laugh.

Have a good discovery...
8-)
Attachments
someDates.ods
(19.53 KiB) Downloaded 213 times
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc: Access to Cell under Checkbox/Button

Post by Villeroy »

Another simple trick: download/file.php?id=27213
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: Calc: Access to Cell under Checkbox/Button

Post by Sébastien C »

Great idea Villeroy; very great idea...
:mrgreen:
Attachments
HyperlinkDates.ods
(21.11 KiB) Downloaded 198 times
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc: Access to Cell under Checkbox/Button

Post by Villeroy »

Thank you. You can do even more with this trick:

=HYPERLINK("vnd.sun.star.script:Standard.Module1.CellPropertyValue
?language=Basic
&location=document
&s="&SHEET()
&"&r="&ROW()
&"&c="&COLUMN() - 1
&"&p=CellStyle&v=MyCurrency" ;
"Apply Style"
)

The macro evaluates the property name "p", the property value "v" and then: oCell.setPropertyValue(p, v)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: Calc: Access to Cell under Checkbox/Button

Post by Sébastien C »

ahem...
If I may, I use the variable "command" in =HYPERLINK(...) of HyperlinkDates.ods
It is not for nothing...
;)
 Edit: And it is so funny to use UTF8 or Wingdings font... 
Attachments
HyperlinkDatesIcons.ods
(19.13 KiB) Downloaded 197 times
Last edited by Sébastien C on Sat Sep 09, 2017 7:27 pm, edited 1 time in total.
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc: Access to Cell under Checkbox/Button

Post by Villeroy »

But then you have to do a "select case" for each property value.

Anyway, if you want to set the formula, value or string of a cell, you need separate cases anyway because Value, String and Formula are pseudo-properties.

Let's try to set any property value, cell value, cell formula or cell string:

Code: Select all

REM value, formula and string are exclusive to each other
REM and should not be used in the same URL
sval = getArgumentFromURL("value")
	if len(sval)>0 then oCallingCellButton.setValue(cDbl(sVal))

        sval = getArgumentFromURL("formula")
	if len(sval)>0 then oCallingCellButton.setFormula(sVal)

        sval = getArgumentFromURL("string")
	if len(sval)>0 then oCallingCellButton.setString(sVal)

REM pair of prop name and prop value
        sval = getArgumentFromURL("pname")
        sval2 = ""
	if len(sval)>0 then sval2 = getArgumentFromURL("pvalue")
        if len(sval2)>0 then oCallingCellButton.setPropertyValue(sVal, sVal2)
I stop at this point. Some property values are numeric, some are not. Decimal strings may have comma or point. But this can be handled.

the above snippet would evaluate the following arguments to dump the spreadsheet's NOW() value and apply a user defined cell style.
[...]& "&value="&NOW() & "&pname=CellStyle&pvalue=TimeStamp"

The same code can enter any formula or calculated value or string into any other cell and apply arbitrary property values. A cell object in LO has 101 properties.
The property names, values and positions can be entered (or calculated) in sheet cells and one click on a hyperlink button manipulates a target cell one way or the other.
Let's create a new macro language like the one in Lotus 1-2-3 or Excel before 1993 where you entered commands into special macro sheets.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: Calc: Access to Cell under Checkbox/Button

Post by Sébastien C »

Villeroy wrote:Let's create a new macro language like the one in Lotus 1-2-3 or Excel before 1993 where you entered commands into special macro sheets.
I understand you very well Villeroy. But perhaps it exceeds the desire of our friend Toxic, and therefore, of this own thread ...
:ugeek:
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
Toxic
Posts: 8
Joined: Thu Sep 07, 2017 6:46 pm

Re: Calc: Access to Cell under Checkbox/Button

Post by Toxic »

Hi again!

I think I#m out of your round now...
I realized (for now) the simplest way (Sébastien#s tip) and set the current date in selected cell(s) from whithin ONE BIG BUTTON 8-)
-> Set Date only, if the cell(s) is/are formatted at least with a DATE NumberFormat -> DateTime seem to rigid for me ;)
...other cells are not touched (simple warning), so I have not to do some validation overhead, whether the right cells are selected or not...
The action button is located in a (header)row which is always visible.

For now the best alternative! If I find the time to do some more (I want to do it) I will go deeper in macro stuff of OOo#s

For now many ThanX to you guys, for your contributions and approaches! You#re familiar with that, what you write, I see ;)

cu (for now) ToX
LibreOffice 5.3.4.2 on Windows 10
Post Reply