Determine if part of a multi-line cell has been deleted using VBA

[Laavista]

I'm using Excel 2007. Using VBA, I need to determine if part of a multi-line cell has been deleted.

Example

Contents of cell A1 before user clicks on the cell
-comment 1
-status 2
-status 3


User deletes second line
-comment 1
-status 3

I need to determine if a 'line' has been deleted. I thought I could count the number of characters in the cell before and after, but can't get the number of characters in the cell before they make the changes.

Any suggestions?

 

[kevin.philips]

I'm using Excel 2007. Using VBA, I need to determine if part of a multi-line cell has been deleted.

Example

Contents of cell A1 before user clicks on the cell
-comment 1
-status 2
-status 3


User deletes second line
-comment 1
-status 3

I need to determine if a 'line' has been deleted. I thought I could count the number of characters in the cell before and after, but can't get the number of characters in the cell before they make the changes.

Any suggestions?

Put this in the sheet code:

Dim oldval1, oldval

Private Sub Worksheet_Change(ByVal Target As Range)
oldval1 = Target.Value
MsgBox "oldvalue: " & oldval & vbCrLf & "newval: " & oldval1
End Sub




Private Sub Worksheet_SelectionChange(ByVal Target As Range)
oldval = Target.Value
End Sub

 

[Teeroy]

The approach you've suggested (counting cells) will tell you if there are any changes in the string but not necessarily whether you've lost a line. You can combine the following with Kevin.Philips approach to see whether there are less lines (if that's what you need):

NoOfLines = Len(Range("A1")) - Len(Replace(Range("A1"), vbLf, "")) + 1
[code]

 

[Laavista]

Put this in the sheet code:

Dim oldval1, oldval

Private Sub Worksheet_Change(ByVal Target As Range)
oldval1 = Target.Value
MsgBox "oldvalue: " & oldval & vbCrLf & "newval: " & oldval1
End Sub




Private Sub Worksheet_SelectionChange(ByVal Target As Range)
oldval = Target.Value
End Sub

I will try this and let you know. THANKS!!

 

[Laavista]

Thanks so much!!! I will try this!