Lookup a value on a UserForm

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,744
Office Version
  1. 365
Platform
  1. Windows
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?
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

rpaulson

Well-known Member
Joined
Oct 4, 2007
Messages
1,107
I give it a try

here is the code for Userform

Code:
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

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,360
Messages
5,528,235
Members
409,809
Latest member
VICKRAM

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top