VBA pop up box with vlookup

[katblack]

I am new to using VBA and not really sure how to accomplish what I want. Please see attached example.

In sheet1 I have a list of IDs. When I click on the cell for the Note column, I would like for a popup box to display the note from sheet2.

For example, in sheet1 the ID in A2 is 1. If I click on C2 I want the note from sheet2 C2 to appear in a pop up box (in this example it would be "apple").

I want this to change dynamically so that you could sort by the IDs or other information (for example "color") and always get the correct note - this is why I think vlookup is appropriate.

Additionally, I want new IDs to be able to be added and the note popup function would still work. For example, if ID 11 was added in sheet1 and sheet2, I could click on C12 in sheet1 and return the associated note.

Please let me know if anything is not clear. Thanks in advance!!

 

[Hui]

Katblack

Firstly, Welcome to the Chandoo.org Forums

Put the following code in the Code Module for Sheet1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lr As Integer, lr2 As Integer
Dim txt As String
Dim rng As Range

' Check if selected cell intersects with Column 3
lr = Range("A" & Rows.Count).End(xlUp).Row
If Intersect(ActiveCell, Range("C2:C" & CStr(lr))) Is Nothing Then Exit Sub

' Get Sheet2 range
lr2 = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Set rng = Worksheets("Sheet2").Range("A2:A" & CStr(lr))

' Find the Column A value in Sheet1 Range
frng = rng.Find(Target.Offset(0, -2))

' Extract the Column C value from Sheet2 & Display
txt = Worksheets("Sheet2").Range("C1").Offset(frng)
a = MsgBox("Test:" & txt, vbOKOnly, "Put some text here")

End Sub

See the attached file

You can also sort or add/delete data as you please

 

[katblack]

Hui,

thanks so much for your efforts! I am excited to explore the possibilities of VBA.

When I tried your example code with my real data, I am getting an error: Run-time error '1004': Application-defined or object-defined error.

The line that is highlighted when I try to debug is "txt = Worksheets("Sheet2").Range("C1").Offset(frng)"

After looking into what the issue may be, I think it could be the "ID" variable in my real data. This is a variable that is 10 digits long and exceeds the integer capacity of 2,147,483,647. I think this means that it cannot be stored as an integer variable, as your code has done.

I tried to change lr and lr2 to long variables instead with the following edits, but I am still getting the same error. Do you have any suggestions?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lr As Long, lr2 As Long
Dim txt As String
Dim rng As Range

' Check if selected cell intersects with Column 3
lr = Range("A" & Rows.Count).End(xlUp).Row
If Intersect(ActiveCell, Range("C2:C" & CLng(lr))) Is Nothing Then Exit Sub

' Get Sheet2 range
lr2 = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Set rng = Worksheets("Sheet2").Range("A2:A" & CLng(lr))

' Find the Column A value in Sheet1 Range
frng = rng.Find(Target.Offset(0, -2))

' Extract the Column C value from Sheet2 & Display
txt = Worksheets("Sheet2").Range("C1").Offset(frng)
a = MsgBox("Test:" & txt, vbOKOnly, "Put some text here")

End Sub

Thank you!!
Katherine

 

[Hui]

Please try changing this code as shown below

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lr As Integer, lr2 As Integer
Dim txt As String
Dim rng As Range
Dim frng As Integer


' Check if selected cell intersects with Column 3
lr = Range("A" & Rows.Count).End(xlUp).Row
If Intersect(ActiveCell, Range("C2:C" & CStr(lr))) Is Nothing Then Exit Sub

' Get Sheet2 range
lr2 = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Set rng = Worksheets("Sheet2").Range("A2:A" & CStr(lr))

' Find the Column A value in Sheet1 Range
frng = rng.Find(Target.Offset(0, -2)).Row

' Extract the Column C value from Sheet2 & Display
txt = Worksheets("Sheet2").Range("C1").Offset(frng - 1)
a = MsgBox("Test:" & txt, vbOKOnly, "Put some text here")

End Sub

 

[katblack]

Oh great, thanks so much! That worked perfectly!

 

[katblack]

Sorry - one more question. How would I make this code work correctly if there are other sheets in the workbook?

I am getting an error with the "activecell" command when I click on another sheet other than "sheet1".

Is there a way to define sheet1 as the reference for the activecell?

Thanks!

 

[Hui]

Place the code in the Worksheet Code Module for the Worksheet that you want to click on



Then other sheets won't come into play