How to position a VBA UserForm near a cell

Hi again Ezybao

In the FIRST attached revised file:

1. pressing Ctrl+J in the "Quotation" worksheet positions the UserForm just to the right and down from the selected cell
2. if a valid code is typed (i.e. one found in the Database sheet column A), the form goes green to say that code was found and typing Enter key will paste it in the selected cell (as in my previous Answer Copy and paste a named range)
3. if the user presses Enter on an invalid code, a warning message box appears
4. if the user presses Esc at any time, the UserForm is closed.

It uses using a new UserForm control feature- the KeyDown event and checks each key depression. The Enter and Esc keys are identified by the specific keyboard constants shown in bold below (with some explanatory comments):

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    ' close UF if ESC pressed
    If KeyCode = vbKeyEscape Then Unload Me
    ' do nothing if no entry in ComboBox1
    If TextBox1.Text = "" Then Exit Sub
    ' otherwise entry exists so loop through the Names in the array to check entry is a code
    For n = 1 To UBound(Cds)
        If TextBox1.Text = Cds(n) Then
            ' if text is a valid code, paste and advise user
            Range(Cds(n)).Copy Selection.Resize(1, 1)
            ' ... prevent Textbox change re-triggering by actions below
            Me.DisableUFevents = True
            ' set the form to grey and label + text to nothing
            UserForm1.BackColor = -2147483633
            Label1.BackColor = -2147483633
            Label1.Caption = Cds(n) & " was pasted to " & Selection.Address(0, 0)
            TextBox1.Text = ""
            Me.DisableUFevents = False
            ' move down a cell (for the next code entry)
            Selection.Offset(1, 0).Resize(1, 1).Select
            Exit For
        End If
    Next n
    ' if the Enter button was pressed and code was NOT found..
    If KeyCode = vbKeyReturn And n = UBound(Cds) + 1 Then
        ' ..say and quit
        MsgBox "Code not found, please try again"
        Exit Sub
    End If
End Sub

Important: the positioning relies on the UserForm property Startup Position being set to 0 - Manual before the UserForm is launched- it MUST be set in the UserForm design (like I have done)- it cannot be set during run time, e.g. in the UserForm_Initialize code.

Revision 25 January 2024:

In Windows (at least), the form is then positioned by the bold lines in the procedure below:

Private Sub UserForm_Initialize()

    ' size the array to hold all names
    ReDim Cds(ActiveWorkbook.Names.Count)

    p = 1
    ' loop through the Names
    For n = 1 To ActiveWorkbook.Names.Count
        ' look at the nth Name
        CdNm = ActiveWorkbook.Names(n).Name
          ' if the range is on the Database sheet...
        CdRng = ActiveWorkbook.Names(n).RefersTo
        If InStr(1, CdRng, "Database", vbTextCompare) > 0 Then
          ' add the Name to the array
          Cds(p) = CdNm
          p = p + 1
        End If
    Next n

    ' resize the array to remove any unused locations
    ReDim Preserve Cds(p - 1)
    ' put cursor in textbox, ready for typing
    TextBox1.SetFocus

    ' for UF...
    With Me
        ' allow code changes to occur
        .DisableUFevents = False
        ' position UF to right of cell
        .Left = Application.Left + ActiveCell.Left + ActiveCell.Width + 20
        .Top = Application.Top + (Application.Height - Application.UsableHeight) + ActiveCell.Top
    End With

End Sub

Revision 2 25 January 2024

Differences on the Mac mean the above approach doesn't work on that platform since the .Top isn't treated the same as on a PC (but a hint was found by EzyBao on Ron de Bruin's website). Accordingly...

The SECOND attached file replaces the code behind sheet 1 "Quotation" with this:

Sub PasteCode()

    '

    ' PasteCode Macro

    '

    ' Keyboard Shortcut: Ctrl+j

    '

    ' place the form

    With UserForm1

        ' position UF to right of cell

        .Left = Application.Left + ActiveCell.Left + ActiveCell.Width + 20

        ' position the top, depending on platform (Mac or PC)

        #If Mac Then

            .Top = Application.UsableHeight - Application.Top + (Application.Height - Application.UsableHeight) + ActiveCell.Top



            #Else ' for PC...

            .Top = Application.Top + (Application.Height - Application.UsableHeight) + ActiveCell.Top

        #End If

    ' display

    .Show

    End With



End Sub

(That code in bold is removed from the UserForm_Initialize procedure.)

Hope this is what you need. If so, please be sure to mark this Answer as Selected.