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.
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.
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.
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.
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.
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
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()
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
2 Likes
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)
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.
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.
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.
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
Nope!
Go play with your “Magic”
I’m done here.
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.
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
1 Like
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.
1 Like