VBA Excel 2010 - data validation filter - event handler

[acctgdana]

Hi all,

I'm new to using VBA in Excel, and I'm having a tough time getting my event handlers to work properly.

I have data validation set up in a specific column. The user must choose from a specific list of part #s, which is listed above the cells with the data validation (current worksheet, range $G$2:$G$19999). I found code that seems to be working correctly here (http://www.contextures.on.ca/xlDataVal11.html), but to launch it requires a double-click.

I'd like to launch this without a double-click, and I'm hoping you can help with that. I don't want my end users to have to take their hands off the keyboard, since this sheet is being used for physical inventory data entry, and there will be a lot of tags to enter. Time savings are important for this task, and the double-click action takes longer (keyboard, to mouse, to keyboard again versus staying on the keyboard and pressing a key combo).

I tried replacing the BeforeDoubleClick event handler with SelectionChange, but that is already used further down in the code, so I kept getting errors I didn't know how to resolve. I know how to do this in Access, but I'm trying to avoid the hassle of importing/exporting data into Access for this once a year project.

I'm sure I'm missing something that will be obvious to more experienced VBA users, so I'm hoping you can help please.

Thanks,

Dana

Code as I have it written in my workbook:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim str As String
Dim cmbPartList As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet

Set cmbPartList = ws.OLEObjects("cmbPartList")
On Error Resume Next
With cmbPartList
'clear and hide the combo box
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
'if the cell contains a data validation list
Cancel = True
Application.EnableEvents = False
'get the data validation formula
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
With cmbPartList
'show the combobox with the list
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = str
.LinkedCell = Target.Address
End With
cmbPartList.Activate
'open the drop down list automatically
Me.cmbPartList.DropDown

End If

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub
'=========================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cmbPartList 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 cmbPartList = ws.OLEObjects("cmbPartList")
On Error Resume Next
With cmbPartList
.Top = 10
.Left = 10
.Width = 0
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With

errHandler:
Application.EnableEvents = True
Exit Sub

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
'Table with numbers for other keys such as Right Arrow (39)
'https://msdn.microsoft.com/en-us/library/aa243025%28v=vs.60%29.aspx

Private Sub cmbPartList_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(0, 1).Activate
Case Else
'do nothing
End Select
End Sub

 

[acctgdana]

Is this too complicated to modify? Maybe there's a better way to filter the combo box while typing? I need the data validation, but we have 15000+ item #s, so scrolling through a big long list isn't a workable option. Thanks, Dana