About Comments.

Closed
Pushpa - Apr 2, 2015 at 08:20 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Apr 7, 2015 at 11:49 AM
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.




2 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Apr 2, 2015 at 11:27 AM
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
0
Hi Trowa,

Thank you for the reply.

The above code is not working exactly because few changes in the data sheet.

Please find the below details:

Sheet1 - RData sheet
Sheet3 - EData Sheet.

Sheet1 - RData Sheet, Column E has the Products.

Sheet3- EData sheet, Column R has Products, Column J - Quantity and Column T - Price (AlphaNumeric).

Now when i need comments to be added for all the cells of Column E of sheet1. for which data of Column J & T of sheet2 should be displayed.

And also data in sheet1 and Sheet3 are not same, there is few products present and few products doesnot.
Please do the needful.

Regards,
Pushpa
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Apr 7, 2015 at 11:49 AM
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.
0