Forum Discussion

smg198811200's avatar
smg198811200
Copper Contributor
Jan 19, 2021

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

  • 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?

      • HansVogelaar's avatar
        HansVogelaar
        MVP

        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?

Resources