Load userform in a specific position

Question

Dear All,

by Excel VBA I need to load/show an userform in a specific locked position and once done this userform can no longer be moved.

When I click on the first command button I need this:

while If I click on the second command button I need this:

Any way to check the position of the object whatever is the size/resolution of the monitor?

For you reference here you find the sample file

Thanks

Answer 1

I was not able to use your file because it opened with Excel on-line and no VBA or Userforms. If uploading workbooks, zip the file first and then we can download the Zipped file and un-zip. However, the following is the code to position a second Userform on an already open Userform.

Your Screen Shot displays 2 x Userform1. Not sure how you did that, but the code below has Userform1 as the first Userform to display and then Userform2 is positioned over top of Userform1.

Rather than run the code from buttons on Userform1, the code is run when the User clicks in TextBox1 or TextBox2. You should be able to assign it to buttons if desired. The cursor is positioned in TextBox3 when the Userform1 is shown.

Feel free to get back to me if any problems arise and I will attempt to assist further.

The following code to show Userform1 can be wherever you want but I have placed it in Module1 and called it with a Form Control Button positioned on a worksheet.

Sub Button1_Click() 

    'Called from a Form Control Button on Sheet1 

    UserForm1.Show 

End Sub

The following code goes in Userform1 module. I have used 3 controls on Userform1, TextBox1, TextBox2 and TextBox3. Note all of the comments.

Private Sub UserForm_Initialize()   'Event Sub. (Do not rename sub) 

    Me.TextBox3.SetFocus    'Position cursor in field other than TextBox1 or TextBox2 

End Sub 

Private Sub TextBox1_Enter()    'Event Sub. (Do not rename sub) 

    'This code runs as an Event when the User Clicks in TextBox1 on Userform1 

    Dim FormBorderSize As Double 

    Dim FormTitleSize As Double 

    Dim dblTop As Double 

    Dim dblLeft As Double 

    With Me  'Me is generic reference for the Userform (In this case Userform1) 

        'Get co-ordinates to display Userform2 under TextBox1 on Userform1 

        FormBorderSize = (.Width - .InsideWidth) 

        FormTitleSize = .Height - (.InsideHeight + FormBorderSize) 

        dblTop = .Top + .TextBox1.Top + .Height - .InsideHeight + FormTitleSize 

        dblLeft = .Left + .TextBox1.Left + .Width - .InsideWidth - FormBorderSize 

    End With 

    'Position of Userform2 under TextBox1 

    With UserForm2 

        .StartUpPosition = 0 

        .Top = dblTop 

        .Left = dblLeft 

    End With 

    UserForm2.Show 

End Sub 

Private Sub TextBox2_Enter()    'Event Sub. (Do not rename sub) 

    'This code runs as an Event when the User Clicks in TextBox2 on Userform1 

    Dim FormBorderSize As Single 

    Dim FormTitleSize As Single 

    Dim dblTop As Double 

    Dim dblLeft As Double 

    With Me  'Me is generic reference for the Userform (In this case Userform1) 

        'Get co-ordinates to display Userform2 under TextBox2 on Userform1 

        FormBorderSize = (.Width - .InsideWidth) 

        FormTitleSize = .Height - (.InsideHeight + FormBorderSize) 

        dblTop = .Top + .TextBox2.Top + .Height - .InsideHeight + FormTitleSize 

        dblLeft = .Left + .TextBox2.Left + .Width - .InsideWidth - FormBorderSize 

    End With 

    'Position of Userform2 under TextBox2 

    With UserForm2 

        .StartUpPosition = 0 

        .Top = dblTop 

        .Left = dblLeft 

    End With 

    UserForm2.Show 

End Sub 

The following code all goes in Userform2 module. It prevents the User from moving Userform2.

Dim dblFixedTop As Double       'At top of Userform2 Module 

Dim dblFixedLeft As Double      'At top of Userform2 Module 

Private Sub UserForm_Activate() 'Event Sub. (Do not rename sub) 

    'Assign the values for Userform Top and Left to variables 

    dblFixedTop = Me.Top 

    dblFixedLeft = Me.Left 

End Sub 

Private Sub UserForm_Layout()   'Event Sub. (Do not rename sub) 

    'Prevents user from moving the Userform 

    'dblFixedTop and dblFixedLeft are declared at top of module 

    'and values are assigned in UserForm_Activate event 

    Static bolFormActive As Boolean 

    'Executes only when form is activated and position is known 

    If bolFormActive Then 

        If Me.Top <> dblFixedTop Then 

            Me.Top = dblFixedTop 

        End If 

        If Me.Left <> dblFixedLeft Then 

            Me.Left = dblFixedLeft 

        End If 

    End If 

    bolFormActive = True 

End Sub

Answer 2

Hello lucausa75

Thank you for posting in Microsoft Community.

I understand that you have an issue in regard to the Excel VBA of loading the user form in a specific locked position. Let me check what I can do for this matter.

Based on the descriptions posted above is best handled by our Office VBA support and feedback since they are the experts and have a broad knowledge in regards to the issue. I understand the importance of having this issue fixed. Since you are here with us, I want to ensure that your time is worth it. Kindly access this link Office VBA support and feedback | Microsoft Learn to get assistance from our internal team.

Hope you understand.

Regards,

Jason Guer

Microsoft Community Moderator

Answer 3

@lucausa75,

Did you try the solution that I posted and if so, did it resolve your issue?

Answer 4

@lucausa75,

Did you try the solution that I posted and if so, did it resolve your issue?

Thanks a lot for your code which works as a charm.

To prevents the User from moving the Userform I'm using the below code

Option Explicit 

#If VBA7 And Win64 Then 

    Private Declare PtrSafe Function GetSystemMenu Lib "USER32" (ByVal hWnd As Long, ByVal bRevert As Long) As Long 

    Private Declare PtrSafe Function DeleteMenu Lib "USER32" (ByVal hme2nu As Long, ByVal nPosition As Long, ByVal wFlags As Long) As Long 

    Private Declare PtrSafe Function FindWindowA Lib "USER32" (ByVal lpClassName2 As String, ByVal lpWindowName2 As String) As Long 

#Else 

    Private Declare Function GetSystemMenu Lib "USER32" (ByVal hWnd As Long, ByVal bRevert As Long) As Long 

    Private Declare Function RemoveMenu Lib "USER32" (ByVal hme2nu As Long, ByVal nPosition As Long, ByVal wFlags As Long) As Long 

    Private Declare Function FindWindowA Lib "USER32" (ByVal lpClassName2 As String, ByVal lpWindowName2 As String) As Long 

#End If 

Private Const MF_BYPOSITION As Long = &H400 

Public Sub FormatUserForm(UserFormCaption As String) 

    Dim lFrmHdl As Long 

    Dim iCount As Integer 

    lFrmHdl = FindWindowA(vbNullString, UserFormCaption) 

    If lFrmHdl <> 0 Then 

        For iCount = 0 To 1 

            DeleteMenu GetSystemMenu(lFrmHdl, False), 0, MF_BYPOSITION 

        Next iCount 

    End If 

End Sub