Activate cell close to a button

Creating a macro - Writing a Script - Using the API

Activate cell close to a button

Postby Skidd » Wed Jan 10, 2018 1:09 pm

Is there a way to instruct the cursor to activate an adjacent cell to a button?

It is for a scoreboard spreadsheet, for multiple players, in order that I may click a button and the cell, for example, immediately below 'lights up', whence I can instruct, for example, an addition.

I do not seek to activate a named cell, I can do that already. I just want to be able to cut and paste rather than do dozens of repetitions, typing in cell references.

Thankyou
OpenOffice 3.1 on Windows 10
Skidd
 
Posts: 7
Joined: Wed Jan 10, 2018 1:00 pm

Re: Activate cell close to a button

Postby UnklDonald418 » Wed Jan 10, 2018 6:05 pm

If you are using Calc then you should be able to use a recorded macro to do that. If you are not familiar with recording macros Open office Help has instructions.

If you are using Base, the macro recorder doesn't work, but maybe this will help
Base Copy Field or Clone Record – Example
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.5 & LibreOffice 6.0.4.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 933
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Activate cell close to a button

Postby Villeroy » Wed Jan 10, 2018 8:06 pm

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25708
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Activate cell close to a button

Postby Skidd » Wed Jan 10, 2018 8:29 pm

Thanks for the replies, but I was hoping there would be a string of code that I could cut and paste. It's not that I am lazy, but I am only au fait with macros without being an expert, something along the lines of "

Activate the cell in which this button sits
Go to the cell below
Add one to the number in the cell below

The latter two lines are not a problem. It is the first where I have been stuck for several weeks.

So to put it slightly differently. What is the code for making the cell in which a button sits the active cell? (e.g. If I place a button in A1, fix it in that cell, and link it to my macro which is an instruction to move one cell down and add one, all it does is go to the last active cell prior to me pushing the button). I would then want to copy it many times to many cells with the desired outcome relative to the new buttons and their cells. Is there a code which moves and clicks the cursor a sufficient number of milemeters, or a way to turn a cell (field) into the actual button?

I've been trying to work this out for literally years!
OpenOffice 3.1 on Windows 10
Skidd
 
Posts: 7
Joined: Wed Jan 10, 2018 1:00 pm

Re: Activate cell close to a button

Postby UnklDonald418 » Wed Jan 10, 2018 11:05 pm

It still isn't entirely clear what you want to do so if the following doesn't help then perhaps you should upload an example.
From your reference to A1 it appears that you are using Calc not Base. If I understand you correctly, the button is the problem. When you press the button it receives focus and any previous cell location is lost.
To avoid that problem you can assign a shortcut key to the macro by going to Tools->Customize->Keyboard to open the Customize Keyboard dialog. In the lower part locate and select your saved macro and in the upper part select an unused shortcut key (for instance <Alt>c) and click on Modify and OK.
Now each time you press <Alt>c your macro will execute without changing the focus.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.5 & LibreOffice 6.0.4.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 933
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Activate cell close to a button

Postby Villeroy » Thu Jan 11, 2018 12:44 am

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25708
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Activate cell close to a button

Postby Skidd » Thu Jan 11, 2018 10:19 pm

Primarily I want to activate the cell in which a button sits when I press that button.

______________________________

Then if I cut and paste that cell and button, and press the new (copied) button) It activates the new (copied) cell

___________

Everything else I can do already.

Thanks for the reference post above, but these threads all seem to go the same way. No solution. :(
OpenOffice 3.1 on Windows 10
Skidd
 
Posts: 7
Joined: Wed Jan 10, 2018 1:00 pm

Re: Activate cell close to a button

Postby Lupp » Thu Jan 11, 2018 11:28 pm

May it be that you didn't study the code Tibor (Zizi64) provided in the other thread, in specific the version included in the demo?
If there are also controls accessed going through the DrawPage that have different tasks, you only need in addition to exit the Sub based on the controls' names and/or types.
On Windows 10: LibreOffice 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Let's create a powerful UFO: United Free Office!
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 1995
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Activate cell close to a button

Postby Skidd » Fri Jan 12, 2018 1:49 am

Could you link to " the code Tibor (Zizi64) provided in the other thread" please.
OpenOffice 3.1 on Windows 10
Skidd
 
Posts: 7
Joined: Wed Jan 10, 2018 1:00 pm

Re: Activate cell close to a button

Postby Lupp » Fri Jan 12, 2018 5:03 pm

("The other thread" was used for the one Villeroy linked to some posts above.)
https://forum.openoffice.org/en/forum/v ... 72#p426552
and, even better adapted, I think, the code contained in the example attached to
https://forum.openoffice.org/en/forum/v ... 72#p426555
On Windows 10: LibreOffice 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Let's create a powerful UFO: United Free Office!
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 1995
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Activate cell close to a button

Postby Skidd » Fri Jan 12, 2018 8:08 pm

The thread linked poses essentially the same question, and the solutions offered do not answer it. For instance,

Sub myNow
thisComponent.currentController.selection.value = now()
End Sub

Merely puts the date (or addone or whatever instruction)) in the last active cell.

Not necesarily the cell beneath the button. This is the conclusion at the end of that thread, i.e. it is a work around, but not what was sought.
OpenOffice 3.1 on Windows 10
Skidd
 
Posts: 7
Joined: Wed Jan 10, 2018 1:00 pm

Re: Activate cell close to a button

Postby Villeroy » Fri Jan 12, 2018 9:12 pm

What are you trying to write if you can't even read?
https://forum.openoffice.org/en/forum/v ... 72#p426552
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25708
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Activate cell close to a button

Postby Skidd » Fri Jan 12, 2018 9:47 pm

I installed the above linked macro (labled "Button Handler") into a workbook
I placed a button in A1
I associated the button with the macro
I locked the button into the cell (I also tried it realtive to the sheet
I turned of form controls
I clicked a random cell (c7)
I then clicked the button and got a dialogue which told me that the button was linked to cell a1
The random cell last used (c7 in this case) was still the active cell.

Please don't make assumptions about my reading ability. :)

But to answer your question without the caveat " I want to activate the cell in which a button sits when I press that button."
OpenOffice 3.1 on Windows 10
Skidd
 
Posts: 7
Joined: Wed Jan 10, 2018 1:00 pm

Re: Activate cell close to a button

Postby Lupp » Fri Jan 12, 2018 11:35 pm

You shouldn't expect to find a ready-made "macro" for every nonstandard task you have in mind. And you shouldn't expect to get a volunteer here to write the macro for you. What you can expect, and what you got are hints concerning the means and some steps. Insofar the code contained in the example from the next post by Zizi64 there (I linked it earlier) may even come nearer to your specific needs.
To integrate the components to get the machine you actually want is your turn now.
If there comes up a specific new question, you are welcome when asking again in this forum.

Editing
On more hint:
If you have a cell object, say oAnchor, you get the cell below by
Code: Select all   Expand viewCollapse view
oCellBelow = oAnchor.Spreadsheet.GetCellByPosition(oAnchor.CellAddress.Column, oAnchor.CellAddress.Row + 1)

If you want to select it, the following line of code does the trick:
Code: Select all   Expand viewCollapse view
ThisComponent.CurrentController.Select(oCellBelow)

If you want the cell to have the focus but not to be selected ... Find a thread where this is already answered.
You see, there's no end.
On Windows 10: LibreOffice 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Let's create a powerful UFO: United Free Office!
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 1995
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Activate cell close to a button

Postby Skidd » Sat Jan 13, 2018 12:36 am

I apologise most profusely for the times when I have expected too much from this forum. To make amends I would like to donate some cash support for the people who run Apach OpenOffice, so I will give £100 for each time when I have indicated that "I expect to find a ready-made "macro" for every nonstandard task" and £1000 for each time I indicated that "I expect to get a volunteer here to write the macro". I hope this goes some way to compensate for my newbiness.

I did not expect to be insulted "What are you trying to write if you can't even read?"


But I have learned at least that it's a great place for "hints" and "Find a thread where this is already answered"

I live and learn.
OpenOffice 3.1 on Windows 10
Skidd
 
Posts: 7
Joined: Wed Jan 10, 2018 1:00 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 6 guests