Vba to auto insert comment (by vlookup list) on cell data entry
Closed
lincau
Posts
4
Registration date
Saturday 11 May 2013
Status
Member
Last seen
21 May 2013
-
11 May 2013 à 17:42
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 24 Feb 2015 à 11:28
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 24 Feb 2015 à 11:28
Related:
- Vba addcomment
- Vba find - Guide
- Vba select case like - Guide
- How to open vba in excel - Guide
- Vba userform - Guide
- Vba timer - Guide
3 responses
TrowaD
Posts
2921
Registration date
Sunday 12 September 2010
Status
Contributor
Last seen
27 December 2022
555
13 May 2013 à 11:36
13 May 2013 à 11:36
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 11 May 2013
Status
Member
Last seen
21 May 2013
16 May 2013 à 17:43
16 May 2013 à 17:43
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 11 May 2013
Status
Member
Last seen
21 May 2013
21 May 2013 à 10:34
21 May 2013 à 10:34
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 12 September 2010
Status
Contributor
Last seen
27 December 2022
555
21 May 2013 à 11:22
21 May 2013 à 11:22
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 11 May 2013
Status
Member
Last seen
21 May 2013
21 May 2013 à 12:44
21 May 2013 à 12:44
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 12 September 2010
Status
Contributor
Last seen
27 December 2022
555
23 May 2013 à 10:13
23 May 2013 à 10:13
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 12 September 2010
Status
Contributor
Last seen
27 December 2022
555
24 Feb 2015 à 11:28
24 Feb 2015 à 11:28
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
