refresh textbox when powerpoint slide is shown

Question

I'm using PowerPoint 2016 for a Wallboard display and I'd like it to pull a number from an MSSQL-Server table. I can get the SQL data into Powerpoint easily enough but I would like the data to refresh every day automatically and leave the wallboard running continuously.

I have a textbox on a slide that pulls data from an SQL VBA script. Is there a way to automatically run the script each time the slide is shown while the presentation is running or have the script run once every 24 hours to refresh the textbox?

PeterT PeterT · Accepted Answer · 2019-02-13T20:01:49

This is a tricky and interesting problem and I'll admit that this is most of the solution, but there is a missing part. I haven't done much programming in Powerpoint, so it was a challenge. When you open the VBE, there are no modules, classes, or objects of any kind available. This is quite different from Word and Excel. This means we're all on our own...

I created a Presentation with five slides. On the third slide, I inserted some objects so it looked like this:

For the VBA part, the first thing I had to learn was to create a class module that will catch all of the events for the presentation. My very simple class is called EventClassModule and looks like this:

Option Explicit

Public WithEvents App As Application

Private Sub App_SlideShowNextSlide(ByVal Wn As SlideShowWindow)
    If Wn.View.CurrentShowPosition = 3 Then
        UpdateTheCount Wn.View.Slide
    End If
End Sub

So now in a regular module, you have to run something to create and initialize this class as a global object.

Option Explicit

Dim eventClass As EventClassModule

Sub InitializeApp()
    Set eventClass = New EventClassModule
    Set eventClass.App = Application
End Sub

In my testing, I just manually ran the InitializeApp procedure to create the global object. This is the missing part of my solution... - I don't know how to automatically run this initialization to create the object. Maybe a ribbon button?

Then, also in the regular code module is the procedure to update the textbox:

Public Sub UpdateTheCount(ByRef thisSlide As Slide)
    '--- loop through all the shapes to find the correct textbox,
    '    then update the value for display
    Const LABEL_TEXT As String = "Value to update:"
    Dim shp As Shape
    For Each shp In thisSlide.Shapes
        If shp.Type = msoTextBox Then
            Dim theText As String
            theText = shp.TextFrame.TextRange.Text
            If InStr(1, theText, LABEL_TEXT, vbTextCompare) = 1 Then
                Dim colonPos As Long
                Dim theValue As Long
                colonPos = InStr(1, theText, ":", vbTextCompare)
                theValue = CLng(Right$(theText, Len(theText) - colonPos))
                theValue = theValue + 1
                theText = LABEL_TEXT & " " & theValue
                shp.TextFrame.TextRange.Text = theText
            End If
        End If
    Next shp
End Sub

Once your code is in place and you've run the InitializeApp() sub manually, just start the slide show (and probably set it to loop from the end) and step through it. The textbox value will update and increment automatically.

I'm interested in learning how to kick this off automatically from someone who's got that experience.

refresh textbox when powerpoint slide is shown

1 Answers