Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-28-2016, 09:41 PM
IIOII IIOII is offline VBA failing when updaing TextBox in PowerPoint Windows 10 VBA failing when updaing TextBox in PowerPoint Office 2010 32bit
Novice
VBA failing when updaing TextBox in PowerPoint
 
Join Date: Jul 2016
Posts: 6
IIOII is on a distinguished road
Cool VBA failing when updaing TextBox in PowerPoint


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
Reply With Quote
  #2  
Old 07-29-2016, 04:56 PM
IIOII IIOII is offline VBA failing when updaing TextBox in PowerPoint Windows 10 VBA failing when updaing TextBox in PowerPoint Office 2010 32bit
Novice
VBA failing when updaing TextBox in PowerPoint
 
Join Date: Jul 2016
Posts: 6
IIOII is on a distinguished road
Default

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
Reply With Quote
Reply



Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA failing when updaing TextBox in PowerPoint Powerpoint VBA to remove/replace/delete certain colored font in textbox and in tables lifelessons PowerPoint 3 06-24-2016 12:17 AM
VBA failing when updaing TextBox in PowerPoint Create e-mail copying Powerpoint textbox and send it automatically Ochimus PowerPoint 6 06-03-2015 08:41 AM
VBA failing when updaing TextBox in PowerPoint 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

Other Forums: Access Forums

All times are GMT -7. The time now is 11:47 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft