Excel - Compile Error: Method or data memeber not found

[KellyMaurine]

I have a worksheet with the following Macro to auto complete pull-downs. Every few months I get the error "Compile Error: Method or data member not found" and have to spend time recreating the file.

How can I prevent this error from happening again?


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim xStr As String
Dim xCombox As OLEObject
Dim xWs As Worksheet
Set xWs = Application.ActiveSheet
On Error Resume Next
Application.EnableEvents = False
Set xCombox = xWs.OLEObjects("TempCombo")
With xCombox
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
If Target.Validation.Type = 3 Then
Cancel = True
xStr = Target.Validation.Formula1
xStr = Right(xStr, Len(xStr) - 1)
With xCombox
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = xStr
.LinkedCell = Target.Address
End With
xCombox.Activate
Me.TempCombo.DropDown
End If
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim xCombox As OLEObject
Dim xWs As Worksheet
Set xWs = Application.ActiveSheet
On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = True
Set xCombox = xWs.OLEObjects("TempCombo")
With xCombox
.Top = 10
.Left = 10
.Width = 0
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
Application.EnableEvents = True
End Sub
Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case 9
Application.ActiveCell.Offset(0, 1).Activate
Case 13
Application.ActiveCell.Offset(1, 0).Activate
End Select
End Sub