Data Validation -- Drop Down List on Error

[rjm65]

I have an order form, column 1 is quantity, column 2 is item number, column 3 is description, and column 4 is cost. Columns 3 and 4 are automatically filled in using VLOOKUP. I use Data Validation in column 2 so that the part number must be typed in correctly. We have about a thousand different part numbers so a drop down list is a little cumbersome to use for column 2. Our part numbers can get fairly long too, I was wondering if it was possible to have a drop down list appear ONLY if an incorrect part number is entered, and it showed either near matches or the drop down list was scrolled to the same beginning character as the incorrectly entered part number. Posting here because I suspect it can only be done with code.

Thanks,
Raymond

 

[ETID]

Assuming that you populate your valid "lookup" list of numbers on sheet2 col A and that is is sorted alphabetically!


try this event code.....



Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell.Address = Sheets("sheet2").Range("z1") Then
Exit Sub
Else
End If
user_entered = Range(Sheets("sheet2").Range("z1"))
On Error GoTo err_hit
x = Application.WorksheetFunction.Match(Range(Sheets("sheet2").Range("z1")), Sheets("sheet2").Range(Sheets("sheet2").Range("valid_list").Address), 0)
Exit Sub
err_hit:
validate_list
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
Sheets("sheet2").Range("z1") = ActiveCell.Address
With Range(Sheets("sheet2").Range("z1")).Validation
.Delete
End With
Application.EnableEvents = True
End Sub
Sub validate_list()

Application.EnableEvents = False
If IsEmpty(Range(Sheets("sheet2").Range("z1"))) Then
c_val = "Invalid_Entry"
Else
c_val = Range(Sheets("sheet2").Range("z1"))
End If

For Each c In Sheets("sheet2").Range("valid_list")

c_Text = c.Text
If Left(CStr(c_Text), 1) = Left(c_val, 1) And start_it = 0 Then
start_it = c.Row
Else
End If
If start_it > 0 And Left(c_Text, 1) = Left(c_val, 1) Then
stop_it = c.Row
Else
End If

Next

If start_it > 0 And stop_it > 0 Then
ActiveWorkbook.Names.Add Name:="list_window", RefersToR1C1:= _
"=Sheet2!R" + Trim(CStr(start_it)) + "C1:R" + Trim(CStr(stop_it)) + "C1"

With Range(Sheets("sheet2").Range("z1")).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=list_window"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Else
With Range(Sheets("sheet2").Range("z1")).Validation
.Delete
End With
MsgBox ("No Similar items found in the Valid_List Range " + Sheets("sheet2").Range("valid_list").Address)
Range(Sheets("sheet2").Range("z1")) = "Invalid_Entry"
End If
Range(Sheets("sheet2").Range("z1")) = "Invalid_Entry"
Range(Sheets("sheet2").Range("z1")).Select
Application.EnableEvents = True

End Sub

 

[ETID]

Doh....almost forgot! you have to give you valid list in sheet2 col "A" a Range Name....the code is expecting "Valid_list".

 

[rjm65]

Thanks ETID. I listed alphabetically my items in col A of sheet 2 and named the range "Valid_List". I then pasted the above code into THIS WORKBOOK of my VBA Project.

My drop down data validation boxes are working like normal, I do not see any changes after adding the code. I need to clarify one other point, in cell B1 I have a drop down list using data validation for inputting my customer name. My item entry begins with B9 on down. So I need the above code to only take effect on the drop down boxes below B9. Also, what is referenced in:

If ActiveCell.Address = Sheets("sheet2").Range("z1") Then
Exit Sub?

I have no Range named Z1. I'm just learning my way around VBA, so much of the code doesn't make complete sense, other than I have a general idea of what steps are taking place.

Thanks,
Raymond




I'm just finding my way around VBA right now,

 

[ETID]

'Try the edits below to the code...

'As far as the code not running, check that macro security
'is set to med. or low and that the code is pasted into the
'worksheet event and not a module or workbook event.

'the most direct way is to right click on the sheet name
'tab, then select "view code", change the left drop down in
'the right frame from general to worksheet if the code you
'pasted is there then you should be good...if not then
'paste all of the code there.



Private Sub Worksheet_Change(ByVal Target As Range)
'in this sub change the first line to this
If ActiveCell.Address = Sheets("sheet2").Range("z1") Or ActiveCell.Row < 9 Then



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'in this sub add this
If ActiveCell.Row < 9 Then
Exit Sub
Else
End If

 

[rjm65]

I had placed the code in workbook event and not worksheet. Now pasted in worksheet and it works, except the code affects every cell in the worksheet and not just the ones that have a drop down data validation in them. So if I try to enter a quantity in column A, it gives a "No Similar Items Found...." message. Also, after selecting a cell, when I return to it, the drop down list has been removed. If I look at data validation for that cell, it has been changed to allow any value, and no longer has list selected.

Raymond

 

[ETID]

You'll need to add something like...

If ActiveCell.column = (your choice here 1 to 265) then exit sub
else
end if

to the cell "select" and or "change" events

 

[ETID]

Still having problems...?