compile error method or data member not found

%3CLINGO-SUB%20id%3D%22lingo-sub-2074053%22%20slang%3D%22en-US%22%3Ecompile%20error%20method%20or%20data%20member%20not%20found%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2074053%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUsing%20my%20work%20computer%20to%20open%20a%20macro%20enabled%20worksheet%20and%20im%20unable%20to%20type.%20i%20have%20not%20changed%20anything%20or%20touched%20anything%20and%20i%20am%20a%20total%20noob%20when%20it%20comes%20to%20stuff%20like%20this.%20This%20is%20what%20i%20am%20getting%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20Worksheet_SelectionChange(ByVal%20Target%20As%20Range)%3CBR%20%2F%3E'Update%20by%20Extendoffice%3A%202018%2F9%2F21%3CBR%20%2F%3EDim%20xCombox%20As%20OLEObject%3CBR%20%2F%3EDim%20xStr%20As%20String%3CBR%20%2F%3EDim%20xWs%20As%20Worksheet%3CBR%20%2F%3EDim%20xArr%3CBR%20%2F%3E%3CBR%20%2F%3ESet%20xWs%20%3D%20Application.ActiveSheet%3CBR%20%2F%3EOn%20Error%20Resume%20Next%3CBR%20%2F%3ESet%20xCombox%20%3D%20xWs.OLEObjects(%22TempCombo%22)%3CBR%20%2F%3EWith%20xCombox%3CBR%20%2F%3E.ListFillRange%20%3D%20%22%22%3CBR%20%2F%3E.LinkedCell%20%3D%20%22%22%3CBR%20%2F%3E.Visible%20%3D%20False%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3EIf%20Target.Validation.Type%20%3D%203%20Then%3CBR%20%2F%3ETarget.Validation.InCellDropdown%20%3D%20False%3CBR%20%2F%3ECancel%20%3D%20True%3CBR%20%2F%3ExStr%20%3D%20Target.Validation.Formula1%3CBR%20%2F%3ExStr%20%3D%20Right(xStr%2C%20Len(xStr)%20-%201)%3CBR%20%2F%3EIf%20xStr%20%3D%20%22%22%20Then%20Exit%20Sub%3CBR%20%2F%3EWith%20xCombox%3CBR%20%2F%3E.Visible%20%3D%20True%3CBR%20%2F%3E.Left%20%3D%20Target.Left%3CBR%20%2F%3E.Top%20%3D%20Target.Top%3CBR%20%2F%3E.Width%20%3D%20Target.Width%20%2B%205%3CBR%20%2F%3E.Height%20%3D%20Target.Height%20%2B%205%3CBR%20%2F%3E.ListFillRange%20%3D%20xStr%3CBR%20%2F%3EIf%20.ListFillRange%20%3D%20%22%22%20Then%3CBR%20%2F%3ExArr%20%3D%20Split(xStr%2C%20%22%2C%22)%3CBR%20%2F%3EMe.TempCombo.List.xArr%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E.LinkedCell%20%3D%20Target.Address%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3ExCombox.Activate%3CBR%20%2F%3EMe.TempCombo.DropDown%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20Sub%3CBR%20%2F%3EPrivate%20Sub%20TempCombo_KeyDown(ByVal%20KeyCode%20As%20MSForms.ReturnInteger%2C%20ByVal%20Shift%20As%20Integer)%3CBR%20%2F%3ESelect%20Case%20KeyCode%3CBR%20%2F%3ECase%209%3CBR%20%2F%3EApplication.ActiveCell.Offset(0%2C%201).Activate%3CBR%20%2F%3ECase%2013%3CBR%20%2F%3EApplication.ActiveCell.Offset(1%2C%200).Activate%3CBR%20%2F%3EEnd%20Select%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETempcombo%20is%20highlighted.%20can%20anyone%20help%3F!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2074053%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2074068%22%20slang%3D%22en-US%22%3ERe%3A%20compile%20error%20method%20or%20data%20member%20not%20found%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2074068%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F934455%22%20target%3D%22_blank%22%3E%40smg198811200%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ExWs.OLEObjects(%22TempCombo%22)%20refers%20to%20an%20ActiveX%20combo%20box%20named%20TempCombo.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOn%20the%20Home%20tab%20of%20the%20ribbon%2C%20in%20the%20Editing%20group%2C%20select%20Find%20%26amp%3B%20Select%20%26gt%3B%20Selection%20Pane.%3C%2FP%3E%0A%3CP%3EYou%20should%20see%20a%20task%20pane%20on%20the%20right%20hand%20side%20of%20the%20window%20that%20lists%20all%20objects%20(shapes)%20on%20the%20worksheet.%20Do%20you%20see%20TempCombo%20in%20this%20pane%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,

 

Using my work computer to open a macro enabled worksheet and im unable to type. i have not changed anything or touched anything and i am a total noob when it comes to stuff like this. This is what i am getting:

 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Update by Extendoffice: 2018/9/21
Dim xCombox As OLEObject
Dim xStr As String
Dim xWs As Worksheet
Dim xArr

Set xWs = Application.ActiveSheet
On Error Resume Next
Set xCombox = xWs.OLEObjects("TempCombo")
With xCombox
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
If Target.Validation.Type = 3 Then
Target.Validation.InCellDropdown = False
Cancel = True
xStr = Target.Validation.Formula1
xStr = Right(xStr, Len(xStr) - 1)
If xStr = "" Then Exit Sub
With xCombox
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = xStr
If .ListFillRange = "" Then
xArr = Split(xStr, ",")
Me.TempCombo.List.xArr
End If
.LinkedCell = Target.Address
End With
xCombox.Activate
Me.TempCombo.DropDown
End If
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

 

Tempcombo is highlighted. can anyone help?!

 

 

3 Replies

@smg198811200 

xWs.OLEObjects("TempCombo") refers to an ActiveX combo box named TempCombo.

 

On the Home tab of the ribbon, in the Editing group, select Find & Select > Selection Pane.

You should see a task pane on the right hand side of the window that lists all objects (shapes) on the worksheet. Do you see TempCombo in this pane?

@Hans Vogelaar 

 

i have done all you have said and when i open the selection pane it is totally blank.

@smg198811200 

That means that there is no combo box named TempCombo on the active sheet, hence the error message. Perhaps the code was intended to be run from another sheet?