This should always appear below the formula bar (if shown, or Ribbon if not). The -10 is to allow for window borders, though you could probably get that information from the registry if necessary!
[vba]
Option Explicit
Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long

'API's for getting the factors to convert points to pixels
Private Declare Function GetDC Lib "user32" ( _
ByVal hWnd As Long) As Long


Private Declare Function GetDeviceCaps Lib "gdi32" ( _
ByVal hdc As Long, _
ByVal nIndex As Long) As Long


Private Declare Function ReleaseDC Lib "user32" ( _
ByVal hWnd As Long, _
ByVal hdc As Long) As Long

Private Declare Function GetWindowRect Lib "user32.dll" ( _
ByVal hWnd As Long, lpRect As RECT) As Long
Private Type RECT
left As Long
top As Long
right As Long
bottom As Long
End Type
Private Const LOGPIXELSY = 90

'The width of a Y pixel in Excel's userform coordinates
Private Function PointsPerPixelY() As Double
Dim hdc As Long
hdc = GetDC(0)
PointsPerPixelY = 72 / GetDeviceCaps(hdc, LOGPIXELSY)
ReleaseDC 0, hdc
End Function

Private Sub UserForm_Activate()
Dim rectBk As RECT
Dim hWndBk As Long
hWndBk = GetWorkbookHandle(ActiveWorkbook.Name)
GetWindowRect hWndBk, rectBk
Me.top = rectBk.top * PointsPerPixelY
Me.left = Application.left + Application.Width - Me.Width - 10
Me.Height = (rectBk.bottom - rectBk.top) * PointsPerPixelY
End Sub

Function GetWorkbookHandle(strWBCaption As String, Optional lngHWnd As Long) As Long
Dim hWnd As Long, hWndDesk As Long
Dim strText As String
If lngHWnd = 0 Then lngHWnd = Application.hWnd
hWndDesk = FindWindowEx(lngHWnd, 0&, "XLDESK", vbNullString)
If hWndDesk <> 0 Then
hWnd = FindWindowEx(hWndDesk, hWnd, "EXCEL7", strWBCaption)
If hWnd <> 0 Then GetWorkbookHandle = hWnd
End If
End Function

[/vba]