Double Click Problem with Code

[sarahrb1989]

I am using an Excel file to store primarily demographic data. I have two pieces of code imbedded in this workbook that allows me to use a Combobox to select demographic data and change to an abbreviation.
For example Green -> GR, Purple ->PUR.

The code has affected my ability to double click in the workbook on cells that are not utilizing the code to use my cursor to type instead of using the formula bar. Please see the attached test file.

https://www.dropbox.com/s/nohdlxj35zdgrpa/Completed Test Log.xlsm?dl=0


Does anyone know how to modify this code to allow for double clicking inside cells not utilizing the code (no data validation lists) to give me my cursor back??

Any help is much appreciated.

 

[sarahrb1989]

I know, I was highlighting where I replaced it in case I put it in the wrong spot. I replaced that code with the mod you provided but it did not work.

 

[Fluff]

If I type Autism into K2 I get AUT, if I then type Other I end up with AUT,OTH
If you type a value that is not in the validation list, then it will wipe the cell contents.

 

[sarahrb1989]

Ohhh okay, is there a way to change it so that I can type anything? I sometimes just go through the sheet and type the abbreviations which aren't in the data validation list. Please let me know.

Thank you!

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim rngDV As Range
   Dim oldVal As String
   Dim newVal As String
   Dim Rng As Range
   Dim Fnd As Range

   On Error Resume Next
   With Sheets("Demographics Options") 'Change Date sheet here as required
      Set Rng = .Range("R2", .Range("R" & Rows.Count).End(xlUp))
   End With
   If Target.CountLarge > 1 Then Exit Sub
   If IsEmpty(Target.Value) Then Exit Sub
   If Not Intersect(Target, Range("K:K")) Is Nothing Then
      Application.EnableEvents = False
      newVal = Target.Value
      Application.Undo
      oldVal = Target.Value
      Set Fnd = Rng.Find(newVal, , , xlWhole, , , False, , False)
      If Fnd Is Nothing Then
         Target.Value = IIf(oldVal = "", UCase(newVal), oldVal & ", " & UCase(newVal))
      Else
         Target.Value = IIf(oldVal = "", Fnd.Offset(, -1).Value, oldVal & ", " & Fnd.Offset(, -1).Value)
      End If
      Application.EnableEvents = True
   End If
End Sub