VBA Excel 2010 - data validation filter - event handler

acctgdana

New Member
Joined
Aug 27, 2015
Messages
13
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top