compile error method or data member not found

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?