Data Validation List annoyance !!

[Jaafar Tribak]

Hi dear forum members,

I have the following scenario:

1- Add data validation list to cell A1.
2- Unlock the DV cell (A1) and Protect the worksheet.
3- I have the following code that displays a Msgbox upon double clicking the DV cell (A1):

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Address = Range("A1").Address Then
        Cancel = True
        MsgBox "hello"
    End If
End Sub

PROBLEM:

if the worksheet is protected with the Edit Objects option CheckBox left unticked then the Msgbox never gets a chance to be displayed.

I have to make sure that I tick the Edit Objects in order for the MsgBox to be displayed.

The problem I am facing now is that if I tick the Edit Objects option when protecting the worksheet then all other objects, shapes etc that I have on the worksheet can be moved, resized, deleted by the user which I obviously want to avoid at all cost.

Can anybody think of a work around this problem ?

Using Excel 2016.

Thank You.

 

[mole999]

is it possible to refer to the object by its name, so if a user selects one that isn't usable the cursor is moved away ? just a thought

 

[Fazza]

any interest in changing from a double click to a right click event ?

 

[Jaafar Tribak]

Thanks Fazza and mole999.

I really must us the DBlClick event to bring up the MsgBox

As for preventing the selection of shapes , I had thought about something along those lines but the main issue is that there is no native event that fires when a shape is being selected.

I can think of a couple of vba tacky workarounds in order to fire an event when a shape is selected but it is flaky at best.

I just hoped there would be a more stable fix.