Create a PowerPoint Fill-in-the-Blank Quiz with VBA and Excel Export
Question details
Create image-based quiz slides that collect typed answers, advance between questions, and export participant responses to Excel.

- Product
- Microsoft PowerPoint and Microsoft Excel
- Device & OS
- not provided
- Scenario
- A quiz creator wants participants to enter answers during a slide show and transfer the results to a spreadsheet.
- Observed behavior
- The desired presentation captures a participant name and answers, associates each answer with a slide, and makes the results available in Excel.
Use desktop PowerPoint with VBA support and save the presentation as a PowerPoint Macro-Enabled Presentation (.pptm). For the Excel export below, use Windows with desktop Excel installed, and confirm that recipients are permitted to run trusted macros.
Build a VBA answer form and export responses to Excel
Use this approach for a desktop PowerPoint quiz when participants can run macros and Excel is available on Windows.
This example records one participant per presentation session. Answers are held in memory until exported; they are not automatically saved in the presentation.
PowerPoint for the web does not run VBA, and organizational security settings may block macros. Avoid slide ActiveX controls if Mac compatibility matters, and test the form on every target platform.
In PowerPoint, add an image and a visible answer button or picture to each question slide. Add a final slide with a separate Export Results button, then use File > Save As to save the presentation as a .pptm file.
Open Developer > Visual Basic, then choose Insert > UserForm. In the Properties window, name the form QuizForm. Add a text box named txtAnswer and a command button named cmdSubmit; set the button caption to Submit.
In the VBA editor, choose Insert > Module and paste this code: Public responses As Collection Public participant As String Sub StartQuizQuestion() If SlideShowWindows.Count = 0 Then MsgBox "Start the slide show before answering." Exit Sub End If If responses Is Nothing Then Set responses = New Collection If participant = "" Then participant = Trim$(InputBox("Enter your name:")) If participant = "" Then Exit Sub End If QuizForm.txtAnswer.Value = "" QuizForm.Show End Sub Sub RecordAnswer(ByVal answer As String) Dim slideNumber As Long slideNumber = SlideShowWindows(1).View.Slide.SlideIndex responses.Add Array(participant, slideNumber, answer) SlideShowWindows(1).View.Next End Sub Sub ExportResults() Dim excelApp As Object, workbook As Object, sheet As Object Dim item As Variant, rowNumber As Long If responses Is Nothing Then MsgBox "There are no responses to export." Exit Sub End If If responses.Count = 0 Then MsgBox "There are no responses to export." Exit Sub End If On Error GoTo ExportError Set excelApp = CreateObject("Excel.Application") excelApp.Visible = True Set workbook = excelApp.Workbooks.Add Set sheet = workbook.Worksheets(1) sheet.Cells(1, 1).Value = "Participant" sheet.Cells(1, 2).Value = "Slide" sheet.Cells(1, 3).Value = "Answer" rowNumber = 2 For Each item In responses sheet.Cells(rowNumber, 1).Value = item(0) sheet.Cells(rowNumber, 2).Value = item(1) sheet.Cells(rowNumber, 3).Value = item(2) rowNumber = rowNumber + 1 Next item MsgBox "Results are open in Excel. Use File > Save As to save them." Exit Sub ExportError: MsgBox "Could not export to Excel: " & Err.Description End Sub
In the VBA editor, double-click cmdSubmit on QuizForm and add this event code: Private Sub cmdSubmit_Click() If Trim$(Me.txtAnswer.Value) = "" Then MsgBox "Enter an answer before submitting." Exit Sub End If RecordAnswer Me.txtAnswer.Value Unload Me End Sub
In PowerPoint, select each answer picture or button and use Insert > Action > Run macro to assign StartQuizQuestion. Assign ExportResults to the final slide's Export Results button. Start Slide Show, submit test answers, select Export Results, and use Excel's File > Save As to save the workbook as .xlsx.

Create quiz slides and organize answers with WPS Office
WPS Office is a free, lightweight alternative with a familiar interface for editing quiz slides and spreadsheets. Open compatible PowerPoint and Excel files for an easier transition; the PowerPoint-specific VBA form and Excel automation above still require a compatible Microsoft Office environment.

Frequently Asked Questions
Can participants take the VBA quiz in PowerPoint for the web?
No. PowerPoint for the web does not run VBA macros. Participants need a compatible desktop PowerPoint environment where trusted macros are permitted.
Why does the quiz need to be saved as a PPTM file?
The .pptm format preserves VBA code. In PowerPoint, choose File > Save As and select PowerPoint Macro-Enabled Presentation.
Where are answers stored before export?
The example stores the participant name, slide number, and answer in a VBA collection in memory. Use the Export Results button and save the workbook in Excel before the session ends.
Does the example automatically save an Excel file?
No. Export Results opens a workbook in desktop Excel on Windows. Choose File > Save As in Excel and save it as an .xlsx file.
Will the quiz work on a Mac?
Compatibility depends on the PowerPoint version and the VBA features used. Avoid slide ActiveX controls and test the form on the target Mac. The Excel automation in this example is intended for Windows.




