Compile Error: Method or data member not found

steveblaha

New Member
Joined
Jan 11, 2014
Messages
8
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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:
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top