User form in PowerPoint to feed Excel database

[pcowner]

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]

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.

' 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

' standard module
Public oEH As New clsAppEvents


Sub InitApp()
  Set oEH.App = Application
End Sub

' 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]

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.

' 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

' standard module
Public oEH As New clsAppEvents


Sub InitApp()
  Set oEH.App = Application
End Sub

' 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]

The following example shows how to require a registration:

' 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

' standard module
Public oEH As New Class1, registered As Boolean


Sub InitApp()
  Set oEH.App = Application
  registered = False
End Sub

' class module named Class1
Public WithEvents App As Application


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

 

[Worf]

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]

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]

• 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]

Yes, please would you share your draft?

 

[pcowner]

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]

• 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