• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 162
  • Last Modified:

Use VB Script to systematically add information to a cell comment

I have a spreadsheet that is systematically updating cells from another source.  The cell contents gets populated with an Item#.  However, there is a lot of other information that needs to be associated with the cell, so I add all the rest of the info on to the cell comment.  It works great as long as there is only one entry per cell.

However, it is possible for the macro to comes across the cell a second time. And, I need it to append the new information to the original information that was already stored in the cell comment.  Is it possible to write something like:

Comnt = "Item Info: " & Chr(10) & Chr(13) & _
        "SKU: " & SKU2 & Chr(10) & Chr(13) & _
        "Lot#: " & Lot2 & Chr(10) & Chr(13) & _
        "Qty: " & Qty2 & Chr(10) & Chr(13) & _
        "LPN: " & LPN2 & Chr(10) & Chr(13) & Chr(10) & Chr(13) & objComment

Open in new window


the second time around?  Below is the code used to populate the cell the first time around.  Thanks!

Sheets("INVENTORY").Cells(y, x).Interior.Color = vbBlue
Sheets("INVENTORY").Cells(y, x).Value = SKU2

Comnt = "Item Info: " & Chr(10) & Chr(13) & _
        "SKU: " & SKU2 & Chr(10) & Chr(13) & _
        "Lot#: " & Lot2 & Chr(10) & Chr(13) & _
        "Qty: " & Qty2 & Chr(10) & Chr(13) & _
        "LPN: " & LPN2 & Chr(10) & Chr(13) & Chr(10) & Chr(13) & _
        "Location Info: " & Chr(10) & Chr(13) & _
        "Loc#: " & LocNum2 & Chr(10) & Chr(13) & _
        "AREA: " & s_AREA & Chr(10) & Chr(13) & _
        "WAREA: " & s_WAREA & Chr(10) & Chr(13) & _
        "WZONE: " & s_WZONE & Chr(10) & Chr(13) & _
        "Capacity: " & s_CAPACITY & Chr(10) & Chr(13) & _
        "Velocity: " & s_VELOCITY & Chr(10) & Chr(13) & _
        "Trav Seq: " & s_TRAVSEQ
        
ComLen4 = Len("Item Info: " & Chr(10) & Chr(13) & _
             "SKU: " & SKU2 & Chr(10) & Chr(13) & _
             "Lot#: " & Lot2 & Chr(10) & Chr(13) & _
             "Qty: " & Qty2 & Chr(10) & Chr(13) & _
             "LPN: " & LPN2 & Chr(10) & Chr(13) & Chr(10) & Chr(13))
        
With Sheets("INVENTORY").Cells(y, x)
    Set objComment = Sheets("INVENTORY").Cells(y, x).AddComment(Comnt)
    With objComment.Shape.TextFrame
        .Characters.Font.Size = 16
        .Characters.Font.Bold = False
        .Characters(1, 11).Font.Bold = True
        .Characters(1, 11).Font.Underline = True
        .Characters(ComLen4, 15).Font.Bold = True
        .Characters(ComLen4, 15).Font.Underline = True
    End With
End With

Sheets("INVENTORY").Cells(y, x).Comment.Visible = False
Sheets("INVENTORY").Cells(y, x).Comment.Shape.TextFrame.AutoSize = True

Open in new window

0
jmcclosk
Asked:
jmcclosk
  • 3
  • 3
1 Solution
 
RobSampsonCommented:
Hi, this should work.  I've added to check to see whether the comment exists or not.

Regards,

Rob.

Sheets("INVENTORY").Cells(y, x).Interior.Color = vbBlue
Sheets("INVENTORY").Cells(y, x).Value = SKU2

Comnt = "Item Info: " & Chr(10) & Chr(13) & _
        "SKU: " & SKU2 & Chr(10) & Chr(13) & _
        "Lot#: " & Lot2 & Chr(10) & Chr(13) & _
        "Qty: " & Qty2 & Chr(10) & Chr(13) & _
        "LPN: " & LPN2 & Chr(10) & Chr(13) & Chr(10) & Chr(13) & _
        "Location Info: " & Chr(10) & Chr(13) & _
        "Loc#: " & LocNum2 & Chr(10) & Chr(13) & _
        "AREA: " & s_AREA & Chr(10) & Chr(13) & _
        "WAREA: " & s_WAREA & Chr(10) & Chr(13) & _
        "WZONE: " & s_WZONE & Chr(10) & Chr(13) & _
        "Capacity: " & s_CAPACITY & Chr(10) & Chr(13) & _
        "Velocity: " & s_VELOCITY & Chr(10) & Chr(13) & _
        "Trav Seq: " & s_TRAVSEQ
        
ComLen4 = Len("Item Info: " & Chr(10) & Chr(13) & _
             "SKU: " & SKU2 & Chr(10) & Chr(13) & _
             "Lot#: " & Lot2 & Chr(10) & Chr(13) & _
             "Qty: " & Qty2 & Chr(10) & Chr(13) & _
             "LPN: " & LPN2 & Chr(10) & Chr(13) & Chr(10) & Chr(13))
        
With Sheets("INVENTORY").Cells(y, x)
	If TypeName(Sheets("INVENTORY").Cells(y, x).Comment) = "Comment" Then
		Sheets("INVENTORY").Cells(y, x).Comment.Text Sheets("INVENTORY").Cells(y, x).Comment.Text & Chr(10) & Chr(13) & Comnt
	Else
	    Set objComment = Sheets("INVENTORY").Cells(y, x).AddComment(Comnt)
	    With objComment.Shape.TextFrame
	        .Characters.Font.Size = 16
	        .Characters.Font.Bold = False
	        .Characters(1, 11).Font.Bold = True
	        .Characters(1, 11).Font.Underline = True
	        .Characters(ComLen4, 15).Font.Bold = True
	        .Characters(ComLen4, 15).Font.Underline = True
	    End With
	End If
End With

Sheets("INVENTORY").Cells(y, x).Comment.Visible = False
Sheets("INVENTORY").Cells(y, x).Comment.Shape.TextFrame.AutoSize = True

Open in new window

0
 
jmccloskAuthor Commented:
Hi Rob,

Unfortunately, this did not work.  For some reason, it never gets to this statement when it encounters a duplicate:

If TypeName(Sheets("INVENTORY").Cells(y, x).Comment) = "Comment" Then
		Sheets("INVENTORY").Cells(y, x).Comment.Text Sheets("INVENTORY").Cells(y, x).Comment.Text & Chr(10) & Chr(13) & Comnt

Open in new window


I have 10 lines of data in my test file.  Six of them are non-duplicates.  The other four lines are two sets of duplicates.  When it handles the six non-duplicates, it resolves correctly in the 'Else' part of the statement.  And, when it encounters the first record of a duplicate set, it also correctly resolves using the 'Else' part of the statement.  However, when it gets to the last two records, which are duplicates, it does not handle them at all with this If-Then.  It skips the statement completely.

I put two MsgBox statements in the If-Then.  One on the 'If' part and the second on the 'Else' part.  I get the 'Else' message only eight times.  And, I get the 'If' message zero times.
0
 
RobSampsonCommented:
Hmmm, instead of this
If TypeName(Sheets("INVENTORY").Cells(y, x).Comment) = "Comment" Then

Open in new window


Try this
If Sheets("INVENTORY").Cells(y, x).HasComment = True Then

Open in new window


Rob.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
jmccloskAuthor Commented:
Got this error on that line.If statement error
0
 
RobSampsonCommented:
Ok try
If Not Sheets("INVENTORY").Cells(y, x).Comment Is Nothing Then

Open in new window

0
 
jmccloskAuthor Commented:
This second suggestion did end up working.  I had another error in my code that was masking its success that I had to work through.  Once I resolved that, this statement works perfectly!  Thank you!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now