Get address of cell that triggered userform load, populate input fields via offset

Question

I load a userform via a double-click event executed on a range of cells. Once any of the cells in the range gets double clicked, my userform is loaded.

I would like the input boxes of the userform populated with data that is based on an offset of the triggering cell.

I am struggling with capturing the address of the cell that triggered the event, and consequently would need to figure out how to offset from that cell's column and obtain the relevant value for population in the userform.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Application.Intersect(Target, Sheets("Daily Summary").Range("D27:D93")) Is Nothing Then
        Cancel = True
        CommentDetails.Show
    End If
End Sub

a) How do I capture the dynamic cell address that triggered the userform load?
b) How do I offset three columns to the right, capture that cell's value and load it into the userform's input field named first_name?

---

Thanks to @Zwenn in the comments for pointing me in the right direction with Application.Caller. Updated code below, it executes but shows a Object Required error.
The name of the form is CommentDetails, the name of the input field is TextBoxArrival, both of which matches the code.

Private Sub Userform_initialize()
    Me.TextBoxArrival.Value = Cells(Application.Caller.Row, Application.Caller.Column + 1)
    'TextBoxArrival.Value = Cells(Application.Caller.Row, Application.Caller.Column + 1)
    'MsgBox Cells(Application.Caller.Row, Application.Caller.Column + 1).Value, vbOKOnly
End Sub

---

I understand I have to declare application.caller along with the calling method, which in my case is Sub Worksheet_BeforeDoubleClick. Still getting the same error. I tried circumventing this by calling another separate sub before loading the userform.

Where do I define application.caller?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim callingCellRow As Integer

    If Not Application.Intersect(Target, Sheets("Daily Summary").Range("D27:D29")) Is Nothing Then
    
       Select Case TypeName(Application.Caller)
            Case "Range"
                callingCellRow = Application.Caller.Row
                callingCellColumn = Application.Caller.Column
            Case "String"
                callingCellRow = Application.Caller.Row
                callingCellColumn = Application.Caller.Column
                callingCellSheet = Application.Caller
            Case "Error"
                MySheet = "Error"
            Case Else
                MySheet = "unknown"
        End Select
    
        With CommentDetails
            .Tag = callingCellRow '<~~ tell the UserForm there's something to bring in so that it'll fill controls from the sheet instead of initializing them
            .Show
            .Tag = ""
        End With
        Unload CommentDetails
    
    End If
End Sub

Answer 1

There's 3 ways to do this explained on Daily Dose of Excel by Dick Kusleika (18 years ago!). I prefer the 3rd option as it handles the form instance with a variable.

In Worksheet_BeforeDoubleClick you can have this:

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim objForm As UserForm1
    
    If Not Application.Intersect(Target, Me.Range("B2:D2")) Is Nothing Then
        Cancel = True
        Set objForm = New UserForm1 ' <-- use a variable for the form instance
        Set objForm.rngDoubleClicked = Target ' <-- set property of the form here with Target
        objForm.Show
    End If
End Sub

And then in the form code:

Option Explicit

Private m_rngDoubleClicked As Range

' set only
Public Property Set rngDoubleClicked(rng As Range)
    Set m_rngDoubleClicked = rng
End Property

' use the property
Private Sub UserForm_Activate()
    Dim strAddress As String
    Dim rngOffset As Range
    
    ' m_rngDoubleClicked is now the range that was double clicked
    strAddress = m_rngDoubleClicked.Parent.Name & "!" & m_rngDoubleClicked.Address
    Set rngOffset = m_rngDoubleClicked.Offset(3, 0)
    
    Me.TextBox1.Text = "The address of the double clicked cell is " & strAddress
    Me.TextBox2.Text = "The value 3 rows down from double clicked cell is " & rngOffset.Text

End Sub

Private Sub UserForm_Initialize()
    ' no args for initialization unfortunately :(
End Sub

Example: