Macro to Select Cell to the Left and Second Macro do Check B

Creating a macro - Writing a Script - Using the API

Macro to Select Cell to the Left and Second Macro do Check B

Postby israeltandrade » Thu Aug 01, 2019 6:32 pm

I am trying to record a macro that selects the cell to the left of the button, shifts up to two cells further to the left, applies the overwritten landing to the letters, and changes the font color. Everything works fine, the problem is that if I apply the same macro to another button it changes the cells of the previous button. That is, you would have to record a different macro for each line. Is there a way around this to make Libreoffice understand that it just have to follow the procedure with the cell immediately to the left of the cell where the button is?

Another thing, how to apply a second macro when we "deselect" the check box?

Thanks for your attention!
Libreoffice Calc 6.2.5.2 (x86) Windows 32 Bits
israeltandrade
 
Posts: 1
Joined: Thu Aug 01, 2019 6:12 pm

Re: Macro to Select Cell to the Left and Second Macro do Che

Postby robleyd » Fri Aug 02, 2019 1:56 am

Same question posted at AskLibreOffice
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2985
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Macro to Select Cell to the Left and Second Macro do Che

Postby Lupp » Fri Aug 02, 2019 3:09 pm

READ your recorded macro and you'll find it didn't base on the "cell left of the button" but on the specific cell you selected during recording.
In other words: You need to change the assignment of that cellname (address) to the repective argsX(...).Value property to an assignment meeting your needs.

This said, it seems clear to me that your actual problem is: "How do I get the address (and sheet?) of the cell left of the button I clicked?"
First part of the problem now is how to DEFINE the "cell left of the button". Button areas are not bound to be congruent with any cell area. Cells may be hidden...
Second part is that a button not anchored to a cell but to the sheet has no simple answer to the question.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2531
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Macro to Select Cell to the Left and Second Macro do Che

Postby Lupp » Fri Aug 02, 2019 6:10 pm

As often I was interested in the question for reasons not related to actual needs.
The result of my investigation is contained in the attachment. It was created under LibreOffice V6.3.0.2RC and worked also for me in AOO V4.1.5 and in LibO V5.4.4.

As always I am interested in criticism and in suggestions how to do it better or simpler.

In case of controls anchored to the sheet (see my above post) we would need to add up the widths of columns and the heights of rows till we find the cell where the top-left corner of the ControlShape is located. It's pure fun. (We cannot work with the .PosSize of the control because it gives different values for reasons unknown to me.)
Attachments
ask203096aoo98840andIdentifyShapeBasedOnControl_1.ods
(11.07 KiB) Downloaded 19 times
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2531
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Macro to Select Cell to the Left and Second Macro do Che

Postby JeJe » Fri Aug 02, 2019 7:12 pm

Lupp: posSize usually gives pixels. If you position your button in the first column it will give an error, or if aligned on a column boundary the cell to the left of the cell to the left.
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 584
Joined: Wed Mar 09, 2016 2:40 pm

Re: Macro to Select Cell to the Left and Second Macro do Che

Postby Lupp » Fri Aug 02, 2019 7:41 pm

Thanks for your reply!
JeJe wrote:posSize usually gives pixels.
I should have known that. Thanks!

JeJe wrote:If you position your button in the first column it will give an error, ...

This was clear to me. I didn't try to get the code "foolproof". Having activated the On-Error-clause the Sub will exit in any error case.

Your statement on boundaries seems to address the case where not an .Anchor can be used. I had implemented that case, too but threw that away due to my lacking knowledge about the pixel size in mm (regarding your explanation).

Do you also know a simpler way to get the shape or would you suggest to do it based on the pixel measures taken from the properties of the control itself?
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2531
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Macro to Select Cell to the Left and Second Macro do Che

Postby JeJe » Sat Aug 03, 2019 12:40 am

Don't know a simpler way to get the shape - but no expert there. Would looping through the columns widths and comparing the sum to the position of the button perhaps be better? I've forgotten how to convert pixels to 100ths of a mm though.

colwidth= thiscomponent.sheets(0).getColumns.getByIndex(whatevercolumn).getPropertyValue("Width",0)
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 584
Joined: Wed Mar 09, 2016 2:40 pm

Re: Macro to Select Cell to the Left and Second Macro do Che

Postby Lupp » Sat Aug 03, 2019 11:28 am

(Sorry. I slept a bit and had a breakfast then.)

For LibreOffice:
https://api.libreoffice.org/docs/idl/re ... eInfo.html and
Code: Select all   Expand viewCollapse view
devInf = createUnoStruct("com.sun.star.awt.DeviceInfo")
e.g.

I would suppose that's old and unchanged stuff. It should be the same in AOO from the beginning.

BUT: I did not yet find the (a) way to get the DeviceInfo for the currently used screen (device).

There's a Lupp's Theorem:
"Its mostly easier to guess the right answer tan to find the right question trying to ask the API reference."
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2531
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Macro to Select Cell to the Left and Second Macro do Che

Postby JeJe » Sat Aug 03, 2019 12:27 pm

Ah yes... that was it...

thisComponent.CurrentController.ComponentWindow.getInfo.PixelPerMeterX
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 584
Joined: Wed Mar 09, 2016 2:40 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: JeJe and 3 guests