![]() |
#1
|
|||
|
|||
![]() 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 SAMPLE.xlsm |
#2
|
|||
|
|||
![]()
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 | Thread Starter | Forum | Replies | Last Post |
![]() |
lifelessons | PowerPoint | 3 | 06-24-2016 12:17 AM |
![]() |
Ochimus | PowerPoint | 6 | 06-03-2015 08:41 AM |
![]() |
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 |