Skip to content

Template Macros Troubleshooting

Jump to bottom
Rhiannon Cameron edited this page Jul 7, 2023 · 2 revisions

The Microsoft Excel Macro-Enabled Workbook file .xlsm format stores Visual Basic for Application (VBA) code in workbooks. The reason you get system warnings on whether it's safe to open a file is because people can use this feature to harm your computer. We have not done so in this case, all the inserted VBA is for requested functionalities by the Canadian Antimicrobial Resistance Genomics Research and Development Initiative (GRDI-AMR) Workgroup and implemented by the Centre for Infectious Disease Genomics (CIDGOH).

Troubleshooting

Within the Worksheet

By default, when you first open a macro-enabled workbook you’ll see a yellow “SECURITY WARNING” bar appear just underneath the ribbon. Clicking the “Enable Content” button will enable macros. (source: https://www.automateexcel.com/macros/enable-macros/)

If you instead see a red "SECURITY RISK" warning proceed to the next troubleshooting section.

File Explorer (Windows)

Excel Red Banner Warning: SECURITY RISK Microsoft has blocked macros from running because the source of this file is untrusted

To resolve this issue, find the file on your computer, right-click and select "Properties". Navigate to "Security: This file came from another computer and might be blocked to help protect this computer." and select "Unblock".

Add a Trusted Location

If you consistently save the template is a specific folder, or access via a GitHub repository clone, you can follow the "Add a new Trusted Location" on this page: https://www.automateexcel.com/macros/enable-macros/

Template VBA Code

Copy of the VBA code in the Excel Template for users to reference to confirm it is safe.

Sample Collection & Processing

'Sample Collection & Processing
Private Sub Worksheet_Change(ByVal Target As Range)

'Updated by Rhiannon Cameron 2022/11/28

Select Case True`

'Multiselect (trimmed)
Case Not Intersect(ActiveCell, Range("X:X,AA:AA,AB:AB,AC:AC,AD:AD,AE:AE,AF:AF,AG:AG,AH:AH,AI:AI,AN:AN")) Is Nothing

    'Updated by Extendoffice 2019/11/13
    'Updated by Ken Gardner 2022/07/11
    'Modified by Rhiannon Cameron 2022/11/28
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    Dim semiColonCnt As Integer
    If Target.Count > 1 Then Exit Sub
        On Error Resume Next
        Set xRng = Intersect(Target, Range("X3:X1522,AA3:AA1522,AB3:AB1522,AC3:AC1522,AD3:AD1522,AE3:AE1522,AF3:AF1522,AG3:AG1522,AH3:AH1522,AI3:AI1522,AN3:AN1522"))
        If xRng Is Nothing Then Exit Sub
            Application.EnableEvents = False
            'If Not Application.Intersect(Target, xRng) Is Nothing Then
            If Application.Intersect(Target, xRng) Then
                xValue2 = Trim(Target.Value)
                Application.Undo
                xValue1 = Trim(Target.Value)
                Target.Value = xValue2
                If xValue1 <> "" Then
                    If xValue2 <> "" Then
                        If xValue1 = xValue2 Or xValue1 = xValue2 & ";" Or xValue1 = xValue2 & "; " Then ' leave the value if only one in list
                            xValue1 = Replace(xValue1, "; ", "")
                            xValue1 = Replace(xValue1, ";", "")
                            Target.Value = xValue1
                        ElseIf InStr(1, xValue1, "; " & xValue2) Then
                            xValue1 = Replace(xValue1, xValue2, "") ' removes existing value from the list on repeat selection
                            Target.Value = xValue1
                        ElseIf InStr(1, xValue1, xValue2 & ";") Then
                            xValue1 = Replace(xValue1, xValue2, "")
                            Target.Value = xValue1
                        Else
                            Target.Value = xValue1 & "; " & xValue2
                        End If
                            
                        Target.Value = Replace(Target.Value, ";;", ";")
                        Target.Value = Replace(Target.Value, "; ;", ";")
                        If InStr(1, Target.Value, "; ") = 1 Then ' check for ; as first character and remove it
                            Target.Value = Replace(Target.Value, "; ", "", 1, 1)
                        End If
                            
                        If InStr(1, Target.Value, ";") = 1 Then
                            Target.Value = Replace(Target.Value, ";", "", 1, 1)
                        End If
                            
                        semiColonCnt = 0
                        For i = 1 To Len(Target.Value)
                        If InStr(i, Target.Value, ";") Then
                            semiColonCnt = semiColonCnt + 1
                        End If
                            
                        Next i
                        If semiColonCnt = 1 Then ' remove ; if last character
                            Target.Value = Replace(Target.Value, "; ", "")
                            Target.Value = Replace(Target.Value, ";", "")
                        End If
                    End If
                End If
            End If
        Application.EnableEvents = True
                
'Single select (trimmed)
Case Not Intersect(ActiveCell, Range("J:J,AL:AL,AM:AM")) Is Nothing

    'Updated by Extendoffice 2019/11/13
    'Updated by Ken Gardner 2022/07/11
    'Modified by Rhiannon Cameron 2022/11/28
    Dim xxRng As Range
    Dim xxValue1 As String
    Dim xxValue2 As String
    Dim xsemiColonCnt As Integer
    If Target.Count > 1 Then Exit Sub
        On Error Resume Next
        Set xxRng = Intersect(Target, Range("J3:J1522,AL3:AL1522,AM3:AM1522"))
        If xxRng Is Nothing Then Exit Sub
            Application.EnableEvents = False
            'If Not Application.Intersect(Target, xRng) Is Nothing Then
            If Application.Intersect(Target, xxRng) Then
                xxValue2 = Trim(Target.Value)
                Application.Undo
                xxValue1 = Trim(Target.Value)
                Target.Value = xxValue2
                If xxValue1 <> "" Then
                    If xxValue2 <> "" Then
                        If xxValue1 = xxValue2 Or xxValue1 = xxValue2 & ";" Or xxValue1 = xxValue2 & "; " Then ' leave the value if only one in list
                            xxValue1 = Replace(xxValue1, "; ", "")
                            xxValue1 = Replace(xxValue1, ";", "")
                            Target.Value = xxValue1
                        ElseIf InStr(1, xxValue1, "; " & xxValue2) Then
                            xxValue1 = Replace(xxValue1, xxValue2, "") ' removes existing value from the list on repeat selection
                            Target.Value = xxValue1
                        ElseIf InStr(1, xxValue1, xxValue2 & ";") Then
                            xxValue1 = Replace(xxValue1, xxValue2, "")
                            Target.Value = xxValue1
                        Else
                            Target.Value = xxValue2 ' replace with new value
                        End If
                            
                        Target.Value = Replace(Target.Value, ";;", ";")
                        Target.Value = Replace(Target.Value, "; ;", ";")
                        If InStr(1, Target.Value, "; ") = 1 Then ' check for ; as first character and remove it
                            Target.Value = Replace(Target.Value, "; ", "", 1, 1)
                        End If
                            
                        If InStr(1, Target.Value, ";") = 1 Then
                            Target.Value = Replace(Target.Value, ";", "", 1, 1)
                        End If
                            
                        xsemiColonCnt = 0
                        For i = 1 To Len(Target.Value)
                        If InStr(i, Target.Value, ";") Then
                            xsemiColonCnt = xsemiColonCnt + 1
                        End If
                            
                        Next i
                        If xsemiColonCnt = 1 Then ' remove ; if last character
                            Target.Value = Replace(Target.Value, "; ", "")
                            Target.Value = Replace(Target.Value, ";", "")
                        End If
                    End If
                End If
            End If
        Application.EnableEvents = True
        
End Select
        
End Sub

Host Information

'Host Information

Private Sub Worksheet_Change(ByVal Target As Range)
'Single select (trimmed)

    'Updated by Extendoffice 2019/11/13
    'Updated by Ken Gardner 2022/07/11
    'Modified by Rhiannon Cameron 2022/11/28
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    Dim semiColonCnt As Integer
    If Target.Count > 1 Then Exit Sub
        On Error Resume Next
        Set xRng = Intersect(Target, Range("C3:C1522,D3:D1522,G3:G1522"))
        If xRng Is Nothing Then Exit Sub
            Application.EnableEvents = False
            'If Not Application.Intersect(Target, xRng) Is Nothing Then
            If Application.Intersect(Target, xRng) Then
                xValue2 = Trim(Target.Value)
                Application.Undo
                xValue1 = Trim(Target.Value)
                Target.Value = xValue2
                If xValue1 <> "" Then
                    If xValue2 <> "" Then
                        If xValue1 = xValue2 Or xValue1 = xValue2 & ";" Or xValue1 = xValue2 & "; " Then ' leave the value if only one in list
                            xValue1 = Replace(xValue1, "; ", "")
                            xValue1 = Replace(xValue1, ";", "")
                            Target.Value = xValue1
                        ElseIf InStr(1, xValue1, "; " & xValue2) Then
                            xValue1 = Replace(xValue1, xValue2, "") ' removes existing value from the list on repeat selection
                            Target.Value = xValue1
                        ElseIf InStr(1, xValue1, xValue2 & ";") Then
                            xValue1 = Replace(xValue1, xValue2, "")
                            Target.Value = xValue1
                        Else
                            Target.Value = xValue2 ' replace with new value
                        End If
                            
                        Target.Value = Replace(Target.Value, ";;", ";")
                        Target.Value = Replace(Target.Value, "; ;", ";")
                        If InStr(1, Target.Value, "; ") = 1 Then ' check for ; as first character and remove it
                            Target.Value = Replace(Target.Value, "; ", "", 1, 1)
                        End If
                            
                        If InStr(1, Target.Value, ";") = 1 Then
                            Target.Value = Replace(Target.Value, ";", "", 1, 1)
                        End If
                            
                        semiColonCnt = 0
                        For i = 1 To Len(Target.Value)
                        If InStr(i, Target.Value, ";") Then
                            semiColonCnt = semiColonCnt + 1
                        End If
                            
                        Next i
                        If semiColonCnt = 1 Then ' remove ; if last character
                            Target.Value = Replace(Target.Value, "; ", "")
                            Target.Value = Replace(Target.Value, ";", "")
                        End If
                    End If
                End If
            End If
        Application.EnableEvents = True

End Sub

Strain and Isolate Information

'Strain and Isolate Information

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Rhiannon Cameron 2022/11/28

'Single select (trimmed)

    'Updated by Extendoffice 2019/11/13
    'Updated by Ken Gardner 2022/07/11
    'Modified by Rhiannon Cameron 2022/11/28
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    Dim semiColonCnt As Integer
    If Target.Count > 1 Then Exit Sub
        On Error Resume Next
        Set xRng = Intersect(Target, Range("P3:P1522"))
        If xRng Is Nothing Then Exit Sub
            Application.EnableEvents = False
            'If Not Application.Intersect(Target, xRng) Is Nothing Then
            If Application.Intersect(Target, xRng) Then
                xValue2 = Trim(Target.Value)
                Application.Undo
                xValue1 = Trim(Target.Value)
                Target.Value = xValue2
                If xValue1 <> "" Then
                    If xValue2 <> "" Then
                        If xValue1 = xValue2 Or xValue1 = xValue2 & ";" Or xValue1 = xValue2 & "; " Then ' leave the value if only one in list
                            xValue1 = Replace(xValue1, "; ", "")
                            xValue1 = Replace(xValue1, ";", "")
                            Target.Value = xValue1
                        ElseIf InStr(1, xValue1, "; " & xValue2) Then
                            xValue1 = Replace(xValue1, xValue2, "") ' removes existing value from the list on repeat selection
                            Target.Value = xValue1
                        ElseIf InStr(1, xValue1, xValue2 & ";") Then
                            xValue1 = Replace(xValue1, xValue2, "")
                            Target.Value = xValue1
                        Else
                            Target.Value = xValue2 ' replace with new value
                        End If
                            
                        Target.Value = Replace(Target.Value, ";;", ";")
                        Target.Value = Replace(Target.Value, "; ;", ";")
                        If InStr(1, Target.Value, "; ") = 1 Then ' check for ; as first character and remove it
                            Target.Value = Replace(Target.Value, "; ", "", 1, 1)
                        End If
                            
                        If InStr(1, Target.Value, ";") = 1 Then
                            Target.Value = Replace(Target.Value, ";", "", 1, 1)
                        End If
                            
                        semiColonCnt = 0
                        For i = 1 To Len(Target.Value)
                        If InStr(i, Target.Value, ";") Then
                            semiColonCnt = semiColonCnt + 1
                        End If
                            
                        Next i
                        If semiColonCnt = 1 Then ' remove ; if last character
                            Target.Value = Replace(Target.Value, "; ", "")
                            Target.Value = Replace(Target.Value, ";", "")
                        End If
                    End If
                End If
            End If
        Application.EnableEvents = True
        
End Sub

Sequence Information

'Sequence Information

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Rhiannon Cameron 2022/11/28

Select Case True

'Multiselect (trimmed)
Case Not Intersect(ActiveCell, Range("J3:J1522")) Is Nothing

    'Updated by Extendoffice 2019/11/13
    'Updated by Ken Gardner 2022/07/11
    'Modified by Rhiannon Cameron 2022/11/28
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    Dim semiColonCnt As Integer
    If Target.Count > 1 Then Exit Sub
        On Error Resume Next
        Set xRng = Intersect(Target, Range("J3:J1522"))
        If xRng Is Nothing Then Exit Sub
            Application.EnableEvents = False
            'If Not Application.Intersect(Target, xRng) Is Nothing Then
            If Application.Intersect(Target, xRng) Then
                xValue2 = Trim(Target.Value)
                Application.Undo
                xValue1 = Trim(Target.Value)
                Target.Value = xValue2
                If xValue1 <> "" Then
                    If xValue2 <> "" Then
                        If xValue1 = xValue2 Or xValue1 = xValue2 & ";" Or xValue1 = xValue2 & "; " Then ' leave the value if only one in list
                            xValue1 = Replace(xValue1, "; ", "")
                            xValue1 = Replace(xValue1, ";", "")
                            Target.Value = xValue1
                        ElseIf InStr(1, xValue1, "; " & xValue2) Then
                            xValue1 = Replace(xValue1, xValue2, "") ' removes existing value from the list on repeat selection
                            Target.Value = xValue1
                        ElseIf InStr(1, xValue1, xValue2 & ";") Then
                            xValue1 = Replace(xValue1, xValue2, "")
                            Target.Value = xValue1
                        Else
                            Target.Value = xValue1 & "; " & xValue2
                        End If
                            
                        Target.Value = Replace(Target.Value, ";;", ";")
                        Target.Value = Replace(Target.Value, "; ;", ";")
                        If InStr(1, Target.Value, "; ") = 1 Then ' check for ; as first character and remove it
                            Target.Value = Replace(Target.Value, "; ", "", 1, 1)
                        End If
                            
                        If InStr(1, Target.Value, ";") = 1 Then
                            Target.Value = Replace(Target.Value, ";", "", 1, 1)
                        End If
                            
                        semiColonCnt = 0
                        For i = 1 To Len(Target.Value)
                        If InStr(i, Target.Value, ";") Then
                            semiColonCnt = semiColonCnt + 1
                        End If
                            
                        Next i
                        If semiColonCnt = 1 Then ' remove ; if last character
                            Target.Value = Replace(Target.Value, "; ", "")
                            Target.Value = Replace(Target.Value, ";", "")
                        End If
                    End If
                End If
            End If
        Application.EnableEvents = True
        
'Single select (trimmed)
Case Not Intersect(ActiveCell, Range("L:L,M:M")) Is Nothing

    'Updated by Extendoffice 2019/11/13
    'Updated by Ken Gardner 2022/07/11
    'Modified by Rhiannon Cameron 2022/11/28
    Dim xxRng As Range
    Dim xxValue1 As String
    Dim xxValue2 As String
    Dim xsemiColonCnt As Integer
    If Target.Count > 1 Then Exit Sub
        On Error Resume Next
        Set xxRng = Intersect(Target, Range("L3:L1522,M3:M1522"))
        If xxRng Is Nothing Then Exit Sub
            Application.EnableEvents = False
            'If Not Application.Intersect(Target, xRng) Is Nothing Then
            If Application.Intersect(Target, xxRng) Then
                xxValue2 = Trim(Target.Value)
                Application.Undo
                xxValue1 = Trim(Target.Value)
                Target.Value = xxValue2
                If xxValue1 <> "" Then
                    If xxValue2 <> "" Then
                        If xxValue1 = xxValue2 Or xxValue1 = xxValue2 & ";" Or xxValue1 = xxValue2 & "; " Then ' leave the value if only one in list
                            xxValue1 = Replace(xxValue1, "; ", "")
                            xxValue1 = Replace(xxValue1, ";", "")
                            Target.Value = xxValue1
                        ElseIf InStr(1, xxValue1, "; " & xxValue2) Then
                            xxValue1 = Replace(xxValue1, xxValue2, "") ' removes existing value from the list on repeat selection
                            Target.Value = xxValue1
                        ElseIf InStr(1, xxValue1, xxValue2 & ";") Then
                            xxValue1 = Replace(xxValue1, xxValue2, "")
                            Target.Value = xxValue1
                        Else
                            Target.Value = xxValue2 ' replace with new value
                        End If
                            
                        Target.Value = Replace(Target.Value, ";;", ";")
                        Target.Value = Replace(Target.Value, "; ;", ";")
                        If InStr(1, Target.Value, "; ") = 1 Then ' check for ; as first character and remove it
                            Target.Value = Replace(Target.Value, "; ", "", 1, 1)
                        End If
                            
                        If InStr(1, Target.Value, ";") = 1 Then
                            Target.Value = Replace(Target.Value, ";", "", 1, 1)
                        End If
                            
                        xsemiColonCnt = 0
                        For i = 1 To Len(Target.Value)
                        If InStr(i, Target.Value, ";") Then
                            xsemiColonCnt = xsemiColonCnt + 1
                        End If
                            
                        Next i
                        If xsemiColonCnt = 1 Then ' remove ; if last character
                            Target.Value = Replace(Target.Value, "; ", "")
                            Target.Value = Replace(Target.Value, ";", "")
                        End If
                    End If
                End If
            End If
        Application.EnableEvents = True
        
End Select
        
End Sub

AMR Phenotypic Test Information

'AMR Phenotypic Test Information

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case True

'Single select (trimmed)
Case Not Intersect(ActiveCell, Range("J:J,Q:Q,W:W,AF:AF,AO:AO,AX:AX,BG:BG,BP:BP,BY:BY,CH:CH,CQ:CQ,CZ:CZ,DI:DI,DR:DR,EA:EA,EJ:EJ,ES:ES,FB:FB,FK:FK,FT:FT")) Is Nothing

    'Updated by Extendoffice 2019/11/13
    'Updated by Ken Gardner 2022/07/11
    'Modified by Rhiannon Cameron 2022/11/30
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    Dim semiColonCnt As Integer
    If Target.Count > 1 Then Exit Sub
        On Error Resume Next
        Set xRng = Intersect(Target, Range("J3:J1522,Q3:Q1522,W3:W1522,AF3:AF1522,AO3:AO1522,AX3:AX1522,BG3:BG1522,BP3:BP1522,BY3:BY1522,CH3:CH1522,CQ3:CQ1522,CZ3:CZ1522,DI3:DI1522,DR3:DR1522,EA3:EA1522,EJ3:EJ1522,ES3:ES1522,FB3:FB1522,FK3:FK1522,FT3:FT1522"))
        If xRng Is Nothing Then Exit Sub
            Application.EnableEvents = False
            'If Not Application.Intersect(Target, xRng) Is Nothing Then
            If Application.Intersect(Target, xRng) Then
                xValue2 = Trim(Target.Value)
                Application.Undo
                xValue1 = Trim(Target.Value)
                Target.Value = xValue2
                If xValue1 <> "" Then
                    If xValue2 <> "" Then
                        If xValue1 = xValue2 Or xValue1 = xValue2 & ";" Or xValue1 = xValue2 & "; " Then ' leave the value if only one in list
                            xValue1 = Replace(xValue1, "; ", "")
                            xValue1 = Replace(xValue1, ";", "")
                            Target.Value = xValue1
                        ElseIf InStr(1, xValue1, "; " & xValue2) Then
                            xValue1 = Replace(xValue1, xValue2, "") ' removes existing value from the list on repeat selection
                            Target.Value = xValue1
                        ElseIf InStr(1, xValue1, xValue2 & ";") Then
                            xValue1 = Replace(xValue1, xValue2, "")
                            Target.Value = xValue1
                        Else
                            Target.Value = xValue2 ' replace with new value
                        End If
                            
                        Target.Value = Replace(Target.Value, ";;", ";")
                        Target.Value = Replace(Target.Value, "; ;", ";")
                        If InStr(1, Target.Value, "; ") = 1 Then ' check for ; as first character and remove it
                            Target.Value = Replace(Target.Value, "; ", "", 1, 1)
                        End If
                            
                        If InStr(1, Target.Value, ";") = 1 Then
                            Target.Value = Replace(Target.Value, ";", "", 1, 1)
                        End If
                            
                        semiColonCnt = 0
                        For i = 1 To Len(Target.Value)
                        If InStr(i, Target.Value, ";") Then
                            semiColonCnt = semiColonCnt + 1
                        End If
                            
                        Next i
                        If semiColonCnt = 1 Then ' remove ; if last character
                            Target.Value = Replace(Target.Value, "; ", "")
                            Target.Value = Replace(Target.Value, ";", "")
                        End If
                    End If
                End If
            End If
        Application.EnableEvents = True

'Single select (trimmed) - part 2 (didn't work when too many columns indicated)
Case Not Intersect(ActiveCell, Range("GC:GC,GL:GL,GU:GU,HD:HD,HM:HM,HV:HV,IE:IE,IN:IN,IW:IW,JF:JF,JO:JO,JX:JX,KG:KG,KP:KP,KY:KY,LH:LH,LQ:LQ,LZ:LZ,MI:MI")) Is Nothing

    'Updated by Extendoffice 2019/11/13
    'Updated by Ken Gardner 2022/07/11
    'Modified by Rhiannon Cameron 2022/11/28
    Dim xxRng As Range
    Dim xxValue1 As String
    Dim xxValue2 As String
    Dim xsemiColonCnt As Integer
    If Target.Count > 1 Then Exit Sub
        On Error Resume Next
        Set xxRng = Intersect(Target, Range("GC3:GC1522,GL3:GL1522,GU3:GU1522,HD3:HD1522,HM3:HM1522,HV3:HV1522,IE3:IE1522,IN3:IN1522,IW3:IW1522,JF3:JF1522,JO3:JO1522,JX3:JX1522,KG3:KG1522,KP3:KP1522,KY3:KY1522,LH3:LH1522,LQ3:LQ1522,LZ3:LZ1522,MI3:MI1522"))
        If xxRng Is Nothing Then Exit Sub
            Application.EnableEvents = False
            'If Not Application.Intersect(Target, xRng) Is Nothing Then
            If Application.Intersect(Target, xxRng) Then
                xxValue2 = Trim(Target.Value)
                Application.Undo
                xxValue1 = Trim(Target.Value)
                Target.Value = xxValue2
                If xxValue1 <> "" Then
                    If xxValue2 <> "" Then
                        If xxValue1 = xxValue2 Or xxValue1 = xxValue2 & ";" Or xxValue1 = xxValue2 & "; " Then ' leave the value if only one in list
                            xxValue1 = Replace(xxValue1, "; ", "")
                            xxValue1 = Replace(xxValue1, ";", "")
                            Target.Value = xxValue1
                        ElseIf InStr(1, xxValue1, "; " & xxValue2) Then
                            xxValue1 = Replace(xxValue1, xxValue2, "") ' removes existing value from the list on repeat selection
                            Target.Value = xxValue1
                        ElseIf InStr(1, xxValue1, xxValue2 & ";") Then
                            xxValue1 = Replace(xxValue1, xxValue2, "")
                            Target.Value = xxValue1
                        Else
                            Target.Value = xxValue2 ' replace with new value
                        End If
                            
                        Target.Value = Replace(Target.Value, ";;", ";")
                        Target.Value = Replace(Target.Value, "; ;", ";")
                        If InStr(1, Target.Value, "; ") = 1 Then ' check for ; as first character and remove it
                            Target.Value = Replace(Target.Value, "; ", "", 1, 1)
                        End If
                            
                        If InStr(1, Target.Value, ";") = 1 Then
                            Target.Value = Replace(Target.Value, ";", "", 1, 1)
                        End If
                            
                        xsemiColonCnt = 0
                        For i = 1 To Len(Target.Value)
                        If InStr(i, Target.Value, ";") Then
                            xsemiColonCnt = xsemiColonCnt + 1
                        End If
                            
                        Next i
                        If xsemiColonCnt = 1 Then ' remove ; if last character
                            Target.Value = Replace(Target.Value, "; ", "")
                            Target.Value = Replace(Target.Value, ";", "")
                        End If
                    End If
                End If
            End If
        Application.EnableEvents = True


End Select

End Sub
Clone this wiki locally