BeforeDoubleClick on Protected Sheet with DataValidation

[Teun Ham]

I have a protected sheet where users can make choices (by DataValidation) to certain cells.

I want to use the "BeforeDoubleClick" event to let the users switch between the options.
So if a cell holds "Option A" and "Option B", then doubleclicking would switch from "Option A" to "Option B" and vice-versa.

But the "BeforeDoubleClick" is never fired when a cell has DataValidation applied to it (when the sheet is protected)

Does anyone have a workaround for this issue?

 

[Teun Ham]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim sCheckRange As Variant
    sCheckRange = Array("NamedCell1", "NamedCell2")
    Dim i As Integer
    For i = LBound(sCheckRange) To UBound(sCheckRange)
        If Not Intersect(Target, ThisWorkbook.Names(sCheckRange(i)).RefersToRange) Is Nothing Then
            Dim sOptions As Variant
            sOptions = Split(ThisWorkbook.Names(sCheckRange(i)).RefersToRange.Validation.Formula1, ";")
            
            Dim iOptionIndex As Integer
            
            Dim j As Integer
            For j = LBound(sOptions) To UBound(sOptions)
                If ThisWorkbook.Names(sCheckRange(i)).RefersToRange.Value = sOptions(j) Then
                    iOptionIndex = j
                    Exit For
                End If
            Next j
            
            If iOptionIndex = UBound(sOptions) Then
                'Active option is the last option of the list, so select the first option
                ThisWorkbook.Names(sCheckRange(i)).RefersToRange.Value = sOptions(0)
            Else
                'Select the next option
                ThisWorkbook.Names(sCheckRange(i)).RefersToRange.Value = sOptions(iOptionIndex + 1)
            End If
            
            Cancel = True
        End If
    Next i
End Sub

This is the code I am using. If you want, you can test it yourself, you only have to change the array called "sCheckRange" to inculde the NamedRanges which have DataValidation (List).

The code works if the sheet is unprotected, but when the sheet is protected the BeforeDoubleClick is not fired when doubleclicking on the cell.

The BeforeDoubleClick IS fired when you doubleclick on a protected cell. So if I first select the cell with the DataValidation and then doubleclick on an protected cell, the BeforeDoubleClick is being fired. As the protected cell does not change the Target, the "current" cell is used instead.

But this behaviour is counter-intuitive (select the cell you want to change, then doubleclick somewhere else to change the cell you want to change)

So, in the end, I am still looking for a solution which solves the "BeforeDoubleClick on a cell with DataValidation on a Protected Sheet"-event notbeing fired.

 

[Teun Ham]

Seems like more people are hitting a wall here...

BeforeDoubleClick on Protected Sheet

 

[Teun Ham]

I have searched some more, but can't find any post/blog/etc. with a solution...

Anyone?

 

[Teun Ham]

I hate to bump this post, but this "bug" is annoying me

 

[Teun Ham]

Sounds like nobody is using the BeforeDoubleClick event on a protected sheet?

Any takers?

 

[Jaafar Tribak]

Do you necessarly have to do this by rightclicking the worksheet ? why don't you use a Button next to the cell with validation and perform whatever you want by clicikng on the button ? That should also be more user-freindly.

 

[Teun Ham]

Genious! I was so focused on the DoubleClick that I forgot that there's also a RightClick event!

DoubleClicking with Validation does not work on a protected sheet, but RightClick does! (just tested this)

Thanks Jaafar!