About Comments.Closed

Question

Hello, 

I have a data in A1 cell and certain information of this data is in other sheet.

Now i need a pop-up comments when mouse just moves near to the cell A1.

Eg: A1 Cell : Apple

Data in other sheet is: qty:50 and price:100Rs.

SO now when i take the mouse point towards cell A1. Qty and price should be shown as the comment.

Can anyone please help me how to get this work done.




Configuration: Windows 7 / Chrome 41.0.2272.101

TrowaD · Answer

Hi Pushpa,

I imagined your situation as followed:
Sheet1 has a value in A1, where a comment has to be added.
That value can be found in column A of sheet2.
The qty and price can be found in sheet2 columns B & C.
Sheet2 has header in row 1.

Also the qty's in column B are numbers only and the price in column C is a combination of numbers and text.

Try the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub

Dim QtyValue As Integer
Dim PriceValue As String
Dim cFind As Range
Dim CommentThere As Object

With Sheets("Sheet2")
    Set cFind = .Range("A:A").Find(what:=Target, lookat:=xlWhole)
    QtyValue = .Cells(cFind.Row, "B").Value
    PriceValue = .Cells(cFind.Row, "C").Value
End With

Set CommentThere = Target.Comment

If CommentThere Is Nothing Then
    Range("A1").AddComment Text:= _
    Sheets("Sheet2").Range("B1") & QtyValue & Chr(10) & Sheets("Sheet2").Range("C1") & PriceValue
Else
    Range("A1").Comment.Text _
    Sheets("Sheet2").Range("B1") & QtyValue & Chr(10) & Sheets("Sheet2").Range("C1") & PriceValue
End If
End Sub


To implement the code, right-click sheet1's tab and select View Code.
Paste the code in the big white field.

Now try changing the value in sheet1 A1 and check if the comment shows up.

Let us know if something is unclear or needs to be adjusted.

Best regards,
Trowa

TrowaD · Answer

Hi Pushpa,

Then why not provide the sheet setup in the beginning?

The code is still setup to run automatically, even though you probably already have data in column E of sheet1.
To provide comments to the data that is already there; double-click a cell and hit enter to register as a new entry. Let me know if there is already to much data to handle it this way.

Since not all data will find a match, a message will appear when that happens.

Here is the amended code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E:E")) Is Nothing Then Exit Sub

Dim QtyValue As Integer
Dim PriceValue As String
Dim cFind As Range
Dim CommentThere As Object

With Sheets("Sheet3")
    Set cFind = .Range("R:R").Find(what:=Target, lookat:=xlWhole)
    If cFind Is Nothing Then
        MsgBox "The entered product does NOT exist in sheet3!", vbCritical
        Exit Sub
    End If
    QtyValue = .Cells(cFind.Row, "J").Value
    PriceValue = .Cells(cFind.Row, "T").Value
End With

Set CommentThere = Target.Comment

If CommentThere Is Nothing Then
    Target.AddComment Text:= _
    Sheets("Sheet3").Range("J1") & QtyValue & Chr(10) & Sheets("Sheet3").Range("T1") & PriceValue
Else
    Target.Comment.Text _
    Sheets("Sheet3").Range("J1") & QtyValue & Chr(10) & Sheets("Sheet3").Range("T1") & PriceValue
End If
End Sub

Best regards,
Trowa 
 
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.

About Comments.

2 responses

Similar discussions

Subscribe To Our Newsletter!