Vba to auto insert comment (by vlookup list) on cell data entry
Closed
lincau
Posts
4
Registration date
Saturday May 11, 2013
Status
Member
Last seen
May 21, 2013
-
May 11, 2013 at 05:42 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Feb 24, 2015 at 11:28 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Feb 24, 2015 at 11:28 AM
Related:
- Excel vba comment
- Number to words in excel without vba - Guide
- Enable vba in excel - Guide
- Vba case like - Guide
- Excel date format dd.mm.yyyy - Guide
- Excel free download - Download - Spreadsheets
3 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
May 13, 2013 at 11:36 AM
May 13, 2013 at 11:36 AM
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?
What do you mean by inserting a comment via Vlookup?
What is the content of the comment?
Which column should contain the comment?
lincau
Posts
4
Registration date
Saturday May 11, 2013
Status
Member
Last seen
May 21, 2013
May 16, 2013 at 05:43 PM
May 16, 2013 at 05:43 PM
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.
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.
lincau
Posts
4
Registration date
Saturday May 11, 2013
Status
Member
Last seen
May 21, 2013
May 21, 2013 at 10:34 AM
May 21, 2013 at 10:34 AM
Hi TrowaD,
I'm sorry but it seems I have posted an explanation to you query as an answer. I hope this will reach you and maybe you can help me. Thank you so much
I'm sorry but it seems I have posted an explanation to you query as an answer. I hope this will reach you and maybe you can help me. Thank you so much
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
May 21, 2013 at 11:22 AM
May 21, 2013 at 11:22 AM
Thanks for explaining Lincau,
This is what I understand:
Upon entry in sheet "accounts" columns
C "Vat No"
J "DB"
L "CR"
Create comment.
Comment contains:
Value from sheet "list" Column B (no header?)
The row is determined by Vlookup (comparing entry with sheet "list" Column A).
Comment is deleted, when cell is cleared.
If this is correct you can try this code:
To implement the code:
Right-click on the "accounts" sheet tab and select "view code".
Paste the code in the big white field.
Let me know if everything is working as you though it would or if there is anything you would like to adjust.
Best regards,
Trowa
This is what I understand:
Upon entry in sheet "accounts" columns
C "Vat No"
J "DB"
L "CR"
Create comment.
Comment contains:
Value from sheet "list" Column B (no header?)
The row is determined by Vlookup (comparing entry with sheet "list" Column A).
Comment is deleted, when cell is cleared.
If this is correct you can try this 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 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
Target.AddComment
Target.Comment.Text Text:=cell.Offset(0, 1).Value
End If
Next cell
End Sub
To implement the code:
Right-click on the "accounts" sheet tab and select "view code".
Paste the code in the big white field.
Let me know if everything is working as you though it would or if there is anything you would like to adjust.
Best regards,
Trowa
lincau
Posts
4
Registration date
Saturday May 11, 2013
Status
Member
Last seen
May 21, 2013
May 21, 2013 at 12:44 PM
May 21, 2013 at 12:44 PM
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.
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.
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
May 23, 2013 at 10:13 AM
May 23, 2013 at 10:13 AM
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
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
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Feb 24, 2015 at 11:28 AM
Feb 24, 2015 at 11:28 AM
Hi Kcampbell,
For that we have to turn your number into a text value.
Try the following code:
Best regards,
Trowa
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
