Double Click

E

edgaryp

New member
Joined
Aug 4, 2013
Messages
1
Reaction score
0
Points
0
Hi, the code below is working fine. However I would like to improve the code, currently the code disables the double click feature for all the cells in the sheet1 and sheet2, but I would like to keep the double click feature for some cells (J10, J12, H14) so the users still able to edit the cells via double click mouse.

Thank you in advance

Ed



Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 
    'Status to FYI to Flow up to Solved
    If Not Intersect(Target, Range("Status")) Is Nothing Then
    Select Case Target.Cells(1, 1).Value
        Case "Status"
            Target.Value = "FYI"
        Case "FYI"
            Target.Value = "Follow Up"
        Case "Follow Up"
            Target.Value = "Solved"
        Case "Solved"
                Target.Value = "Status"
        End Select
    End If
   
    'Profile to Updated
    If Not Intersect(Target, Range("Profile")) Is Nothing Then
    Select Case Target.Cells(1, 1).Value
        Case "Profile"
            Target.Value = "Updated"
        Case "Updated"
            Target.Value = "Profile"
        End Select
    End If
   
    'Priority to High to Medium to Low
    If Not Intersect(Target, Range("Priority")) Is Nothing Then
    Select Case Target.Cells(1, 1).Interior.ColorIndex
        Case xlNone
            Target.Interior.ColorIndex = 3
            Target.Value = "High"
        Case 3
            Target.Interior.ColorIndex = 44
            Target.Value = "Medium"
        Case 44
            Target.Interior.ColorIndex = 43
            Target.Value = "Low"
        Case 43
            Target.Interior.ColorIndex = xlNone
            Target.Value = "Priority"
        End Select
    End If
 
    'Background Colour None to Black
    If Not Intersect(Target, Range("Department")) Is Nothing Then
    Select Case Target.Cells(1, 1).Interior.ColorIndex
        Case xlNone
            Target.Interior.ColorIndex = 1
        Case 1
            Target.Interior.ColorIndex = xlNone
        End Select
    End If
   
    Cancel = True
 
End Sub
 

Attachments

  • Help.xlsm
    88.6 KB · Views: 12
Last edited:
Instead of disabling the double click to prevent users from editing, it would make more sense to simply unprotect cels (J10, J12, H14) and then lock the sheet. Or am I missing something?

Edit: Ahh, I see that you are using the doubleclick event to toggle things. It would have been helpful if you had stated the purpose of the double-click, so that helpers don't have to read through the code and determine it for themselves.
 
Last edited:
Just define the named range "AllowEditing" as J10, J12, H14 and then wrap this around the outside of your current code:

If Not Intersect(Target.cells(1,1), Range("AllowEditing") ) is nothing then

Existing code

End If
 
You must log in or register to reply here.
Back
Top