Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Compile Error: Method or data member not found

  1. #1
    New Member
    Join Date
    Jan 2014
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Compile Error: Method or data member not found

    This code worked perfectly in my workbook for nearly a year and then seemingly for no reason this error comes up on each worksheet and on different computers. I haven't made any recent VBA changes and don't see anything different. Red lines are where the error was flagged. Any ideas? Thanks!


    Private Sub HVACCombo_Change()


    End Sub


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


    Set cboTemp = ws.OLEObjects("HVACCombo")
    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)
    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
    'open the drop down list automatically
    Me.HVACCombo.DropDown


    End If

    errHandler:
    Application.EnableEvents = True
    Exit Sub
    End Sub
    '=========================================
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim str As String
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Application.EnableEvents = False
    Application.ScreenUpdating = True


    If Application.CutCopyMode Then
    'allow copying and pasting on the worksheet
    GoTo errHandler
    End If


    Set cboTemp = ws.OLEObjects("HVACCombo")
    On Error Resume Next
    With cboTemp
    .Top = 10
    .Left = 10
    .Width = 0
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Value = ""
    End With


    errHandler:
    Application.EnableEvents = True
    Exit Sub
    End Sub

  2. #2
    New Member
    Join Date
    Jan 2014
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compile Error: Method or data member not found

    I should mention that I get the same error on previously saved workbooks where it worked fine.

  3. #3
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    4,502
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compile Error: Method or data member not found

    Maybe it has to do with the Windows update temp files, use a disk cleaning program (e.g. CCleaner) to delete all temp files, or manually search for *.exd files and delete them.
    Last edited by jtakw; Dec 11th, 2014 at 07:33 PM.

  4. #4
    New Member
    Join Date
    Jan 2014
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compile Error: Method or data member not found

    I didn't find any .exd files. This issue has cropped up on two different computers at the same time and I'll check from a 3rd tomorrow.
    Is it possible that my reference options were altered accidentally?

  5. #5
    New Member
    Join Date
    Jan 2014
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compile Error: Method or data member not found

    In trying to recreate the sheet, I discovered that trying to insert an activeX combo box brings up the following error: "Cannot insert object"

    Not sure how this is related, but am pretty sure it is.

    I have not tried the disk cleaning program - hesitant to download and wondering if the free version will suffice.

    Thanks for any help.

  6. #6
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)
    Microsoft MVP - Excel

  7. #7
    New Member
    Join Date
    Jan 2014
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compile Error: Method or data member not found

    Got it.

    Removing the .exd files fixed the issue on both machines. I just had to find them by "including hidden and system files and folders" in my C Drive search.

    Thanks, I very grateful!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •