Pesky Excel VBA pop-up "Compile error"


Can someone help me understand and solve the following Error Alert which keeps popping up in my Excel (2013) app?

Microsoft Visual Basic for Applications

        Compile error:

        User-defined type not defined


I was looking online for a way to better utilize Data Validation drop-down menus in Excel and came across the following webpage:

        Contextures: Excel Data Validation Combo box Click

which includes the below VBA code to customize Excel drop-down menus. However, once it is pasted into a VBA code screen, any click in the spreadsheet always  produces the above error.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
On Error GoTo errHandler

If Target.Count > 1 Then GoTo exitHandler

Set cboTemp = ws.OLEObjects("TempCombo")
  On Error Resume Next
If cboTemp.Visible = True Then
  With cboTemp
    .Top = 10
    .Left = 10
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Value = ""
  End With
End If

  On Error GoTo errHandler
  If Target.Validation.Type = 3 Then
    'if the cell contains a data validation list
    Application.EnableEvents = False
    'get the data validation formula
    str = Target.Validation.Formula1
    str = Right(str, Len(str) - 1)
    With cboTemp
      'show the combobox with the list
      .Visible = True
      .Left = Target.Left
      .Top = Target.Top
      .Width = Target.Width + 15
      .Height = Target.Height + 5
      .ListFillRange = ws.Range(str).Address
      .LinkedCell = Target.Address
    End With
    'open the drop down list automatically
  End If

  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Exit Sub
  Resume exitHandler 

End Sub 
'Optional code to move to next cell if Tab or Enter are pressed
'from code by Ted Lanham
'***NOTE: if KeyDown causes problems, change to KeyUp

Private Sub TempCombo_KeyDown(ByVal _
        KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)
    Select Case KeyCode
        Case 9 'Tab 
            ActiveCell.Offset(0, 1).Activate
        Case 13 'Enter 
            ActiveCell.Offset(1, 0).Activate
        Case Else
            'do nothing
    End Select
End Sub

Open in new window

I must be doing something wrong but I would appreciate some help understanding what that is.

Also, when using VBA, is it OK to paste more than a single script in the View Code window? In other words, I already have one existing VBA script but I just assumed that additional VBA code can be pasted below the "End Sub" line of previous VBA. Is that an incorrect assumption?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Do you have right references selected as selected in this workbook? To check for the same in VB editor goto-->Tools-->References

Also look at the screenshot of references which you need to select for your reference..

Please note this version which showing 12 is for office-2007 this will change to 15 for office-2013.

Wayne Taylor (webtubbs)Commented:
Did you follow all the instructions and add the ActiveX Combobox 'TempCombo'? I received that error until I added the Combobox.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.