• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 528
  • Last Modified:

Pesky Excel VBA pop-up "Compile error"

Hello,

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

------------------------------------------------------------

Background
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
    cboTemp.Activate
    'open the drop down list automatically
    Me.TempCombo.DropDown 
  End If

exitHandler:
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Exit Sub
errHandler:
  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?

Thanks
0
WeThotUWasAToad
Asked:
WeThotUWasAToad
1 Solution
 
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.

ReferencesDataValComboboxClick.xls
0
 
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.
0
 
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Tackle projects and never again get stuck behind a technical roadblock.
Join Now