• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pop-Up to show data from another worksheet

L

lzhenl

New Member
Hi all,


I want to create a pop up that will show up when I enter a value in a cell that corresponds to another cell in another worksheet.


I will have 2 columns of values in Sheet 2.

For example in Sheet 2, cell A1 is Apples and cell B1 is "To buy from Vendor A".

When I enter data in Sheet 1 which correspond to a value in Column A of Sheet 2, the Pop up will show the corresponding Value in Sheet 2 column B.

This means if I enter Apples into any cell in Sheet 1, the Popup will show message "To buy from Vendor A."


Is that possible?


Much appreciated your help.


Best regards,

Zhen
 
Zhen


Firstly, Welcome to the Chandoo.org Forums


Do you really need a pop up?

Can you more simply use an Index/Match formula in an adjacent cell?
 
Thanks Hui for your reply.

I thought that a pop-up will attract my attention more. Furthermore, I am not able to use a Index/Match formula in a adjacent cell as I have to input other data in the adjacent cell.

The Index/Match formula will have to be input a cell which will be away from the immediate view of the excel sheet i.e. in column Z.


Is the pop-up way not possible?


Thanks again for your reply.
 
Zhen


Of course it's possible, I'm just trying to think of a simpler less obtrusive way to present data


Can you post a sample file with Sheet1 and Sheet2 so i can look at a solution for you

Refer: http://www.chandoo.org/forums/topic/posting-a-sample-workbook
 
Hi Hui,


Thanks again for your reply! Pls see below link.


https://docs.google.com/file/d/0B79KDA8KYvN7cUFQWUhkWHFfeTQ/edit?usp=sharing


What I need is for a popup to show up when I enter any value from Sheet 2 Column A.

The Popup message will show the adjacent value for the corresponding value.

For example, if I entered Apple - B into any cell in Sheet 1, a popup will show the message Vendor B - 1 week order time.


Thank you for your help :)


Cheers,

Zhen
 
Opps again, I didnt realise that the access was locked.

I had just granted public access. Pls let me know again if you are not able to access the file.


Thanks SirJB7 for letting me know about this :)


Cheers, Zhen
 
Hi, Izhenl!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Pop-Up%20to%20show%20data%20from%20another%20worksheet%20-%20Book1%20%28for%20Izhenl%20at%20chandoo.org%29.xlsm


This is the involved code:

-----

[pre]
Code: 
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
' constants
Const ksInputColumn = "A"
Const ksSearchWS = "Sheet2"
Const ksSearchColumn = "A"
Const ksRetrieveColumn = "B"
' declarations
Dim rngI As Range, wsS As Worksheet, rngS As Range, rngR As Range
Dim I As Long, bOk As Boolean
' start
Set rngI = Application.Intersect(Target, Range(ksInputColumn & ":" & ksInputColumn))
If rngI Is Nothing Then Exit Sub
If rngI.Cells.Count > 1 Then Exit Sub
' process
'  set ranges
Set wsS = Worksheets(ksSearchWS)
With wsS
Set rngS = .Columns(ksSearchColumn)
Set rngR = .Columns(ksRetrieveColumn)
End With
'  do something
With rngS
bOk = False
For I = 1 To .Rows.Count
If .Cells(I, 1).Value = rngI.Value Then
bOk = True
Exit For
Else
If .Cells(I, 1).Value = "" Then Exit For
End If
Next I
End With
With rngR
If bOk Then
MsgBox "Item '" & rngI.Value & "' found. Special note: " & .Cells(I, 1).Value, _
vbApplicationModal + vbInformation + vbOKOnly, "Item found!"
End If
End With
'  reset ranges
Set rngR = Nothing
Set rngS = Nothing
Set wsS = Nothing
' end
Set rngI = Nothing
End Sub
[/pre]
-----


Take care of the trailing spaces in string values of worksheet Sheet2 as they could lead to unwanted results.


Just advise if any issue.


Regards!
 
Dear SirJB7


Thanks! Its great!

But I found that if I paste the values into the Sheet 1, the popup won't activate. Do I have to enter the data one by one.


Cheers! Zhen
 
Hi,Izhenl!

That's right, this method works with Worksheet Change event which isn't triggered by copy and paste operations. As you wrote "when I enter any value" I assumed that pasting might be ommited. If it has to be included, well, let me think if I found a solution, but for the time being I'm afraid you're not lucky.

Regards!
 
You must log in or register to reply here.
Back
Top