Previous Topic Next Topic

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
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 responses

Answer 1 / 3
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
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?
0
Answer 2 / 3
lincau Posts 4 Registration date Saturday May 11, 2013 Status Member Last seen May 21, 2013
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.
0
lincau Posts 4 Registration date Saturday May 11, 2013 Status Member Last seen May 21, 2013
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
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
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: 
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
0
lincau Posts 4 Registration date Saturday May 11, 2013 Status Member Last seen May 21, 2013
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.
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
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
0
haejosh
Dec 26, 2013 at 11:41 AM
fake
0
Answer 3 / 3
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
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: 
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
0

Similar discussions

Create folder in desktop with vba, if exist, ignore
viriato74 -
praveen -

6 responses