Disable Double Click

[hobbiton73]

Hi, I wonder whether someone may be able to help me please.

Firstly my apologies because to some this may be a very stupid error, but I'm only really just starting to learn and understand VBA.

I'm using the code below to attempt to prevent users from double clicking in a cell within a given range.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("B5:Q2000")) Is Nothing Then Cancel = True
End Sub

Unfortunately it's not working because the 'Double Click' is still enabled and I'm not sure where I'm going wrong.

I just wondered whether someone may be able to take a look at this please and provide some guidance on where my error is?

Many thanks and kind regards

[Tommy]

Try this. You are asking the sub to see if the intersect is in this range then you inverse it and then compare to nothing.
So the below code means that if the target is in the range I want to cancel the double click.

If Intersect(Target, Range("B5:Q2000")) Then Cancel = True

[hobbiton73]

Hi @Tommy, thank you for taking the time to reply to my post and for the proposed solution.

I've made the changes to my code, but unfortunately this doesn't seem to work as the user can still double click in the cells.

I'm not really sure what else I'm doing wrong.

Many thanks and kind regards

[Kenneth Hobs]

They can still press F2 to edit the cell. Why not protect the sheet and lock the cells if you don't want them to edit the cells?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("B5:Q2000")) Is Nothing Then Cancel = True
End Sub

[hobbiton73]

Hi @Kenneth Hobs and @Tommy, thank you very much to both of you for taking the time to view and reply to my post.

I have managed to implement both of your suggestions and the spreadsheet works perfectly.

Once again, thank you for your time and kind regards