User form in PowerPoint to feed Excel database

pcowner

New Member
I'd created a presentation/tutorial in MS PowerPoint, run it in a terminal mode, where users navigate through vba coded buttons. Now, I'm figuring a way to capture who views the PowerPoint (also, trying to capture their feedback). Could anyone please help with a vba code that could be tied to a userform in the beginning or the end of the presentation that would allow users to fill and submit the feedback/registrations? Could an embedded Excel w/VBA help with this?

Additional background info... as an admin, I upload 1 PP slide and it propagates to our public desktops in our classroom. Desktops are locked by the IT and there is no email client installed.
 

Worf

Well-known Member
Welcome to the Board


  • This is Power Point example code; run the InitApp routine first to enable events.
  • A user form will pop up at the last slide, and the inputted data is transferred to a workbook.
  • I am assuming the workbook is open on your machine, not embedded in the presentation.
  • As I tested on my computer, you need to find out how this will behave on the network.

Code:
' class module named clsAppEvents
Public WithEvents App As Application


Private Sub App_SlideShowNextSlide(ByVal Wn As SlideShowWindow)
If ActivePresentation.SlideShowWindow.View.Slide.SlideIndex = _
ActivePresentation.Slides.Count Then UserForm1.Show
End Sub
Code:
' standard module
Public oEH As New clsAppEvents


Sub InitApp()
  Set oEH.App = Application
End Sub
Code:
' PowerPoint UserForm module
Private Sub CommandButton1_Click()
Dim xap, wb As Workbook, i%, ws As Worksheet, lr%
Set xap = GetObject(, "Excel.Application")
For i = 1 To xap.Workbooks.Count                    ' find workbook
    If xap.Workbooks(i).Name Like "*Before*" Then Set wb = xap.Workbooks(i)
Next
Set ws = wb.Worksheets(1)
lr = ws.Range("a" & ws.Rows.Count).End(xlUp).Row + 1
ws.Cells(lr, 1) = Me.OptionButton1                  ' transfer data
ws.Cells(lr, 2) = Me.OptionButton2
ws.Cells(lr, 3) = Me.TextBox1
End Sub
 

pcowner

New Member
Welcome to the Board


  • This is Power Point example code; run the InitApp routine first to enable events.
  • A user form will pop up at the last slide, and the inputted data is transferred to a workbook.
  • I am assuming the workbook is open on your machine, not embedded in the presentation.
  • As I tested on my computer, you need to find out how this will behave on the network.

Code:
' class module named clsAppEvents
Public WithEvents App As Application


Private Sub App_SlideShowNextSlide(ByVal Wn As SlideShowWindow)
If ActivePresentation.SlideShowWindow.View.Slide.SlideIndex = _
ActivePresentation.Slides.Count Then UserForm1.Show
End Sub
Code:
' standard module
Public oEH As New clsAppEvents


Sub InitApp()
  Set oEH.App = Application
End Sub
Code:
' PowerPoint UserForm module
Private Sub CommandButton1_Click()
Dim xap, wb As Workbook, i%, ws As Worksheet, lr%
Set xap = GetObject(, "Excel.Application")
For i = 1 To xap.Workbooks.Count                    ' find workbook
    If xap.Workbooks(i).Name Like "*Before*" Then Set wb = xap.Workbooks(i)
Next
Set ws = wb.Worksheets(1)
lr = ws.Range("a" & ws.Rows.Count).End(xlUp).Row + 1
ws.Cells(lr, 1) = Me.OptionButton1                  ' transfer data
ws.Cells(lr, 2) = Me.OptionButton2
ws.Cells(lr, 3) = Me.TextBox1
End Sub
Perfect, thank you so very much. I'd struggled to get it all wrapped up together because I found excel spreadsheet that allow for tracking (inventory), but couldn't make it work in PowerPoint as an embedded spreadsheet.


Would your routine work in the beginning of the presentation, for example to move forward the presentation after a user registered and a user couldn't bypass the registration to proceed?

You mentioned the spreadsheet is open. Does it have to be open or it could be a file on a desktop? Also, I'm assuming the excel tracking is the only way to capture PowerPoint viewers as email alerts wouldn't work because there is no standalone mail providers, like Outlook? Our public computers do not have Outlook installed.

Sincerely thank you for your help.
 

Worf

Well-known Member
The following example shows how to require a registration:

Code:
' PowerPoint UserForm module
Private Sub CommandButton1_Click()
Dim xap, wb As Workbook, i%, ws As Worksheet, lr%
If (Me.OptionButton1 Or Me.OptionButton2) And Len(Me.TextBox1) Then
    Set xap = GetObject(, "Excel.Application")
    For i = 1 To xap.Workbooks.Count                    ' find workbook
        If xap.Workbooks(i).Name Like "*Before*" Then Set wb = xap.Workbooks(i)
    Next
    Set ws = wb.Worksheets(1)
    lr = ws.Range("a" & ws.Rows.Count).End(xlUp).Row + 1
    ws.Cells(lr, 1) = Me.OptionButton1                  ' transfer data
    ws.Cells(lr, 2) = Me.OptionButton2
    ws.Cells(lr, 3) = Me.TextBox1
    registered = True
    MsgBox "Registered."
    Me.Hide
Else
    MsgBox "Please complete all fields."
End If
End Sub


Private Sub UserForm_Terminate()
If Not registered Then
    MsgBox "you have to register"
    ActivePresentation.SlideShowWindow.View.Exit
End If
End Sub
Code:
' standard module
Public oEH As New Class1, registered As Boolean


Sub InitApp()
  Set oEH.App = Application
  registered = False
End Sub
Code:
' class module named Class1
Public WithEvents App As Application


Private Sub App_SlideShowBegin(ByVal Wn As SlideShowWindow)
UserForm1.Show
End Sub
 

Worf

Well-known Member
Does it have to be open or it could be a file on a desktop?
Do you mean on the user’s desktop? The code can open a file and write the data. It could be a workbook, a Word document or a plain text file.
 

pcowner

New Member
Do you mean on the user’s desktop? The code can open a file and write the data. It could be a workbook, a Word document or a plain text file.
Yes, on the user's desktop. You mentioned that the code can open a file and write a data. How would I specify if the code will create a document or update an existing document? And, how would I specify the type of a program to use, such Word, Excel or a notepad?



I'm trying to apply your code, which I greatly appreciate you helping out in creating, to my slide 1 VBA screen I get an error: "Ambiguous name detected: CommandButton1_Click." I entered the code into Slide 1 VBA ode window, not a module.

Also, how do I "run the InitApp routine first to enable events" ?
 

Worf

Well-known Member
  • The choice to either create a document or update one is yours. It can be decided beforehand or at runtime, based on some condition.
  • Same thing for the file format, although I do not see why we should choose a program at runtime.
  • The ambiguous name error happens when there are two routines with the same name.
  • You should place the code pieces where I indicated. Would you like a link to my test presentation?
  • The Init App procedure can be executed by pressing Alt+F8 and choosing from the list, before starting the slide show, or called from other routine, for example when clicking a shape at the presentation beginning.
  • What Office version are you using?
 

pcowner

New Member
I have the presentation running in a kiosk mode. The first page has a macro button that says: "Click to continue to the next page." A user navigates the slides through these macro buttons back and forth. We're using Excel 2013.

I could work to tie the Init App procedure to the macro coded button on the first slide that says: "Click to continue to the next page."
 

Worf

Well-known Member
  • See below a link to my test presentation for the registration procedure. Note that it expects to find a workbook already open and with a certain name. This can be changed if you wish.
  • The button you described looks ideal to run the initial code. We may need to trigger the user form to appear at the second slide.

https://www.dropbox.com/s/gjkiglayfv81384/pres.pptm?dl=0
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top