-
Notifications
You must be signed in to change notification settings - Fork 3
Macros Troubleshooting
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).
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.
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".
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/
Copy of the VBA code in the Excel Template for users to reference to confirm it is safe.
Updated: 2023-07-06
'SRA_data
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Rhiannon Cameron 2022/12/12
'Multiselect (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
'quality_control_determination, quality_control_issues
Set xRng = Intersect(Target, Range("U1:U1500,V1:V1500"))
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
End Sub