Dynamically position a dialog

mapurves · August 24, 2021, 3:58pm

I want to place dynamically a dialog so the bottom of the dialog is aligned with the top of the active row. This spreadsheet is used by many different users working on a variety of different operating systems, so hard-coding some values is not practical. Different users also prefer to view different toolbars and column headings etc.

There may be an easy way to do this, but I haven’t been able to figure it out. Dialog and cell positions have two different origins, and are given in two different units. I need to relate them to each other.

I have the code to place the dialog and get the position of the dialog when it’s closed, but there are some values I need to get so the macro will work for our various users.

First of all, the position of the top left corner of the active cell is given in pixels from the top left corner of cell A1. The position of the top left corner of the box is given in some kind of units from the top left corner of the spreadsheet. (I call these box units.) I need to relate dynamically the position of cell A1 to the top left corner of the spreadsheet in box units or pixels. And I have to place the dialog in either pixels or box units.

I had brief access to a Mac. The twips per pixels on the Mac were 11, and on my PC they are 15. By moving the dialog so the top of the dialog was at the top of cell A1, I was able to get the position of cell A1 in what I call box units. On the Mac this was ~75. I was also able to get a ratio of pixels to box units by moving the dialog to a cell at the top of the screen and a cell at the bottom and then get the positions of the cells in pixels and the dialog in box units. On the Mac this was ~20.

With these values, I could then place the dialog on the Mac so the top of the dialog was at the top of the active cell. By adjusting the position of A1 (75/1511) and adjusting the ratio of box units to pixels (20/1115) I could position the dialog on the PC so it also lined up properly.

To place the dialog so the bottom is aligned with the top of the active cell, I need the height of the box in box units. In the macro editor, the dialog height is 106. Displaying VRect.Height, I get a value 225. I measure the height of the box by aligning the top and then the bottom of the box on the top of a particular cell. On the Mac, it is around 209 units, and on the PC it is around 256. These measurements are not exact because it is impossible to align the top of the dialog with the top of a cell within one pixel.

I was assuming that both the Mac and my PC had 96 pixels per inch.

So, dynamically I need to get:
the position of cell A1 in box units;
the height of the dialog in box units; and,
maybe the number of pixels per inch of the monitor.

Thank you.

This is the code I’m using:

Y0    = Sheet.getCellByPosition(1,First).Position.Y
Cell  = Doc.getCurrentSelection()
RowY  = Cell.Position.Y
Zoom  = Doc.CurrentController.ZoomValue

X     = 190       ' X position of top left corner of dialog.
Y     = ((RowY - Y0)/Ratio*Zoom/100) - BoxH + 2*Toolbars

with com.sun.star.awt.PosSize
  	nWhat= .X + .Y
end with
Dlg.setPosSize(X, Y, 0, 0, nWhat)
ArgOut = Dlg.Execute()
VRect = Dlg.GetPosSize()

jimk · August 25, 2021, 1:33pm

(Indented for plain code formatting. Apparently Discourse does not have a Basic or VBA code highlighter. Perhaps we could add one to this site? Python syntax highlighting works nicely.)

mikekaganski · August 25, 2021, 1:45pm

Please post a message to site feedback … or better to redmine, since @guilhem seems to not look at the site feedback section lately

jimk · August 25, 2021, 1:50pm

@mapurves: If you haven’t yet, take a look at “Listing 5.84: Insert and position a graphic in a Calc document” in Andrew Pitonyak’s macro document to see if there is any new information there. Also, not sure if this is relevant, but I use the following conversion factor in macros for sizes:

INCHES_TO_MM100 = 2540 # convert inches to hundredths of millimeters

jimk · August 25, 2021, 1:51pm

Okay, I will do that.

sokol92 · August 25, 2021, 1:52pm

See here.

jimk · August 25, 2021, 1:56pm

Well, that looks quite good. However the code in the OP’s question did not format nearly so nicely, turning the entire second half of it to a single solid color. Apparently it was not able to parse that code correctly. Anyway, yes, that looks good enough, so probably we don’t need to ask guilhem about it.

mikekaganski · August 25, 2021, 1:57pm

It looks like it auto-detects the language - which fails if there are not enough detected keywords like sub, etc.

And using the ``` with a language explicitly mentioned doesn’t help, tried with “basic”/“vba”/“vb”/“bas”…

sokol92 · August 25, 2021, 1:59pm

This is my current version too.

jimk · August 25, 2021, 2:00pm

There are no Basic languages listed at How do I select a language in code blocks? - faq - Discourse Meta.

mikekaganski · August 25, 2021, 2:04pm

I believe that the screenshot there was just some specific site configuration used there as an example … but no idea what could be used actually

highlight.js demo lists 189 languages in “all”, including those with Basic in name, and VBScript …

erAck · August 25, 2021, 2:25pm

The difference seems to be code blocks enclosed in ``` on separate lines that format nicely, vs blocks indented by 4 spaces that produces just a preformatted block, which the OP used here.

sokol92 · August 25, 2021, 2:29pm

Option Explicit helps too:

Option Explicit
'  My comment
RowY  = Cell("aaa")
Zoom  = Doc.CurrentController.ZoomValue

jimk · August 25, 2021, 2:30pm

There are also aliases. I tried everything that seemed to be relevant from highlight.js/SUPPORTED_LANGUAGES.md at main · highlightjs/highlight.js · GitHub but didn’t have much success. ```text at least avoids the ugly formatting.

My apologies to @mapurves by the way. You asked a good question and we’ve filled it with off-topic clutter. I suppose that’s going to happen sometimes while the site is still new.

mapurves · August 25, 2021, 8:50pm

No need to apologize for the clutter. As you say, the system is new.

I looked at Andrew Pitonyak’s listing 5.8.4.

I tried using this:

Dim aPosition As New com.sun.star.awt.Point
aPosition.X = 2540
aPosition.Y = 2540
Dlg.setposition(aPosition)

and got this error.
Property or method not found: setposition.

My problem is that I inherited this code from the volunteer who started this project three years ago. For this particular upgrade, I’m at a loss. I’m not sure what the variable nwat in the following code is doing:

*with com.sun.star.awt.PosSize*

```
  	nWhat= .X + .Y*
```
end with*
Dlg.setPosSize(X, Y, 0, 0, nWhat)*

I know how to position the dialog by setting X and Y. I just can’t position it relative to the active cell. One problem is that the origin for a cell’s position (x,y) is at the top left corner of cell A1. The origin for the dialog (X,Y) is the top left corner of the Calc window (frame ?). I’m not sure about the exact term. Furthermore X and Y are in units that are different from the units for x,y, which are pixels. There seems to be relation between Y and y, the machine’s twips and the value 20. Somehow I have to relate Y to y. There is the ratio between the sizes of the units and the difference in the origins. That difference depends on what headings are at the top of the spreadsheet.

With no column headings, no toolbars and no formula bar, placing the dialog so that the top left corner is at the top left corner of A1, Y=58, y=0.

When showing the formula bar, column headings and the font work toolbar, placing the dialog again at the top left of cell A1, Y=144, and y=0.

The horizontal position is hard-coded, so it is not an issue.

I also have to convert the height of the dialog to the Y units. The dialog was created with height = 106. Dlg.GetPosSize().Height gives me 225. Measuring the height in Y units by getting the position when the top of the dialog is aligned with a cell and then getting the position when the bottom of the dialog is aligned with the cell and taking the difference is 256. Measuring the height of the dialog on the screen is 2.69 inches or 6.83 cm. So, depending on what height units you choose, I have: 106, 225, 256, 2.69 and 6.83.

I have no problem maintaining these spreadsheets for most situations, but this one is beyond me. Some of our volunteers would like the Event dialog to be positioned relative to where in the image they are transcribing data, which is a reasonable request. We are transcribing weather and other selected data from Navy and Coast Guard log books, which have been scanned. The images are very large and are set as the spreadsheet background. Selective data are entered using various dialogs etc and the data are then saved in an XML format, and the user moves on to the next image. The weather data being extracted are then being used to improve the climate datasets over the oceans for the years between 1840 and 1955.

This spreadsheet has nine different dialogs and 8,500 lines of macro code so usually I can find code that has some relevance, but not in this case. I have also looked in the two books by Andrew Petonyak, but I have come up with nothing. However, I have found that there is always someone on this forum who has solved a similar problem. I’m hoping there’s someone out there who can figure this one out.

mapurves · August 26, 2021, 5:55pm

Thank you, @jimk!

Your solution solves the problem of the ratio of “box units” to pixels. There is still the issue of accounting for the width of the Calc headings, toolbars and etc. I.e., if A1 is the Active cell, then your code places the dialog at the top of the Calc window, not at the top of A1.

For my particular set of toolbars and headings, I put the top of the dialog at the top of the active cell by modifying your code with this:

Dlg.setPosSize(point_pixels.X, point_pixels.Y+56*2.54,,, com.sun.star.awt.PosSize.POS)

To place it so the bottom of the box is aligned with the top of the active cell:

Dlg.setPosSize(point_pixels.X, point_pixels.Y+56*2.54 - 256,,, com.sun.star.awt.PosSize.POS)

where 56 is the number of “box units” between the top of the Calc window and the top of cell A1. I got it by getting the Y position of the dialog by placing it at the top of A1. I just need to figure out how to get that value of 56 from the system.

The last step will be to convert the dialog height to the units for Y.

Dlg.GetPosSize().Height = 225. My measurements by getting the Y position when the top and then the bottom of the dialog is aligned with a particular cell is 256 and, on my daughter’s Mac, it was 212.

Ratslinger · August 26, 2021, 9:19pm

@mapurves

This may be of help in dealing with the dialog height (also for width).

See → Maximize a dialog in Calc at runtime (OpenBASIC)

mapurves · August 26, 2021, 10:53pm

Hi Ratslinger,

I loaded your code, and I got the Model.Height of 106 and the Dialog.Height of 225, both of which I knew, but which was good to see confirmed. In theory, moving the dialog up 225 units should place it so the bottom of the dialog is aligned with the top of the active cell. However, a value of 256 is needed. The zoom value is 100%

Here is the box, aligned with the top of the active cell.

Here it is, moved up 225 units. The bottom of the dialog is below the top of the active cell.

I’m sure it’s a coincidence, but it appears that the top of the active cell aligns with the top of the information box to the right of the Cancel button.

And moved up the 256 units. Now it’s lined up. It needed an extra 31 units. 256/225 = 1.1378. Or, 256/106 = 2.415.

Your code gave 225/106 = 2.12264150943396

Your code used measurement units APPFont, for this to work I need to use MeasureUnit.MM_100TH.

I’m getting closer, but I’m not there yet.

I have been playing with numbers, and I can get this: 106 * 2.54 * .96 = 258. Is it possible that I have somehow to account inches/mm and my 96 points per inch? I think this may be it.

Thank you,

Michael

mapurves · August 27, 2021, 12:54am

I switched to one of our other dialogs, which does not to be positioned, but is much taller at 247 units versus 106. The formula I guessed at in this case does not work. The dialog is positioned about 40 units too high.

For the original dialog it was: BoxH = Dlg.Model.Height*2.54 *.96

For this dialog it needs to be: BoxH = Dlg.Model.Height*2.54 *.90

I give up for today.

Thanks for your help,

Michael

jimk · August 26, 2021, 3:14pm

According to LibreOffice: SheetCellRange Service Reference, the cell position is given in 1/100 mm. Also, it looks like the dialog position is in pixels. So, here is what I came up with:

oDoc = ThisComponent
oCell = oDoc.getCurrentSelection() 
oDialog = CreateUnoDialog(DialogLibraries.Standard.Dialog1)

point_mm100ths = oCell.Position
point_pixels = oDialog.convertPointToPixel(_
    point_mm100ths, com.sun.star.util.MeasureUnit.MM_100TH)
oDialog.setPosSize(_
    point_pixels.X, point_pixels.Y,,, com.sun.star.awt.PosSize.POS)
oDialog.execute()

nWhat tells which parameters to use. See LibreOffice: XWindow Interface Reference.

This solution is based on BASIC : How to setup width and height of grid referring to width and height of dialog - #2 by Ratslinger and the link given there.

Conversion methods are described at LibreOffice: XUnitConversion Interface Reference.