I am loading a userform via a double-click event executed on a range of cells. Basically, once any of the cells in the range gets double clicked, my user form is loaded and I would like to have the input boxes of the user form populated with data that is based on an offset of the triggering cell.

I managed to get the double click event to work, but 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 user form.

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

Could anyone advise on:

a) how to capture the dynamic cell address that triggered the userform load

b) how to offset 3 columns to the right, capture that cell's value and load it into the user forms input field named first_name?

Much appreciate any guidance on this, thank you!

EDIT: 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, what am I doing wrong here?

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

Edit 2: I understand I have to declare application.caller along with the calling method, which in my case is Sub Worksheet_BeforeDoubleClick. I did just that but still am getting the same error. I tried circumventing this by calling another separate sub before loading the user form, but also no luck. Just where in the world 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

CodePudding user response：

You can do what you try in a simpler way:

1. Create a Public variable in a standard module:

   Public rngTarget As Range

2. Set it in BeforeDoubleClick event code:

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

        'add only the next code line:
        Set rngTarget = Target 'Now it is available in the whole workbook VBA project

        CommentDetails.Show
    End If
End Sub

3. Use it when initializing the UserForm:

Private Sub Userform_initialize()
    Me.TextBoxArrival.Value = rngTarget.Offset(,1).value
    MsgBox rngTarget.Offset(,1).Value, vbOKOnly, "This is it..."
End Sub

Not tested, but it should work...

CodePudding user response：

There's 3 ways to do this explained on