vendredi 30 juin 2017

Remove Duplicates from String in Cell

So, I've been wrestling with this problem a while. Anytime CheckBox3 and CheckBox6 are both checked, it duplicates the "-", when I just need one. I've tried to use Len, as well as a array to go through the string to delete duplicates, but neither did the trick for me; I'm probably just doing it wrong, since I'm still a beginner with VBA. Anyway, here's my code:

Private Sub btnOK_Click()
Dim strText As String, strDelimiter As String

strDelimiter = " "

If cbxDD.Value = "DD" Or cbxMM.Value = "MM" Then
    MsgBox "Please enter both a month and date.", , "Invalid Entry"
    Exit Sub
End If

If CheckBox4.Value = True Then strText = strText & "DR" & strDelimiter
If CheckBox5.Value = True Then strText = strText & "C" & strDelimiter
If CheckBox2.Value = True Then strText = strText
If CheckBox1.Value = True Then strText = strText & ChrW(8730) & strDelimiter
If CheckBox3.Value = True Then strText = strText & "-" & strDelimiter
If CheckBox6.Value = True Then strText = strText & "- CP" & strDelimiter
If CheckBox7.Value = True Then strText = strText & "Cancelled" & strDelimiter
If CheckBox9.Value = True Then strText = strText & "TS21" & strDelimiter
If CheckBox8.Value = True Then strText = strText & "Lost" & strDelimiter

If Len(strText) > 0 = True Then
    strText = Left(strText, Len(strText) - Len(strDelimiter)) 'remove trailing delimiter
    ActiveCell.Value = cbxMM.Value & "/" & cbxDD.Value & " " & txtCode.Value & " " & strText
    Unload Me
Else
    MsgBox "No Status selected.", , "Invalid Entry"
End If

If CheckBox2.Value Or CheckBox4.Value Or CheckBox5.Value = True Then
    ActiveCell.Value = "x" & ActiveCell.Value
Else
    ActiveCell.Value = ActiveCell.Value
End If    
End Sub

Any help would be great! Thanks.




Aucun commentaire:

Enregistrer un commentaire