Vba to auto insert comment (by vlookup list) on cell data entry

[Closed]
Report
Posts
4
Registration date
Saturday May 11, 2013
Status
Member
Last seen
May 21, 2013
-
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
-
I have built an accounting program three columns of which are named Vat Number, Debit, and Credit
I need that each time I make an entry into any cell of these columns, the routine is called to insert a comment (via vlookup) or to delete any previous comment if data is deleted. It is important for me that vlookup is executed in vba direct to comment box.
I thank you in anticipation

3 replies

Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
481
Hi Lincau,

What do you mean by inserting a comment via Vlookup?
What is the content of the comment?
Which column should contain the comment?
Posts
4
Registration date
Saturday May 11, 2013
Status
Member
Last seen
May 21, 2013

Thanks for replying. Let me explain.
I have a workbook consisting of 2 sheet. One sheet is titled "list" and the other is "accounts".
Sheet "list" uses 2 columns and is used by function vlookup in sheet "accounts".
Two example rows of list would read:
cell A1 EHT cell B1 Expense Household Transport
cell A2 CB1 cell B2 Bank Savings A/c

Sheet "accounts" has columns titled"
Name-Date-Vat No- Item-Chq No-Voucher No-Amount-Rate-SubT-DB-Amount-CR
The columns I'm concerned with are:Name-Vat No-DB-CR
The Name cell contains a vlookup formula to generate the name from 'list' sheet according to Vat No entry. DB and CR might contain EHT and CB1 respectively meaning debit household transport and credit Bank savings.
Now I need a routine so that when I enter a Vat No a macro runs to generate a comment (in the same cell) from 'list' showing the name as a comment and doing away altogether with column Name. Same requirements are needed for DB and CR.
Needles to say if an existing entry in any of these columns is deleted, I need the comment to be deleted as well. It is also important that the macro runs upon data entry (and not manually)
I hope I have made myself clear and pls excuse my ignorance in vba routines.
Thanking you.
Posts
4
Registration date
Saturday May 11, 2013
Status
Member
Last seen
May 21, 2013

Hi Trowa,
I did as you said and the code is working fine. I suppose if I want to delete column A in 'accounts' I have to change target cols C,J,L by B,I,K. Can you confirm this pls. The code worked fine at once. Thanks a lot once again I think you're grat.
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
481
Hi Lincau,

Good to see it is to your liking.

And yes you are correct. Change C, J, L to B, I, K when deleting column A or you can hide Column A and leave the code as it is. It's up to you.

Best regards,
Trowa
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
481
???
I am trying to do the same thing only the comment is a number. This works with text but when I replace the list with a number the comment box is blank. How can I fix this?
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
481
Hi Kcampbell,

For that we have to turn your number into a text value.

Try the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("C")) Is Nothing Then _
If Intersect(Target, Columns("J")) Is Nothing Then _
If Intersect(Target, Columns("L")) Is Nothing Then Exit Sub

Dim MyNumber As String
Dim lRow As Integer

lRow = Sheets("list").Range("A1").End(xlDown).Row

If Target.Value = vbNullString Then Target.ClearComments

For Each cell In Sheets("list").Range("A1:A" & lRow)
    If cell.Value = Target.Value Then
        MyNumber = cell.Offset(0, 1).Value
        Target.AddComment
        Target.Comment.Text Text:=MyNumber
    End If
Next cell

End Sub


Best regards,
Trowa