Need to create a pop-up to display data from another sheet

[Tome9499]

Hello All,


I want to create a pop up window that activates when users select a specific field From a range on Sheet3; Range(A1:A100). When active, the pop-up will display data from another sheet(Sheet2).


What I need to do is create a lookup that matches the value of cell A1 on Sheet3 to cell A1 on Sheet2, and then display all data from the corresponding row on Sheet 2 in a pop up window.


All of the tutorials I've seen regarding user forms talk about entering data. I need this form to display existing data. Am I barking up the wrong tree here? Could this be done with a custom message box instead?


Thanks,


Tom

 

[Luke M]

Here's a basic example code. To install, right click on sheet tab, view code, paste this in. MOdify as needed.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Range("A1:A100")) Is Nothing Then Exit Sub

If Target.Count > 1 Then Exit Sub


Dim DataSheet As Range

'Where is data?

Set DataSheet = Worksheets("Data").Range("A:G")


Str1 = WorksheetFunction.VLookup(Target, DataSheet, 2, False)

Str2 = WorksheetFunction.VLookup(Target, DataSheet, 3, False)

Str3 = WorksheetFunction.VLookup(Target, DataSheet, 4, False)

'etc


'display message

MsgBox Str1 & vbCr & Str2 & vbCr & Str3, vbOKOnly, "Data to Display"


End Sub

 

[Tome9499]

Hi Luke,


The Data is located on Sheet2. I want the popup on Sheet3. The Sheet2 Data range is A3:R1076. The column to be referenced in the vlookup is R.


Basically, if Sheet3 A3:A1076 (Unique identifier)is selected and matches Sheet2 R3:R1076 (unique identifier), then display display adjacent row data from Sheet2 columns A:Q in a pop up msg.


Thanks,


TomE

 

[Luke M]

How's this?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Range("A1:A100")) Is Nothing Then Exit Sub

If Target.Count > 1 Then Exit Sub


Dim DataSheet As Range

Dim TargetFound As Range

Dim xRow As Integer

Dim MessageString


'Where is data?

Set DataSheet = Worksheets("Sheet2").Range("A3:R1076")

Set TargetFound = DataSheet.Find(Target.Value)

If Not TargetFound Is Nothing Then

xRow = TargetFound.Row

For i = 1 To 17

'Build the msg string

Debug.Print DataSheet.Cells(xRow - 2, i).Value

MessageString = MessageString & DataSheet.Cells(xRow - 2, i).Value & vbCr

Next i


MsgBox MessageString, vbOKOnly, "Pop-up Title"


End If

End Sub

 

[Tome9499]

Luke,


That code worked really well with a few modifications (below). The resulting list displays a list of data as expected, but I need to work in the column headings so that the data displayed is kept in context.

[pre]

On Error GoTo enditall
If Intersect(Target, Range("A1:A1076")) = "" Then Exit Sub
If Target.Count > 1 Then Exit Sub

Dim DataSheet As Range
Dim TargetFound As Range
Dim xRow As Integer
Dim MessageString

'Where is data?
Set DataSheet = Sheet2.Range("B3:R1076")
Set TargetFound = DataSheet.Find(Target.Value)
If Not TargetFound Is Nothing Then
xRow = TargetFound.Row
For i = 1 To 17
'Build the msg string
Debug.Print DataSheet.Cells(xRow - 2, i).Value
MessageString = MessageString & DataSheet.Cells(xRow - 2, i).Value & vbCr
Next i

MsgBox MessageString, vbOKOnly, "Service Request Survey Detail"

End If

enditall:
Application.EnableEvents = True
End Sub

[/pre]

The data in question is he results from a customer sat survey. The data we are pulling into the pop-up msg is one of: Very Satisfied, Satisfied, Unsatisfied, Very Unsatisfied. You can see why column headings are important.


Any thoughts?


Thanks,


TomE

 

[Luke M]

This line:

MessageString = MessageString & DataSheet.Cells(xRow - 2, i).Value & vbCr



Becomes this:



MessageString = MessageString & Sheet2.Cells(2,i).Value & ": " & DataSheet.Cells(xRow - 2, i).Value & vbCr

This tells the code to take the value from row 2 of the data sheet (adjust if needed)

 

[Tome9499]

Luke,


It's working really well. Thanks for all of your assistance. I have one last question:


Currently the Target is (Target, Range("A1:A1076")). Column A contains the Unique identifier that we are looking up. Is there any way to alter the VBA so that if the user selects any cell in column A:N, then the target would be set to Cell A of the selected row?


The reasn for this is that in this sheet users are working exclusively in Columns O:R. Columns A:N are locked. Users shouldn't have to scroll all the way to column A when they could instead select any of the locked cells adjacent to the cells they are working in.


Thanks,


TomE

 

[Luke M]

Sure. First, we change the intersect check:

If Intersect(Target, Range("A1:N1076")) is Nothing Then Exit Sub




Next, we need to modify how the code gets the "target" value, by changing this line:

[code]

Set TargetFound = DataSheet.Find(Target.Value)

To this:



Set TargetFound = DataSheet.Find(Cells(Target.Row,"A").Value)

[/code]

Also, I just now noticed that I left the Debug.Print line of code in there. There's no need for it, you can delete that line if you want. All it's doing is sending the looked up value to the VB immediate window. My apologies. =/

 

[Tome9499]

Luke,


That does it! I figured out the first line of code to change, but not the second.


This is great.


Thank sagain,


TomE

 

[grahambotha]

Luke,


Please assist as well, nobody else has been able to and need to get this right ASAP.


I want to click on a cell and when doing so a pop up must display with information from several different sheets.


For example:

In "sheet 1" I want to click on cell "B3" and when doing so I want a pop up to display from "sheet 2 B10" and "sheet 3 C5"


Is this at all possible? Please assist. It would be greatly appreciated!!!


Thank you very much


Graham