Dynamically position a dialog

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.

JohnSUN · August 27, 2021, 6:44am

Nice, concise solution. It seems that it needs to be lengthened a little - subtract the height of the dialog from point_pixels.Y (see the first phrase in the question, “top-bottom”), after checking if the dialog will not crawl off the screen.

Ratslinger · August 27, 2021, 4:12pm

@jimk

Thanks for the mention.

Your posted code, at least for me, does not place the dialog at the requested point. Have tried in both Ubuntu 20 and Win 10 now. Have looked for answer to this post on & off now for days. Do not see a satisfactory answer to the problem.

Do not find a direct relation from a cell point to the dialog placement nor any indirect method either.

mapurves · August 27, 2021, 9:04pm

I use this:

First = Doc.CurrentController.getFirstVisibleRow
point_mm100ths = LogSheet.getCellByPosition(1,First).Position
point_pixels_Y0 = Dlg.convertPointToPixel(point_mm100ths, com.sun.star.util.MeasureUnit.MM_100TH)

’ We get the position for the active cell.
Cell = Doc.getCurrentSelection()
point_mm100ths = Cell.Position
point_pixels_Y = Dlg.convertPointToPixel(point_mm100ths, com.sun.star.util.MeasureUnit.MM_100TH)

’ We calculate the Y position for the dialog.
Y = (point_pixels_Y.Y - point_pixels_Y0.Y)*Zoom/100 + Toolbars - BoxH

I had to account for the height of the toolbars etc manually by placing the dialog so the top of the dialog was at the top of the top row in the spreadsheet. Then

Toolbars = Dlg.Position.Y

I had to get the height of the dialog by aligning the top of the dialog with the top of a particular row and getting Dlg.Position.Y. Then I aligned the bottom of the dialog with the top of the same row and get its position. The height of the dialog was just the difference between the two values.

I did try BoxH = Dlg.Model.Height*2.54 *.96

which worked for this particular dialog, whose Model.Height was 106. I tried it with a dialog with a Model.Height of 247, but that formula did not work. To get the value measured, I needed

BoxH = Dlg.Model.Height*2.54 *.90

I was puzzled why there should be a difference between the ratios for the different sized boxes, but I decided just to go with manual measurements.

I wrote a macro for any users who want to position the dialog relative to the active cell. It posts instructions on the process. The macro opens a similar sized dialog three times: once to position it at the bottom of the toolbars, then once with the bottom and then the top positioned on a gridline. Each time the dialog is opened, it has instructions in the text box. The values for Toolbars and BoxH are then saved. When the Event dialog is called it will then be placed properly. As long as the user doesn’t add or remove any toolbars at the top of the spreadsheet, they would need to do this measuring only once, and then only if they want the Event dialog to open relative to their active cell.

Ratslinger · August 27, 2021, 9:43pm

The “code” you present requires some supposition. You should always post an entire picture. For example on the second line:

point_mm100ths = LogSheet.getCellByPosition(1,First).Position

the is nothing to show what “LogSheet” is. Yes I figured it to be the result of getting the sheet.

Also seems to be some manual determinations in there. Have personally gotten further with more automation but still not happy with the results. Trying to position at any X,Y location. Also figured out how to deal with the toolbars. I incorporate “CurrentController → VisibleAreaOnScreen”. This has provided a much better starting point.

But there are a few other glitches.

mapurves · August 27, 2021, 11:27pm

I’m sorry about not sending the full code. As you surmised:

DialogLibraries.LoadLibrary(“Standard”)
Doc = ThisComponent
Setup = Doc.Sheets.getByName(“Setup”)
LogSheet = Doc.Sheets.getByName(“Log Page”)

As for automating the Toolbars variable:

Toolbars = Doc.CurrentController.VisibleAreaOnScreen.Y + 12

I found this works only if the Calc is maximized. If Calc isn’t at the top of the window, I get an incorrect result. Even with Calc maximized I needed the value 12 to get the dialog to the top of the top row. Without it, the top of the dialog was partway over the column headings.

Sok for want of something else, I’ll have to stick to the manual method for getting the values for Toolbars and BoxH.

When I get the code cleaned up, I’ll send it along.

Ratslinger · August 28, 2021, 2:15am

Here is my code which works to place top left corner of dialog in selected cell (A1 is only one to work accurately at this point in my endeavor). This works without the Calc screen being maximized or at the top or at the left of the screen. Note in my code I have a fixed number of “27” being added (I believe as your “12”). This is actually the height of the Title bar and I am still looking for the place in the code where this value resides. Have some other ideas for the completion of selecting other cells and re-positioning the dialog so bottom lines up and not the top. But that will take some more time.

Sub openDialog2
DialogLibraries.LoadLibrary("MyNewLib")
oDoc = ThisComponent
Dim oCurrentController As Variant
Dim aVisibleAreaOnScreen As New com.sun.star.awt.Rectangle
Dim nY As Long
oCurrentController = ThisComponent.getCurrentController()
aVisibleAreaOnScreen = oCurrentController.VisibleAreaOnScreen
nY = aVisibleAreaOnScreen.Y
nX = aVisibleAreaOnScreen.X
oFrame = oCurrentController.getFrame()
oContainerWindow = oFrame.getContainerWindow()
aOutputSize = oContainerWindow.getOutputSize()
aPosSize = oContainerWindow.getPosSize()
nY2 = aPosSize.Y
oCell = oDoc.getCurrentSelection() 
oDialog = CreateUnoDialog(DialogLibraries.MyNewLib.Buttonsss)
point_mm100ths = oCell.Position
point_pixels = oDialog.convertPointToPixel(_
point_mm100ths, com.sun.star.util.MeasureUnit.MM_100TH)
oDialog.setPosSize(point_pixels.X + nX - aPosSize.X, point_pixels.Y + nY - nY2 + 27,,, com.sun.star.awt.PosSize.POS)
oDialog.execute()
End Sub

mapurves · August 28, 2021, 12:19am

Here’s the code. It uses two variables, the value of which I can’t calculate reliably.

DialogLibraries.LoadLibrary(“Standard”)
Dlg = CreateUnoDialog(DialogLibraries.Standard.EventInputWizard)
Doc = ThisComponent
Setup = Doc.Sheets.getByName(“Setup”)
LogSheet = Doc.Sheets.getByName(“Log Page”)
MoveEvent = Setup.getCellRangeByName(“MoveEventWizard”).String

’ We get measured values for BoxH and Toolbars.

BoxH = Setup.getCellRangeByName(“BoxHeight”).ValueToolbars = Setup.getCellRangeByName(“ToolbarHeight”).Value
Zoom = Doc.CurrentController.ZoomValue
X = 10 ’ X position of top left corner of dialog.

’ The position of rows are in pixels from the top of the image.
’ The position of the dialog is from the top of the Calc window.
’ We convert pixels to the box units with convertPointToPixel.
’ We get the position of the top row.
First = Doc.CurrentController.getFirstVisibleRow
point_mm100ths = LogSheet.getCellByPosition(1,First).Position
point_pixels_Y0 = Dlg.convertPointToPixel(point_mm100ths, com.sun.star.util.MeasureUnit.MM_100TH)

’ We get the position for the active cell.
Cell = Doc.getCurrentSelection()
point_mm100ths = Cell.Position
point_pixels_Y = Dlg.convertPointToPixel(point_mm100ths, com.sun.star.util.MeasureUnit.MM_100TH)

’ We calculate the Y position for the dialog.
If (MoveEvent = “Up”) Then
Y = (point_pixels_Y.Y - point_pixels_Y0.Y)Zoom/100 + Toolbars - BoxH
If Y < 5 Then ’ We’re too close to the top. Place it below the active cell.
Y = Y + 1.5BoxH
End If
Else ’ We place it below the active cell by the indicated number of rows.

ActiveRow = Cell.GetCellAddress.Row
point_mm100ths = LogSheet.getCellByPosition(X, ActiveRow + CInt(MoveEvent)+1).Position
Down = Dlg.convertPointToPixel(point_mm100ths, com.sun.star.util.MeasureUnit.MM_100TH)
Y = (Down.Y - point_pixels_Y0.Y)*Zoom/100 + Toolbars
End If

Dlg.setPosSize(X, Y, com.sun.star.awt.PosSize.POS)
ArgOut = Dlg.Execute()

sokol92 · August 28, 2021, 1:38pm

Maybe let’s try this variant (under the conditions of the original example).
Contains no magic constants. Zoom and the first visible cell of the screen are also taken into account.

' Show dialog near active cell
Sub Dialog4
  Dim oDoc, oControl, oCell, oDialog, oWindow, pos, pos0, posP, posW, zoom
  oDoc = ThisComponent                                       ' document
  oControl=oDoc.CurrentController                            ' view controller
  oCell = oDoc.CurrentSelection().getCellByPosition(0,0)     ' first cell in selection
  With oControl
    pos=oCell.Position
    pos0=oCell.SpreadSheet.getCellByPosition(.FirstVisibleColumn, .FirstVisibleRow).Position
    pos.X=pos.X - pos0.X                                     ' cell position on screen (mm/100)
    pos.Y=pos.Y - pos0.Y
    zoom=.ZoomValue / 100
    oWindow=.frame.ContainerWindow
    posW=oWindow.posSize                                     ' location window on screen
    oDialog = CreateUnoDialog(DialogLibraries.Standard.Dialog1)
    posP = oDialog.ConvertPointToPixel(pos, com.sun.star.util.MeasureUnit.MM_100TH)  ' cell position in points
    oDialog.setPosSize(posP.X * zoom + .VisibleAreaOnScreen.X - posW.X, _
        posP.Y * zoom + .VisibleAreaOnScreen.Y - posW.Y + oWindow.AccessibleContext.Location.Y,,, 3)
    'mri oDialog
    oDialog.execute()
  End With  
End Sub

Ratslinger · August 28, 2021, 3:55pm

This has the same issue I have faced. The further down in the rows you go, the more the dialog is lowered from the top of the cell. The same is true or the columns and the left edge of the selected cell.

Also there is no such item as “magic constants”. In my code I had not yet found (as noted) where this setting may be as I was more concerned about the dialog placement. Your code with the “ContainerWindow” resolved my issue without further digging. Thank you.

(sorry for the editing. cannot get space between paragraphs)

mapurves · August 28, 2021, 3:58pm

I copied your code and gave it a try. Like you, I found two problems:

the dialog’s position isn’t consistent when I move the entire Calc window around the screen; and,
the point_pixels.X value is way too large. For the position of my Active cell, it should be similar to point_pixels.Y.

point_pixels.X 1584

point_pixels.Y 428

It seems to be a problem with Cell.Position.X. Different units maybe???

oCell.Position.X 41920

oCell.Position.Y 11318

The X position should be around 360 given the position of my active cell. Actually, the dialog is placed so that the right-hand side is about 20 units to the left of the right-hand side of the Calc window. If I divide point_pixels.X by about 4.7, the dialog is placed just about where it should be, which is why I think there’s a units problem.

I get these values for my Active cell and the Calc window being maximised.

PPX = point_pixels.X 1584

nX = aVisibleAreaOnScreen.X 33

nX2 = aPosSize.X 0

Here are images with X set at 530 and your code for Y. The first is with Calc maximized, and the second is with the Calc window moved down and to the right.

It is an interesting problem that I have stumbled on. I often find that some problems seem really hard to solve, and they end up being trivial. And there are problems which seem like they would be easy, but they end up being really complicated. This is one of them.

Thank you for trying to solve this.

Ratslinger · August 28, 2021, 4:12pm

Did not see those problems you mention. Mine were different. Moving the Calc frame around (or resizing) had no bearing. Also I am working with Ubuntu 20.04.3 with LO v 7.2.0.4

Have not tried Win 10 yet. No sense unless other issues are resolved.

mapurves · August 28, 2021, 5:07pm

I’m on Windows 10. I tried this on a Mac, but I was at my daughter’s in California at the time, so I have no access to it here. It gave different results, too. I suspected it was a difference in pixel sizes, but I’m not sure.

sokol92 · August 28, 2021, 5:27pm

One more variant.
Another magic constant has appeared.
The horizontal shift has almost disappeared.
Tried with different zooms on Windows and Ubuntu.

' Show dialog near active cell
Sub Dialog5
  Dim oDoc, oControl, oCell, oCell0, oDialog, oWindow, pos, pos0, posP, posW, zoom
  oDoc = ThisComponent                                       ' document
  oControl=oDoc.CurrentController                            ' view controller
  oCell = oDoc.CurrentSelection().getCellByPosition(0,0)     ' first cell in selection
  With oControl
    pos=oCell.Position
    oCell0=oCell.SpreadSheet.getCellByPosition(.FirstVisibleColumn, .FirstVisibleRow)
    pos0=oCell0.Position
    pos.X=pos.X - pos0.X - (oCell.CellAddress.Column - oCell0.CellAddress.Column) * 90  ' cell position on screen (mm/100)
    pos.Y=pos.Y - pos0.Y
    zoom=.ZoomValue / 100
    oWindow=.frame.ContainerWindow
    posW=oWindow.posSize                                     ' location window on screen
    oDialog = CreateUnoDialog(DialogLibraries.Standard.Dialog1)
    posP = oDialog.ConvertPointToPixel(pos, com.sun.star.util.MeasureUnit.MM_100TH)  ' cell position in points
    oDialog.setPosSize(posP.X * zoom + .VisibleAreaOnScreen.X - posW.X, _
        posP.Y * zoom + .VisibleAreaOnScreen.Y - posW.Y + oWindow.AccessibleContext.Location.Y,,, 3)
    oDialog.execute()
  End With  
End Sub

Ratslinger · August 28, 2021, 5:41pm

Nope!

Go play with your “Magic”

I’m done here.

mapurves · August 28, 2021, 5:53pm

Thank you, Vladimir!!! That works with Windows 10 and with the Calc window maximized! When the Calc window is moved down and right, the dialog is positioned above and to the right of the active cell. This may not be a problem for us because I imagine all our users run Calc with the window maximized.

FYI, the box lines up vertically when the active cell is near the top of the image, but it is a couple of mm too low when the active cell is near the bottom of the image. The images are very large. The oCell.Position.Y for this test was 41774. This is not an issue for us, but I suspect that the conversion ConvertToPixels is off slightly in a decimal place.

Thank you.

sokol92 · August 30, 2021, 7:24am

And one more way (I hope - a direct one ).
Functions from this topic are used.

' Show dialog near cell.
Sub ShowDialog6 
  Dim oDoc, oCell, oAccCell, oDialog, oWindow, posW  
  oDoc = ThisComponent                                       ' document
  oCell = oDoc.CurrentSelection().getCellByPosition(0,0)     ' first cell in selection
  oAccCell=GetAccessibleCell(oDoc, oCell)                    ' AccessibleCell
  If oAccCell Is Nothing Then Exit Sub
  If oAccCell.Location.X>=0 Then                          ' cell is on screen
    oDialog = CreateUnoDialog(DialogLibraries.Standard.Dialog1)
    oWindow=oDoc.CurrentController.ComponentWindow
    posw=FindAccessibleRoot(oWindow.AccessibleContext).AccessibleContext.LocationOnScreen
    oDialog.setPosSize oAccCell.LocationOnScreen.X - posW.X, oAccCell.LocationOnScreen.Y - posW.Y,,,3
    oDialog.execute()
  End If  
End Sub

mapurves · August 30, 2021, 3:26pm

Brilliant!

In order to place the dialog above the active cell, I did the following:

Y = oAccContext.LocationOnScreen.Y - posW.Y - oDialog.PosSize.Height

It is close, but not quite there. If I just multiply the PosSize.Height by 1.1, it is right at the top of the cell.

Thank you for all your help.