I have made a form with Microsoft Forms and in the same Excel file, I have a macro which triggers every time there is a change in eg. A2-A20.
So my goal is to get the macro triggered every time someone submit and the data comes in.
But it seems like that the macro won't get triggered, when someone submit the form and the data gets added to the Excel file.
The macro works fine when I write something in one of the cells myself.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("A2:A20")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
'Get name for new workbook
Dim WBnew As Variant
WBnew = Range("E" & Target.Row).Value
' Creates a reference to workbook object
Dim WB As Workbook
'Adding a New Workbook
Set WB = Workbooks.Add
'Set where to save the Workbook and name
ActiveWorkbook.SaveAs Filename:="C:\Users\" & WBnew & ".xls"
'Copy entire row
Workbooks("Test forms.xlsm").Worksheets("Form1").Range(Target.Row & ":" & Target.Row).Copy
'Paste to row 2 in the new workbook
Workbooks(WBnew & ".xls").Worksheets("Sheet1").Range("1:1").Insert
'Copy row 1 in old workbook and paste it to the new workbooks row 1
Workbooks("Test forms.xlsm").Worksheets("Form1").Range("1:1").Copy
Workbooks(WBnew & ".xls").Worksheets("Sheet1").Range("1:1").Insert
'Save the workbook
ActiveWorkbook.Save
End If
End Sub
Any idea what to do, so the macro will get triggered, when new data comes in from the form?
Thanks in advance!