VBA and data validation

[mhessnm]

Hello,

I have a table with two columns. In one column is the data validation list, and in the other column are codes for each item in the data validation list. I was curious about whether I could use data validation so that when an item is picked from the data validation list, the corresponding code could be automatically entered in the cell instead of the item.

I don't think I can accomplish that all in data validation, but I found some VBA code online that uses a worksheet change event. I adapted it into the following:

Private Sub Worksheet_Change(ByVal Target As Range)Private Sub Worksheet_Change(ByVal Target As Range)

selectedNA = Target.Value

If IsEmpty(selectedNA) Then
Exit Sub
End If

If Target.Column = 11 Or Target.Column = 12 Then
selectedNum = Application.VLookup(selectedNA, Worksheets("Lookup").ListObjects("Navigation_codes").DataBodyRange, 2, False)
If Not IsError(selectedNum) Then
Target.Value = selectedNum
End If
End If

End Sub

It works great except for one thing. I can delete individual cells in each column and all works fine. However, If I delete a range of cells, such as selecting cells K5:K7 and hitting "delete," the sub crashes and it even kicks me out of Excel. I think I understand what is happening - the Target.Value is a value for one cell only, not a range of cells. And frankly, it is probably not a big deal. But, it is a puzzle to me how to allow for a range of cells to be deleted on this worksheet change event. I have a suspicion that the answer involves Intersect, but I don't know enough about Intersect to know if it is really the answer, or if something else is. What I've read about Intersect so far has not enlightened me.

Just wondering if I can get help solving this problem...I hate leaving loose ends once I notice an interesting problem.

Michael Hess

 

[Osvaldo Palmeiro]

Hi.
Try adding the red line as below.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
selectedNA = Target.Value
...
...
End Sub

 

[Max1616]

If your goal is to prevent excel from crashing when deleting, try throwing this at the top of the vba:
If InStr(Target.Address, ":") > 0 Then Exit Sub

This should prevent any action when an entire range is edited at once

Edit: Looks like Osvaldo posted a more elegent solution. Try that instead.

 

[mhessnm]

Hi.
Try adding the red line as below.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
selectedNA = Target.Value
...
...
End Sub

Thank you, Osvaldo! That worked - I didn't think it would be so simple. While I've learned a lot over the past two years, I still feel like a newbie. I appreciate your help, and will mark this as solved.

 

[mhessnm]

If your goal is to prevent excel from crashing when deleting, try throwing this at the top of the vba:
If InStr(Target.Address, ":") > 0 Then Exit Sub

This should prevent any action when an entire range is edited at once

Edit: Looks like Osvaldo posted a more elegent solution. Try that instead.

Thank you Max! I tried Osvaldo's and it worked. I didn't think it would be so simple. I'll have to look at your code to try to understand how it works, and I appreciate your help! I'm marking this as solved.