VBA/Macros

0

Good Day, 

I am trying to create a VBA or Macros that will transfer the data from a cell from say tab 2, to a comment box on another tab 1.  However this has to lign up with the correct persons name on both pages.  Am I able to do this?

Thanks!

Tasha

Answer
Discuss

Discussion

Should not be too difficult. Edit your question with a sample file and someone can give you more specific help with the macro.
don (rep: 1412) Jul 15, '18 at 1:49 pm
Yes. It's possible to write the value of a cell to a comment. This could be done automatically when the value of the linked cell changes. The new value could be added to the one last recorded or it could replace any existing. The more automation you want the more precise you have to specify your wishes. "Lign up with the correct person's name" is definitely not precise enough. Note however that a personal name in the comment is optional and may not be recommendable if that person doesn't make the comment. Or it might record who changed the referenced cell. As Don said, a workbook will help a lot but it will not suffice to replace your own precise input.
Variatus (rep: 1595) Jul 15, '18 at 10:12 pm
Hi!
I've added in the file.  If you see on the productivity tab, there is a column that says montiors and I have manually added in the cells from the monitor tab, into the comment on productivity.  This is what I need to complete. I have been trying to figure this out and trying a multitude of things but cannot get it to work.  It needs to line up with the persons name within the columns, as you will see when you open the file.  I need the information on the monitors tab to lign up with the agents name on the productivity tab, under the monitors column.  Obviously we will type on the monitors tab...  I can find ways to go from a comment to a cell but not the reverse. Thanks so much for your help.  Tasha
nhicks (rep: 2) Jul 16, '18 at 8:45 pm
Hi Tasha,
The file you posted doesn't match the description in your text. There is no column "Monitors" on the Productivity tab and no comments anywhere in the workbook. I'm not sure about your meaning of "Comments". Is it what you get when you right-click on a cell and select Insert Comment?
I also didn't find any logical connection between the Monitor and Productivity tabs. My understanding (probably wrong) is that you want to type something on the Monitor tab and write that same thing in a comment on the Productivity tab. A programmable instruction for that might read like, "When I type in column E on the Monitor tab, whatever I typed should appear in a comment in column B on the Productivity tab, where the name in Productivity!Column(B) must match the name in Monitor!Column(B)". It seems that the layout of your two tabs doesn't allow for such an instruction.
Variatus (rep: 1595) Jul 16, '18 at 10:23 pm
Hi!  
My apologies, I meant the column that says monitor scores on the productivity tab.  By comment , yes I mean when you right click on a cell and hit insert comment.  If my tab layouts do not allow for this, that could be why it's not working.  :)   Thank you.  
nhicks (rep: 2) Jul 17, '18 at 5:30 am
Add to Discussion

Answers

0
Selected Answer

Sorry, I was a bit slow here but I finally got it. Below is the code that does the job. It must be installed in the code sheet of the 'Monitors' worksheet. You can simply copy and paste either from here or from the code sheet in the attached workbook.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    ' 17 Jul 2018
    
    ' change as required
    Const TargetSheet As String = "Productivity"
    
    Dim Rng As Range
    Dim Rl As Long, Cl As Long              ' last row / column
    Dim Arr As Variant
    Dim C As Long
    Dim Txt As String
    
    ' find the last used row in column A
    Rl = Cells(Rows.Count, "A").End(xlUp).Row
    ' find the last used column in row 1
    Cl = Cells(1, Columns.Count).End(xlToLeft).Column
    ' changes above row 2 and to the left of column 2 (B) are ignored
    Set Rng = Range(Cells(2, 2), Cells(Rl, Cl))
    
    If Not Application.Intersect(Target, Rng) Is Nothing Then
        Set Rng = Range(Cells(Target.Row, "A"), Cells(Target.Row, Cl))
        Arr = Rng.Value
        For C = 2 To UBound(Arr, 2)
            If Len(Arr(1, C)) Then
                If Len(Txt) Then Txt = Txt & Chr(10)
                Txt = Txt & Arr(1, C)
            End If
        Next C
        
        With Worksheets(TargetSheet)
            ' look for list of names in column B
            Rl = .Cells(.Rows.Count, "B").End(xlUp).Row
            Set Rng = .Range(.Cells(1, "B"), .Cells(Rl, "B"))
            On Error Resume Next
            C = Application.Match(Arr(1, 1), Rng, 1)
            If Err Then
                MsgBox "The agent's name " & Arr(1, 1) & " wasn't" & vbCr & _
                       "found on the '" & TargetSheet & "' tab." & vbCr & _
                       "No comment could be added.", _
                       vbInformation, "Missing agent's name"
            Else
                ' write the comment to column R
                SetComment Worksheets(TargetSheet).Cells(C, "R"), Txt
            End If
        End With
    End If
End Sub
Sub SetComment(Cell As Range, _
               Optional ByVal Txt As String, _
               Optional ByVal Concat As Boolean)
    ' 12 Jul 2018
    ' deletes existing comment if Txt = "" and Concat = False
    
    Dim Cmt As String
    
    With Cell
        On Error Resume Next
        Cmt = .Comment.Text
        .Comment.Delete
        On Error GoTo 0
        If Concat Then
            If Len(Cmt) Then Cmt = Cmt & Chr(10)
        Else
            Cmt = ""
        End If
        
        Txt = Cmt & Txt
        If Len(Txt) Then .AddComment Cmt & Txt
    End With
End Sub

The attached workbook is fully functioning. Make any change on the 'Monitors' tab (otehr than in the column captions or column A) and the summary is written to a comment on the 'Productivity' tab. Any existing comment is replaced. If there are no Monitor Scores there will be no comment. The comment in R2 was created by the above code.

I have added some comments to the code which should enable you to make simple changes. Note that since the workbook now contains code it must be saved in XLSM format.

Discuss

Discussion

thank you so much!  I am going to try this and let you know.  You are amazing!  :) 
Tasha

Also, I think I selected the wrong thing when applying to this.  I will let you know how this works.  
nhicks (rep: 2) Jul 17, '18 at 9:58 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login