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.
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()