Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: User form in PowerPoint to feed Excel database

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default User form in PowerPoint to feed Excel database

    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.

  2. #2
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,742
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    2 Thread(s)

    Default Re: User form in PowerPoint to feed Excel database

    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
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: User form in PowerPoint to feed Excel database

    Quote Originally Posted by Worf View Post
    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.

  4. #4
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,742
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    2 Thread(s)

    Default Re: User form in PowerPoint to feed Excel database

    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
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


  5. #5
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,742
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    2 Thread(s)

    Default Re: User form in PowerPoint to feed Excel database

    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.
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


  6. #6
    New Member
    Join Date
    Aug 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: User form in PowerPoint to feed Excel database

    Quote Originally Posted by Worf View Post
    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" ?

  7. #7
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,742
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    2 Thread(s)

    Default Re: User form in PowerPoint to feed Excel database

    • 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?
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


  8. #8
    New Member
    Join Date
    Aug 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: User form in PowerPoint to feed Excel database

    Yes, please would you share your draft?

  9. #9
    New Member
    Join Date
    Aug 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: User form in PowerPoint to feed Excel database

    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."

  10. #10
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,742
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    2 Thread(s)

    Default Re: User form in PowerPoint to feed Excel database

    • 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/gjkiglayfv...pres.pptm?dl=0
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •