Worksheet_BeforeDoubleClick, avoid within-cell selection on exit?

Question

I have a Worksheet_BeforeDoubleClick event that identifies which cell is being clicked, and if it is within a target range of cells, it brings up a userform.

When exiting from that userform, I'd like to have that same cell 'selected', but as the equivalent of a single click (whole cell selected, dark border, cursor is not active in the cell). Due to the DoubleClick functionality, I end up with the cursor in the cell itself.

I did ending the Worksheet_BeforeDoubleClick event by selecting another cell, then reselecting the target cell, but (presumably because this all happens  "_Before", I'm still ending up with the cursor in the cell.

What is the appropriate syntax to force the single-click selection of that same target cell, and perhaps more importantly, where is the appropriate place to put that code- at the end of the UserForm code (and all cancellation options)? Or does the Userform functionality also occur before the last bit of the DoubleClick is fired?

Thank you,

Keith

Answer 1

When exiting from that userform, I'd like to have that same cell 'selected', but as the equivalent of a single click (whole cell selected, dark border, cursor is not active in the cell). Due to the DoubleClick functionality, I end up with the cursor in the cell itself.

Assuming the UserForm's code does not select a different cell as part of its functionality, simply put this statement...

Cancel = True

inside the BeforeDoubleClick event code prior to exiting it (you can put it as the first line of code if you want).

Answer 2

How about:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim rSpecial As Range

Set rSpecial = Range("B2:E4")

If Intersect(Target, rSpecial) Is Nothing Then Exit Sub

Cancel = True

ActiveCell.Select

DoEvents

'##################Proceed with UserForm code#################

End Sub

Note the inclusion of the Cancel line.

Answer 3

Excellent, that was a lot more straightforward than I feared - thank you for both replies.