Auto complete/fill text typing in cell with drop down?

[Hawrylak]

Hello All,

I have the following code I found years ago and I was trying to utilize it again for another application. I am now receiving some errors and I can only imagine it has something to do with newer versions of Excel. The code allows for a user to type text in a cell were a dropdown exists (without requiring a combo box) and auto completes the text as the user continues to type. Something is broken and I need assistance please.


'Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
'Cancel As Boolean)

' On Error GoTo errHandler

'If Target.Validation.Type = 3 Then
' strr = Right(Target.Validation.Formula1, Len(Target.Validation.Formula1) - 1)
' End If
' UserForm1.Show
'exitHandler:
' Application.ScreenUpdating = True
' Application.EnableEvents = True
' Exit Sub
'errHandler:
' Resume exitHandler
'End Sub



'=========================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo errHandler

If Target.Validation.Type = 3 Then
strr =
Right(Target.Validation.Formula1, Len(Target.Validation.Formula1) - 1)
End If
UserForm1.Show
exitHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
errHandler:
Resume exitHandler


Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Application.EnableEvents = False
Application.ScreenUpdating = True

If Application.CutCopyMode Then
'allow copying and pasting on the worksheet
GoTo errHandler
End If

Set cboTemp = ws.OLEObjects("ComboBox1")
On Error Resume Next
With cboTemp
.Top = 10
.Left = 10
.Width = 0
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With

'errHandler:
'Application.EnableEvents = True
' Exit Sub

End Sub '====================================

Private Sub ComboBox1_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

 

[Hawrylak]

Problem solved. I found a missing reference table.