CODEWITHSUNDEEP
vbaexcel
dagan
dagan

Reputation: 215

Append "Version" of comments into cell

If the user enters a new comment, it is appended, but before the comment there should be some way we can convey to the user that you modified it. What I propose is the following

not approved. (This is initial content of the cell) Version 1 : approved. (This + above comment is content of the cell after first edit)
Version 2: subject to certain conditions. (This + the two comments above is content of the cell after second edit).

Please help me with this logic as I am on a tight deadline ! :( . I am very sure what I have below is pretty bad

If ActiveCell.Value = "" Then
    ActiveCell.Value = NRemark
Else
    For i = 1 To 5
       ActiveCell.Value = "Version" & i & ":" & ActiveCell.Value & vbNewLine & "Version"   & i + 1 &  ":" & NRemark
    Next i
End If

Upvotes: 1

Views: 106

Answers (1)

Chrismas007
Chrismas007

Reputation: 6105

Try this: (Creates initial remark as version 1. Adds a new remark at end with version = to the previous version plus 1. It find the previous version by searching all characters in the string backwards until it finds a number. THIS ONLY WORKS IF YOUR COMMENTS DO NOT CONTAIN NUMBERS! OTHERWISE YOU WILL HAVE TO USE MY OTHER CODE BELOW!)

If ActiveCell.Value = "" Then
    ActiveCell.Value = "Version 1: " & NRemark
Else
    If ActiveCell.Find("Version", LookIn:=xlValues, LookAt:=xlPart) is Nothing Then
        ActiveCell.Value = "Version 1: " & ActiveCell.Value
    Else
    End If
    For i = Len(ActiveCell.Value) to 1 Step -1
        currentChar = Mid(ActiveCell.Value, i, 1)
        If isnumeric(currentChar) = True Then Exit For
        Else
        End If
    Next i
    ActiveCell.Value = ActiveCell.Value & vbNewLine & "Version" & CInt(currentChar) + 1 & ": ", & NRemark 
    ActiveCell.WrapText = True
End If

If your comments include numeric characters then: (Puts the new version at the beginning so the search for current version number works if you have numeric characters later in the comments)

If ActiveCell.Value = "" Then
    ActiveCell.Value = "Version 1: " & NRemark
Else
    If ActiveCell.Find("Version", LookIn:=xlValues, LookAt:=xlPart) is Nothing Then
        ActiveCell.Value = "Version 1: " & ActiveCell.Value
    Else
    End If
    For i = 1 To Len(ActiveCell.Value)
        currentChar = Mid(ActiveCell.Value, i, 1)
        If IsNumeric(currentChar) = True Then Exit For
        Else
        End If
    Next i
    ActiveCell.Value = "Version" & CInt(currentChar) + 1 & ": " & NRemark & vbNewLine & ActiveCell.Value
    ActiveCell.WrapText = True
End If

Upvotes: 3

Related Questions