VBA - Compile Error - Method or Data member not Found

Question

I have been using this excel program for several months without issues. suddenly a couple days ago it started to throw this error. On sheet named "Input" I will double click a cell in column "A" which will create a drop down box that will fill with data from the "Data" sheet. I start typing and then I select the data to add to the cell. Now when I click the cell and get an error message "Compile Error - Method or data member not found". Here is my block of code and the error is showing near the bottom highlighting "Me.TempCombo.Activate".

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim str As String
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    Set ws = ActiveSheet

    If Target.Column = 1 And Target.Row > 12 And Target.Row <> HRRow And Target.Row <> HRRow - 1 Then

        lRow = Sheets("Data").Range("A65536").End(xlUp).Row
        Set cboTemp = ws.OLEObjects("TempCombo")
          On Error Resume Next

          With cboTemp
          'clear and hide the combo box
            .ListFillRange = ""
            .LinkedCell = ""
            .Visible = False
          End With
        On Error GoTo errHandler
          'If Target.Validation.Type = 3 Then
            'if the cell contains a data validation list
            Cancel = True
            Application.EnableEvents = False
            'get the data validation formula
            'str = Target.Validation.Formula1
            'str = Right(str, Len(str) - 1)
            str = "=Data!A2:A" & lRow

            With cboTemp
              'show the combobox with the list
              .Visible = True
              .Left = Target.Left
              .Top = Target.Top
              .Width = Target.Width + 5
              .Height = Target.Height + 5
              .ListFillRange = str
              .LinkedCell = Target.Address
            End With
            'cboTemp.Activate
            Me.TempCombo.Activate
            'open the drop down list automatically
            Me.TempCombo.DropDown

      End If

errHandler:
      Application.EnableEvents = True
      Exit Sub
End Sub

I tried several things and for the life of me I cannot figure out what changed. Any help will be appreciated. Thank you.

Can you manually click on the combobox? A recent MS update broke a lot of ActiveX controls so, if you can't do it manually, delete all *.exd files from your computer (with all Office apps closed) and then try again. — Rory, Dec 10 '14 at 15:51
That appeared to have worked. I was wondering if perhaps I got some malware. My scans are clear but I see that a windows update did occur around the time this issue arose. Thanks — Scott, Dec 12 '14 at 19:45

score 6 · Answer 1 · answered Dec 29 '14 at 21:13

I ran into the same error and was able to solve it as Rory suggested. I searched my machine for *.exd files and found a few. The issue was solved for me after removing C:\Users\<username>\AppData\Local\Temp\Excel8.0\MSForms.exd...the others seemed to be unrelated to the ActiveX controls in Excel.

score 2 · Answer 2 · answered Dec 10 '14 at 14:39

Looks like the code came from an example like this: http://www.contextures.com/xlDataVal10.html

except your code has commented out the line which activates the cboTemp combobox. Your code is attempting to access the TempCombo attribute of the worksheet (which I don't think exists). Uncomment 'cboTemp.Activate on the line above the highlighted error line.

score 0 · Answer 3 · answered Dec 15 '14 at 19:18

I had the same problem, my code broke this morning. Fortunately, I recalled that I ran Windows Update this weekend. I performend a system restore (earliest available restore point was 8th of december), and now the problem is gone.

I never did understand the panicy server guys who were always making backups and spending a whole lot of time testing before/after system updates, in all my years I never experienced any problems. Now I sure figured out what they were talking about. Lesson learnt. I'll try running win update again in a few months, hopefully MS has solved the problem by then.

Best of luck

I am in the same boat. It seems like the December 9th Windows update has caused hell for a lot of guys. That ended up being the source of my issues. — Scott, Dec 18 '14 at 00:29

VBA - Compile Error - Method or Data member not Found

3 Answers3