Lookup a value on a UserForm

[gheyman]

I have a Userform (Userform1) where I have two Text Boxes (Textbox1 and Textbox2).

When the user right clicks on a cell in Sheet1 I want to open Userform1 and populate Textbox1 with the data from the cell to the right of where they right clicked Target -1
Then I want to perform a lookup of that data find its match in column C of sheet1 and get the associated data from column D and then place that result in Textbox2



In addition to that, when the user click commandbutton1 on the userform, I want to close userform1 but I want to copy the found data in Textbox2 so that they can paste it in the Target if they choose to once they are back on sheet1 (But I don't want to automatically past it. I want them to have to manually paste it)

Is this even possible?

 

[rpaulson]

I give it a try

here is the code for Userform

Private Sub CommandButton1_Click()
Unload Me

On Error Resume Next
Set myRange = Application.InputBox(Prompt:="Please click on the cell you want to paste you data", _
Title:="Select Cell", Type:=8)
r = myRange.Row
c = myRange.Column
If myRange Is Nothing Then Exit Sub
Cells(r, c) = TextBox2

End Sub

Private Sub UserForm_Initialize()
Data = ActiveCell.Offset(0, 1).Value
If Data = "" Then Exit Sub
TextBox1 = Data
TextBox2 = Cells(WorksheetFunction.Match(Data, Range("C:C")), "D")

End Sub

here is the code for the sheet you plan on using for the right-click

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    
    Dim vTemp As Variant
    
    If Target.Count > 1 Then Exit Sub
    
    If Not Intersect(Target, Cells) Is Nothing Then
    
        Call Open_userForm
        Cancel = True
    End If
    
End Sub

let me know how it works.

Ross