Get macro triggered every time a Microsoft form is submitted

674 views Asked by At

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!

0

There are 0 answers