Position userform relative to cell

[Formula11]

How can userform be positioned so that Top Left Hand corner is at Top Left corner of cell B2.

It should work when window is maximized or restored.

This doesn't seem to work.

Private Sub UserForm_Initialize()
    Me.StartUpPosition = 0
    Me.Top = Range("A1").Top + Range("A1").Height
    Me.Left = Range("A1").Left + Range("A1").Width
End Sub

 

[Domenic]

Have a look at the following article...

Form Positioner

Hope this helps!

 

[John_w]

The code below is based on Show UserForm near active cell - 64-bit version, courtesy of @Jaafar Tribak.

In a standard module:

Option Explicit

#If Win64 Then
    Const NULL_PTR = 0^
#Else
    Const NULL_PTR = 0&
#End If

Private Type RECT
    Left            As Long
    Top             As Long
    Right           As Long
    Bottom          As Long
End Type

#If VBA7 Then
    Private Declare PtrSafe Function IUnknown_GetWindow Lib "shlwapi" Alias "#172" (ByVal pIUnk As IUnknown, ByVal hUF As LongPtr) As Long
    Private Declare PtrSafe Function SetWindowPos Lib "user32" (ByVal hwnd As LongPtr, ByVal hWndInsertAfter As LongPtr, ByVal x As Long, ByVal Y As Long, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long) As Long
#Else
    Private Enum LongPtr
        [_]
    End Enum
    Private Declare Function IUnknown_GetWindow Lib "shlwapi" Alias "#172" (ByVal pIUnk As IUnknown, ByVal hUF As LongPtr) As Long
    Private Declare Function SetWindowPos Lib "user32" (ByVal hwnd As LongPtr, ByVal hWndInsertAfter As LongPtr, ByVal x As Long, ByVal Y As Long, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long) As Long
#End If


Public Sub Show_and_Position_Userform(form As UserForm, cell As Range)

    Const SWP_NOSIZE = &H1, SWP_NOACTIVATE = &H10, SWP_SHOWWINDOW = &H40
    Dim hForm As LongPtr, tTargetRect As RECT
  
    Call IUnknown_GetWindow(form, VarPtr(hForm))
    tTargetRect = GetRangeRect(cell)
    With tTargetRect
        Call SetWindowPos(hForm, NULL_PTR, .Left, .Top, 0&, 0&, SWP_SHOWWINDOW + SWP_NOSIZE)
    End With
   
End Sub

Private Function GetRangeRect(ByVal obj As Object) As RECT
    Dim oPane  As Pane
    Set oPane = ThisWorkbook.Windows(1&).ActivePane

    With GetRangeRect
        .Left = oPane.PointsToScreenPixelsX(obj.Left - 1&)
        .Top = oPane.PointsToScreenPixelsY(obj.Top)
        .Right = oPane.PointsToScreenPixelsX(obj.Left + obj.Width)
        .Bottom = oPane.PointsToScreenPixelsY(obj.Top + obj.Height)
    End With
End Function

In the Userform module:

Private Sub UserForm_Initialize()
    Show_and_Position_Userform Me, ActiveSheet.Range("B2")
End Sub

Note, the code above shows and positions the userform at the top left of cell B2, therefore don't call the userform Show method yourself:

Public Sub Show_Form()
    Dim form As UserForm1
    Set form = New UserForm1
    'Note - don't show the form using the Show method, because it is already displayed
    'form.Show
End Sub

 

[Formula11]

Thanks for the responses.

Domenic, tried the code in the link but was getting an error message.

UserForm1.Show vbModal      ' show the form

John_w, with the code above, the form shows for a split second and then disappears, it seems to show close to the correct location.

 

[Jaafar Tribak]

Building on John_w's suggestion, I would use the following modified version of the code. This is to avoid the annoying error that happens when using the SetWindowPos API on an already loaded modal userform. Also, this avoids the confusion on the user side who would normally expect to use the Form's Show Method for displaying the userform as usual.

The trick is to not call the Show_and_Position_Userform routine from the Initialize event. Instead we call the routine from the Layout event.
Then, we simply use the form's Tag Property to indicate that the code has already ran once. Therefore the Layout event won't fire the routine if the userform is reactivated or moved afterwards.

In a Standard Module:

Option Explicit

#If Win64 Then
    Const NULL_PTR = 0^
#Else
    Const NULL_PTR = 0&
#End If

Private Type RECT
    Left            As Long
    Top             As Long
    Right           As Long
    Bottom          As Long
End Type

#If VBA7 Then
    Private Declare PtrSafe Function IUnknown_GetWindow Lib "shlwapi" Alias "#172" (ByVal pIUnk As IUnknown, ByVal hUF As LongPtr) As Long
    Private Declare PtrSafe Function SetWindowPos Lib "user32" (ByVal hwnd As LongPtr, ByVal hWndInsertAfter As LongPtr, ByVal x As Long, ByVal y As Long, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long) As Long
#Else
    Private Enum LongPtr
        [_]
    End Enum
    Private Declare Function IUnknown_GetWindow Lib "shlwapi" Alias "#172" (ByVal pIUnk As IUnknown, ByVal hUF As LongPtr) As Long
    Private Declare Function SetWindowPos Lib "user32" (ByVal hwnd As LongPtr, ByVal hWndInsertAfter As LongPtr, ByVal x As Long, ByVal Y As Long, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long) As Long
#End If


Public Sub Show_and_Position_Userform(Form As Object, Cell As Range)
    Const SWP_NOSIZE = &H1, SWP_NOACTIVATE = &H10, SWP_SHOWWINDOW = &H40
    Dim hForm As LongPtr, tTargetRect As RECT
 
    If Len(Form.Tag) = 0& Then
        Form.Tag = "loaded"
        Call IUnknown_GetWindow(Form, VarPtr(hForm))
        tTargetRect = GetRangeRect(Cell)
        With tTargetRect
            Call SetWindowPos(hForm, NULL_PTR, .Left, .Top, 0&, 0&, SWP_NOSIZE + SWP_SHOWWINDOW)
        End With
    End If
End Sub

Private Function GetRangeRect(ByVal obj As Object) As RECT
    Dim oPane  As Pane
    Set oPane = ThisWorkbook.Windows(1&).ActivePane

    With GetRangeRect
        .Left = oPane.PointsToScreenPixelsX(obj.Left - 1&)
        .Top = oPane.PointsToScreenPixelsY(obj.Top)
        .Right = oPane.PointsToScreenPixelsX(obj.Left + obj.Width)
        .Bottom = oPane.PointsToScreenPixelsY(obj.Top + obj.Height)
    End With
End Function

Now, you can load & display the userform using the Show Method as usual be it modal or modeless.

Public Sub Show_Form()
    Dim Form As UserForm1
    Set Form = New UserForm1
    Form.Show
End Sub

Or simpler:

Public Sub Show_Form()
    UserForm1.Show
End Sub

 

[Formula11]

Thanks for looking at this Jaafar, it works now as expected.

 

[John_w]

Thanks for completing the solution, Jaafar. I knew there must be a fix for showing the form with the Show method.