CODEWITHSUNDEEP
excelvbams-wordactivex
Jessica Marie
Jessica Marie

Reputation: 189

Compile Error: Method or data member not found, VBA

I am using a ActiveX Command Button in Microsoft Word to do the following tasks:

  1. Add ActiveX labels to a series of tables
  2. Rename the inserted ActiveX labels
  3. Populate the captions of the ActiveX labels with data from an Excel workbook

I am renaming the ActiveX labels to "FY" & seq where seq is a sequence of numbers. For example, a renamed label could be "FY1". In Step 3 my code uses "FY1" (ThisDocument.FY1.Caption = rw.Cells(1).Value) but the code will not run and I receive a Compile Error: Method or data member not found message.

Do I have to call a new Sub for my labels to be recognized? Is there a way for the whole code to be ran with one click of an ActiveX button? 

Private Sub CommandButton1_Click()

Dim objExcel As Excel.Application
Dim exWb As Excel.Workbook
Dim rng As Excel.Range, m, rw As Excel.Range
Dim num As Integer
Dim TableNo As Integer
Dim seq As Integer
Dim ctl As MSForms.Label
Dim ils As Word.InlineShape

Set objExcel = New Excel.Application
Set exWb = objExcel.Workbooks.Open("O:\Documents\Database.csv")
Set rng = exWb.Sheets("FY1819_DatabaseExtracted").Cells
TableNo = ActiveDocument.Tables.Count
num = 3
seq = 1

'' Now, create all FY labels
Do
    Set ils = ActiveDocument.Tables(num).cell(6, 2).Range.InlineShapes.AddOLEControl(ClassType:="Forms.Label.1")
    Set ctl = ils.OLEFormat.Object
    ctl.Name = "FY" & seq
    seq = seq + 1
    num = num + 1
Loop Until num = TableNo + 1

'''' Match to Excel Database
m = objExcel.Match(ThisDocument.Code1.Caption, rng.Columns(3), 0)

If Not IsError(m) Then
    Set rw = rng.Rows(m) '<< get the matching row as a Range
    ThisDocument.FY1.Caption = rw.Cells(1).Value 'value from colA
End If

Set exWb = Nothing

End Sub

My error occurs at ThisDocument.FY1.Caption = rw.Cells(1).Value. The 'FY1' isn't recognized.

Upvotes: 0

Views: 3020

Answers (1)

Tim Williams
Tim Williams

Reputation: 166366

Here's a simplified example of how to change your code:

Private Sub CommandButton1_Click()

    Dim obj, ctl, ils As Word.InlineShape

    'add a control and set its name          
    Set ils = ActiveDocument.Tables(1).Cell(1, 1).Range. _
                  InlineShapes.AddOLEControl(ClassType:="Forms.Label.1")
    Set ctl = ils.OLEFormat.Object
    ctl.Name = "FY1"
    'consider setting the caption here?


    'ThisDocument.FY1.Caption = "Hello" '<< will not compile

    'alternate approach
    Set obj = GetControl(ActiveDocument, "FY1")
    If Not obj Is Nothing Then
        obj.Caption = "Hello"
    End If

End Sub

'get a control by name
Function GetControl(doc As Document, conName As String) As Object
    Dim rv As Object, obj
    For Each obj In doc.InlineShapes
        If obj.OLEFormat.Object.Name = conName Then
            Set rv = obj.OLEFormat.Object
            Exit For
        End If
    Next obj
    Set GetControl = rv
End Function

Upvotes: 1

Related Questions