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.

 

[Fluff]

Add this to the top of the doubleclick event

If Intersect(Target, Range("C:K")) Is Nothing Then Exit Sub

 

[sarahrb1989]

Thank you, that helps me so much!

 

[Fluff]

Glad to help & thanks for the feedback

 

[sarahrb1989]

I also just noticed a problem with typing in the M column. When ever I try to type text, it disappears. I checked the data validation does not have the "error message" checkbox selected. Could this be something to do with the second part of the code?

Any advice is greatly appreciated.

 

[Fluff]

On the file you supplied there is no data validation in col M.
I don't have any probs typing into that column

 

[sarahrb1989]

My apologies, on the test log it is column K.

 

[Fluff]

Ok try this mod

           For Each Dn In Rng
                If LCase(Dn.Value) = LCase(Target.Value) Then
                    newVal = Dn.Offset(, -1).Value

 

[sarahrb1989]

I replaced the piece of code you provided but it didn't work. The text still disappeared. I'm wondering if it is a setting of some sort. I'm really not sure.

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 Dn 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.Count = 1 Then
  If Not Intersect(Target, Range("K:K")) Is Nothing Then
     Application.EnableEvents = False
            [COLOR=#FF8C00]For Each Dn In Rng
                If Dn.Value = Target.Value Then
                    newVal = Dn.Offset(, -1).Value[/COLOR]
                    Exit For
                End If
           Next Dn
            Application.Undo
            oldVal = Target.Value
            Target.Value = newVal
                If Not newVal = "" Then
                    Target.Value = IIf(oldVal = "", newVal, oldVal & ", " & newVal)
                End If
    Application.EnableEvents = True
    End If
End If
End Sub

 

[Fluff]

That code doesn't include my mod.