Show WinForm below a cell

Question

How can I show I winform that I create in VB.NET just below the active cell?

I have no idea how to solve this. I found the following promising solutions: Excel addin: Cell absolute position

-The accepted solution seems too complicated to work reliably. I got an error on the first row (Private Declare Function GetDC Lib "user32" (ByVal hwnd As Long) As Long)

-The second solution looked promising, but it didn't give me the right positions for my windows form.

The following adaptations of the second proposed solution does not create any errors but does not put the windows form in the correct position:

    Public Sub GetScreenPositionFromCell(cell As Excel.Range, excel As Excel.Application)

    Dim x As Double
    Dim y As Double
    If Not excel.ActiveWindow Is Nothing Then
        x = excel.ActiveWindow.PointsToScreenPixelsX(cell.Left)
        y = excel.ActiveWindow.PointsToScreenPixelsY(cell.Top)
    End If

    Me.Left = x
    Me.Top = y

    Me.Show()
    Me.TopMost = True
End Sub

EDIT: @Loating, here is how I have used your code. It's great and I am very happy that you are taking your time to help me with a solution. The x-coordinates seems to work while the x-coordinates are a bit off and more or less off depending on the zoom level.

    Public Sub ShowMeBelowActiveCell()
        Dim ExcelApp As Excel.Application = CType(AddinExpress.MSO.ADXAddinModule.CurrentInstance, AddinModule).ExcelApp
        Dim excelWindow = ExcelApp.ActiveWindow
        Dim cell = ExcelApp.ActiveCell
        Dim zoomFactor As Double = excelWindow.Zoom / 100
        Dim ws = cell.Worksheet

        ' PointsToScreenPixels returns different values if the scroll is not currently 1
        ' Temporarily set the scroll back to 1 so that PointsToScreenPixels returns a
        ' value we know how to handle.
        Dim origScrollCol = excelWindow.ScrollColumn
        Dim origScrollRow = excelWindow.ScrollRow
        excelWindow.ScrollColumn = 1
        excelWindow.ScrollRow = 1

        ' (x,y) are screen coordinates for the top left corner of the top left cell
        Dim x As Integer = excelWindow.PointsToScreenPixelsX(0)
        ' e.g. window.x + row header width
        Dim y As Integer = excelWindow.PointsToScreenPixelsY(0)
        ' e.g. window.y + ribbon height + column headers height
        Dim dpiX As Single = 0
        Dim dpiY As Single = 0
        Using g = Drawing.Graphics.FromHwnd(IntPtr.Zero)
            dpiX = g.DpiX
            dpiY = g.DpiY
        End Using

        ' Note: Each column width / row height has to be calculated individually.
        ' Before, tried to use this approach:
        ' var r2 = (Microsoft.Office.Interop.Excel.Range) cell.Worksheet.Cells[origScrollRow, origScrollCol];
        ' double dw = cell.Left - r2.Left;
        ' double dh = cell.Top - r2.Top;
        ' However, that only works when the zoom factor is a whole number.
        ' A fractional zoom (e.g. 1.27) causes each individual row or column to round to the closest whole number,
        ' which means having to loop through.
        For i As Integer = origScrollCol To cell.Column - 1
            Dim col = DirectCast(ws.Cells(cell.Row, i), Microsoft.Office.Interop.Excel.Range)
            Dim ww As Double = col.Width * dpiX / 72
            Dim newW As Double = zoomFactor * ww
            x += CInt(Math.Round(newW))
        Next

        For i As Integer = origScrollRow To cell.Row - 1
            Dim row = DirectCast(ws.Cells(i, cell.Column), Microsoft.Office.Interop.Excel.Range)
            Dim hh As Double = row.Height * dpiY / 72
            Dim newH As Double = zoomFactor * hh
            y += CInt(Math.Round(newH))
        Next

        excelWindow.ScrollColumn = origScrollCol
        excelWindow.ScrollRow = origScrollRow

        Me.StartPosition = Windows.Forms.FormStartPosition.Manual
        Me.Location = New Drawing.Point(x, y)
        Me.Show()

    End Sub
End Class

Answer 1

When the ScrollColumn and ScrollRow are both 1, then PointsToScreenPixelsX/Y seems to return the top left point of the top left visible cell in screen coordinates. Using this, the offset width and height to the active cell is calculated, taking into consideration the zoom setting.

        var excelApp = Globals.ThisAddIn.Application;
        var excelWindow = excelApp.ActiveWindow;

        var cell = excelApp.ActiveCell;
        double zoomFactor = excelWindow.Zoom / 100;
        var ws = cell.Worksheet;

        var ap = excelWindow.ActivePane; // might be split panes
        var origScrollCol = ap.ScrollColumn;
        var origScrollRow = ap.ScrollRow;
        excelApp.ScreenUpdating = false;
        // when FreezePanes == true, ap.ScrollColumn/Row will only reset
        // as much as the location of the frozen splitter
        ap.ScrollColumn = 1;
        ap.ScrollRow = 1;

        // PointsToScreenPixels returns different values if the scroll is not currently 1
        // Temporarily set the scroll back to 1 so that PointsToScreenPixels returns a
        // value we know how to handle.
        // (x,y) are screen coordinates for the top left corner of the top left cell
        int x = ap.PointsToScreenPixelsX(0); // e.g. window.x + row header width
        int y = ap.PointsToScreenPixelsY(0); // e.g. window.y + ribbon height + column headers height

        float dpiX = 0;
        float dpiY = 0;
        using (var g = Graphics.FromHwnd(IntPtr.Zero)) {
            dpiX = g.DpiX;
            dpiY = g.DpiY;
        }

        int deltaRow = 0;
        int deltaCol = 0;
        int fromCol = origScrollCol;
        int fromRow = origScrollRow;
        if (excelWindow.FreezePanes) {
            fromCol = 1;
            fromRow = 1;
            deltaCol = origScrollCol - ap.ScrollColumn; // Note: ap.ScrollColumn/Row <> 1
            deltaRow = origScrollRow - ap.ScrollRow; // see comment: when FreezePanes == true ...
        }

        // Note: Each column width / row height has to be calculated individually.
        // Before, tried to use this approach:
        // var r2 = (Microsoft.Office.Interop.Excel.Range) cell.Worksheet.Cells[origScrollRow, origScrollCol];
        // double dw = cell.Left - r2.Left;
        // double dh = cell.Top - r2.Top;
        // However, that only works when the zoom factor is a whole number.
        // A fractional zoom (e.g. 1.27) causes each individual row or column to round to the closest whole number,
        // which means having to loop through.
        for (int i = fromCol; i < cell.Column; i++) {
            // skip the columns between the frozen split and the first visible column
            if (i >= ap.ScrollColumn && i < ap.ScrollColumn + deltaCol)
                continue;

            var col = ((Microsoft.Office.Interop.Excel.Range) ws.Cells[cell.Row, i]);
            double ww = col.Width * dpiX / 72;
            double newW = zoomFactor * ww;
            x += (int) Math.Round(newW);
        }

        for (int i = fromRow; i < cell.Row; i++) {
            // skip the columns between the frozen split and the first visible column
            if (i >= ap.ScrollRow && i < ap.ScrollRow + deltaRow)
                continue;

            var row = ((Microsoft.Office.Interop.Excel.Range) ws.Cells[i, cell.Column]);
            double hh = row.Height * dpiY / 72;
            double newH = zoomFactor * hh;
            y += (int) Math.Round(newH);
        }

        ap.ScrollColumn = origScrollCol;
        ap.ScrollRow = origScrollRow;
        excelApp.ScreenUpdating = true;

        Form f = new Form();
        f.StartPosition = FormStartPosition.Manual;
        f.Location = new Point(x, y);
        f.Show();