VBA failing when updaing TextBox in PowerPoint

IIOII · #1 07-28-2016, 09:41 PM

Greetings. The intent of the below code is to access a range within an Excel spreadsheet, find a value and then update an ActiveX TextBox within a PowerPoint slide with the value and then change the colour. The codes works well when there are no other shapes or tables on the slide. However when there are, it's throwing the run time error -2147188160 (80048240): OLEFormat (unknown member): Invalid Request. This property only applies to OLE objects.

Any suggestions on how to best skip all non-required shapes or tables except the required TextBox's? Sample files attached below.

Code:

Private Sub CommandButton1_Click()

Dim objExcel As Object
Dim exWb As Object
Dim rng As Object
Dim c As Object
Dim TaskStatus As String
Dim oTB As PowerPoint.Shape

    Set objExcel = CreateObject("Excel.Application")
    Set exWb = objExcel.Workbooks.Open("C:\Temp\SAMPLE.xlsm")
    Set rng = exWb.Sheets("Data").Range("TEEE")

    For Each c In rng.Cells
        TaskStatus = c.Offset(0, 1)
        MsgBox TaskStatus
        
        For Each oTB In ActivePresentation.Slides(1).Shapes
            If oTB.OLEFormat.Object.Name = c Then
                oTB.OLEFormat.Object.Text = TaskStatus
                If TaskStatus = "B" Then oTB.OLEFormat.Object.BackColor = RGB(0, 0, 255)
                If TaskStatus = "R" Then oTB.OLEFormat.Object.BackColor = RGB(255, 0, 0)
                If TaskStatus = "A" Then oTB.OLEFormat.Object.BackColor = RGB(255, 204, 0)
                If TaskStatus = "G" Then oTB.OLEFormat.Object.BackColor = RGB(0, 255, 0)
                Exit For
            End If

        Next
    Next

    exWb.Close
    objExcel.Quit

    Set exWb = Nothing
    Set objExcel = Nothing
    Set rng = Nothing
    Set c = Nothing
    Set oTB = Nothing

End Sub

PP_SAMPLE.pptm

SAMPLE.xlsm

IIOII · #2 07-29-2016, 04:56 PM

Resolved. You have to check whether the shape is an OLE control:

Code:

For Each oTB In ActivePresentation.Slides(1).Shapes
            If oTB.Type = msoOLEControlObject Then
                If oTB.OLEFormat.Object.Name = c Then
                    oTB.OLEFormat.Object.Text = TaskStatus
                    If TaskStatus = "B" Then oTB.OLEFormat.Object.BackColor = RGB(0, 0, 255)
                    If TaskStatus = "R" Then oTB.OLEFormat.Object.BackColor = RGB(255, 0, 0)
                    If TaskStatus = "A" Then oTB.OLEFormat.Object.BackColor = RGB(255, 204, 0)
                    If TaskStatus = "G" Then oTB.OLEFormat.Object.BackColor = RGB(0, 255, 0)
                    Exit For
                End If
            End If
        Next oTB

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Powerpoint VBA to remove/replace/delete certain colored font in textbox and in tables	lifelessons	PowerPoint	3	06-24-2016 12:17 AM
Create e-mail copying Powerpoint textbox and send it automatically	Ochimus	PowerPoint	6	06-03-2015 08:41 AM
Display result in textbox based on the input of another textbox	scarymovie	Word VBA	5	05-16-2012 07:05 PM
PowerPoint 2007- Reapply Master to Notes Page but Not to the Text within the Textbox/	mejasmine	PowerPoint	0	01-26-2012 07:39 AM
PowerPoint 2007 Textbox Lock / Form Issues	LTechie12	PowerPoint	0	01-08-2012 02:08 PM