WeThotUWasAToad
asked on
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.
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
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
'====================================
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
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.
DataValComboboxClick.xls