Needless to say, these calculation can get rather complicated.  Fortunately, I've done the work for you. Download the FormPositioner workbook. NOTE: FormPositioner can be used only with VBA UserForms -- it will not work with VB Forms.

Note also that FormPositioner does not work on split windows or frozen panes. Support for split windows and frozen panes will be added in a later release.

Copy the module modFormPositioner in to your project.  Then, do the following:

Set the StartupPosition property of your form to 0 - Manual.  This is very important.

 Declare a variable of type Positions:
     Dim PS As Positions

 Call the PositionForm function, passing it the following
 parameters. The PositionForm function returns a Positions structure.

 WhatForm                                         The userform object

 AnchorRange                                    The cell relative to which the form
                                                             should be displayed.

 NudgeRight                                       Optional: Number of points to nudge the
                                                             for to the right. This is useful with
                                                             bordered range. Typically, this should
                                                             be 0, but may be positive or negative.

 NudgeDown                                      Optional: Number of points to nudge the
                                                             for downward. This is useful with
                                                             bordered range. Typically, this should
                                                             be 0, but may be positive or negative.

 HorizOrientation:                              Optional: One of the following values:
            cstFhpNull = Left of screen
            cstFhpAppCenter = Center of Excel screen
            cstFhpAuto = Automatic (recommended and default)

            cstFhpFormLeftCellLeft = left edge of form at left edge of cell
            cstFhpFormLeftCellRight = left edge of form at right edge of cell
            cstFhpFormLeftCellCenter = left edge of form at center of cell

            cstFhpFormRightCellLeft = right edge of form at left edge of cell
            cstFhpFormRightCellRight = right edge of form at right edge of cell
            cstFhpFormRightCellCenter = right edge of form at center of cell

            cstFhpFormCenterCellLeft = center of form at left edge of cell
            cstFhpFormCenterCellRight = center of form at right edge of cell
            cstFhpFormCenterCellCenter = center of form at center of cell

VertOrientation Optional: One of the following values:

            cstFvpNull = Top of screen
            cstFvpAppCenter = Center of Excel screen
            cstFvpAuto = Automatic (recommended and default)

            cstFvpFormTopCellTop = top edge of form at top edge of cell
            cstFvpFormTopCellBottom = top edge of form at bottom edge of cell
            cstFvpFormTopCellCenter = top edge of form at center of cell

            cstFvpFormBottomCellTop = bottom edge of form at top of edge of cell
            cstFvpFormBottomCellBottom = bottom edge of form at bottom edge of cell
            cstFvpFormBottomCellCenter = bottom edge of form at center of cell

            cstFvpFormCenterCellTop = center of form at top of cell
            cstFvpFormCenterCellBottom = center of form at bottom of cell
            cstFvpFormCenterCellCenter = center of form at center of cell

For example:
         PS = PositionForm (UserForm1,Range("C12"),0,0,cstFvpAuto,cstFhpAuto)

Then, position the form using the values from PS:
    UserForm1.Top = PS.FrmTop
   UserForm1.Left = PS.FrmLeft
Finally, show the form:
   UserForm1.Show

 In summary, the code would look like

     Dim PS As Positions
     UserForm1.StartupPosition = 0
     PS = PositionForm (UserForm1,ActiveCell,0,0,cstFvpAuto,cstFhpAuto)
     UserForm1.Top = PS.FrmTop
     UserForm1.Left = PS.FrmLeft
     UserForm1.Show vbModal


The FormPositioner workbook includes a sample userform and a sample procedure called Test that you can use to see how the horizontal and vertical orientation parameters work.

And

And

The Positions structure is defined as follows, and is declared in the modFormPositioner module.

Public Type Positions
    FrmTop As Single ' Userform
    FrmLeft As Single
    FrmHeight As Single
    FrmWidth As Single

    RngTop As Single ' Passed in cell
    RngLeft As Single
    RngWidth As Single
    RngHeight As Single


    AppTop As Single 'Application
    AppLeft As Single
    AppWidth As Single
    AppHeight As Single

    WinTop As Single ' Window
    WinLeft As Single
    WinWidth As Single
    WinHeight As Single

    Cell1Top As Single ' 1st cell in visible range
    Cell1Left As Single
    Cell1Width As Single
    Cell1Height As Single

    LastCellTop As Single ' last visible cell in window
    LastCellLeft As Single
    LastCellWidth As Single
    LastCellHeight As Single

    BaseLeft As Single ' screen based coordinates for the upper left corner
    BaseTop As Single ' of cell.

    VComp As Single ' compensations for displayed object (toolbars, etc)
    HComp As Single

    NudgeDown As Single ' allow user to nudge positioning by a few pixels.
    NudgeRight As Single

#If VBA6 Then
    OrientationH As cstFormHorizontalPosition
    OrientationV As cstFormVerticalPosition
#Else
    OrientationH As Long
    OrientationV As Long
#End If

End Type
 

If you display the form modelessly, it will not move along with the application window and the sheet windows. It will retain its original position on the screen.  See here for code to overcome this circumstance so that the form will move along with the windows and retain its position relative to the application window or sheet windows.