Macro for Comment box

[cpf]

I am encoding 10 digit code on a comment box for most of my
worksheets. Question: how can I create a macro or any formula that
will identify comment boxes which has less than or more that 10 digit
codes? Hope you can help me. I badly need it to achieve accuracy of my
worsheet.
I can also be reached at YM, (e-mail address removed). I will keep my gtalk
activated in case u need further info. Thanks in advance

 

[Joel]

This macro should work

Sub TestComments()

With ActiveSheet
Set LastCell = .Cells.SpecialCells(xlCellTypeLastCell)
Set UsedArea = Range("A1", LastCell)

For Each cell In UsedArea
If Not cell.Comment Is Nothing Then
Comment = cell.Comment.Text
'remove persons name
If InStr(Comment, ":") > 0 Then
Comment = Mid(Comment, InStr(Comment, ":") + 1)
End If
If Len(Comment) < 10 Then
MsgBox (cell.Address & " : Comment has less than 10 characters")
End If
End If
Next cell
End With

End Sub

 

[Rick Rothstein \(MVP - VB\)]

It is not clear if you have a single cell on each worksheet each of which
has a 10-digit code or if you have, say, a column of cells on each worksheet
each cell of which has a 10-digit code in them. Here is a macro which will
iterate all the worksheets in your workbook, looking in Column B on each
worksheet to see if there is comment whose last line (meaning you can have a
name and even a description before the last line) is not 10 digits long and,
if any are found, a MessageBox for each one is displayed showing the
worksheet and cell address.

Sub CheckComments()
Dim C As Comment
Dim WS As Worksheet
Dim ComText() As String
For Each WS In Worksheets
For Each C In WS.Comments
If C.Parent.Column = 2 Then
ComText = Split(Trim(Replace(C.Text, vbLf, " ")))
If Not ComText(UBound(ComText)) Like "##########" Then
MsgBox "Comment on " & WS.Name & " at " & _
C.Parent.Address & vbLf & vbLf & _
"***************" & vbLf & C.Text & vbLf & _
"***************" & vbLf & vbLf & _
"is not 10 character long."
End If
End If
Next
Next
End Sub

If you are looking for only a single cell on each worksheet instead of a
column of cells, then change this line...

If C.Parent.Column = 2 Then

to this instead...

If C.Parent.Address = "$B$5" Then

where you would substitute your cell's address (in absolute form as shown)
for my $B$5 example.

Rick

 

[Rick Rothstein \(MVP - VB\)]

If you are looking for only a single cell on each worksheet instead of a

column of cells, then change this line...

If C.Parent.Column = 2 Then

to this instead...

If C.Parent.Address = "$B$5" Then

where you would substitute your cell's address (in absolute form as shown)
for my $B$5 example.

Actually, if you only have one comment per worksheet that you want to check,
this code is probably more efficient...

Sub CheckComments()
Dim C As Comment
Dim WS As Worksheet
Dim ComText() As String
For Each WS In Worksheets
Set C = WS.Range("B5").Comment
ComText() = Split(Trim(Replace(C.Text, vbLf, " ")))
If Not ComText(UBound(ComText)) Like "##########" Then
MsgBox "Comment on " & WS.Name & " at " & C.Parent.Address & _
vbLf & vbLf & "***************" & vbLf & C.Text & vbLf & _
"***************" & vbLf & vbLf & "is not 10 character long."
End If
Next
End Sub

Rick