PDA

View Full Version : Why does this Previos Cell code some times error, most of the time not ?



frank_m
04-24-2012, 01:18 PM
Did I code this correctly? -or- Is there a way to avoid using On Error Resume Next when I Set Rng ?
- The code usually works without it, but some times errors. (Hi Xld :hi:You should me the basic strategy but I forget if i ever asked this)

Option Explicit

Private prevCol As Long
Private prevRow As Long

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rng As Range
Set Rng = Target
If Not (Rng.Comment Is Nothing) Then
MsgBox "Comments are not permited in the Joblog cells." _
& vbNewLine & " - The comment " & Chr(34) & " " & Trim(Rng.Comment.Text) _
& " " & Chr(34) _
& " in the active cell will now be cocatenated at the end of the text in the Notes Column)."
With Rng.EntireRow.Cells(15)
.Value = .Value & " (Note from Column(" & Rng.Column & ") " & Trim(Rng.Comment.Text)
.WrapText = False
Rng.Comment.Delete
.Select
End With
End If
On Error Resume Next
Set Rng = ActiveSheet.Cells(prevRow, prevCol)
If Not (Rng.Comment Is Nothing) Then

Rng.Select

End If
On Error GoTo 0
If Not Target.HasFormula And Selection.Cells.Count = 1 Then
Target.Value = CleanString(Trim(Target.Value))
End If
prevCol = Target.Column
prevRow = Target.Row
End Sub

Private Function CleanString(StrIn As String) As String
' removes(non-printable) characters, including carriage returns and linefeeds.
' Does not remove special characters like symbols, international characters, etc.
' not sure who wrote this but I found it at: http://www.mrexcel.com/forum/showthread.php?t=618047
Dim iCh As Integer
CleanString = StrIn
For iCh = 1 To Len(StrIn)
If Asc(Mid(StrIn, iCh, 1)) < 32 Then
'remove special character
CleanString = Left(StrIn, iCh - 1) & CleanString(Mid(StrIn, iCh + 1))
Exit Function
End If
Next iCh
End Function