Forum Discussion
compile error method or data member not found
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
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?
- smg198811200Copper Contributor
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?