Solved: How to "unselect" a cell or range

[Thymen]

Hi folks,

In my current project it happens that when a users doubleclick a certain cell, a piece of code is executed. However, after that has been done, the cell gets activated. Meaning, the cursor appears in the cell, blinking, waiting for imput. At the moment I "unselect" the cell by selecting the protected one next to it, but there must be a better way to do this.

Is there a standard way of 'unselecting" cells? Been looking for some time but I cannot seem to find it.

Greetings,

Thymen

[XLGibbs]

If the double click executes code, then put the desired next active cell in the code like:

Target.Offset(1).Activate

to activate (but not select) one row down

[Thymen]

Target.Offset(1).Activate
That's how I do it now. But I mean more like what you get when hitting the Escape button after you doubleclicked a cell: the active cell remains active, but is in "edit mode" no more...

Thymen

[XLGibbs]

The best way is not to select the cell in the first place, but I am unaware of a unselect property for the cell or an edit mode = false.

I would tweak your before_DoubleClick code to not require the cell to be selected....or to activate an adjacent cell as part of the code execution, then just activate the double clicked target on completion...

There are likely more robust options to trigger the required operation that does not rely on a double click....perhaps?

[Thymen]

I have a workbook with several sheets in it, each sheet containing several blocks of data. Users can add / remove / blocks by selecting them (click), then select the required operation from a drop-down menu. I have the entire sheets protected, all cells locked except from one cell per block with a distinct colour. People are supposed to click this cell. I then use it to figure out which block has been selected. Doubleclicking results in a form being opened with all the block data loaded in it, for editing. After I close that form, I need to select/active an adjacent cell to "get out of the cell edit mode". It works, but I just wonder if there is a standard command / more elegant way of doing it.

If the number of block and their location was stationary, I could have used a comand button...but this is not the case.

Thymen

[XLGibbs]

Well, the double click event in a cell will force edit mode...perhaps an alternate method of activating the chart...maybe placing an x in the cell and hitting enter....that way no edit mode.

You would just have your change event look for a change in that column where the result is "x" or something....

I am unaware of a more elegant method of not forcing edit mode, other than perhaps this may work:

in the before double click event, call your routine, but then add this line when it is complete (in the event, not the subroutine)

Cancel = True

which would essentially allow it to run your routine, then cancel the double click, thus not forcing the edit mode.

You would have to trap for your column/row where this blank cell is, so that it would only work on your desired cells (and also only cancel the edit mode on those cells)

[johnske]

Hi Thymen,

As XLGibbs suggested, something like this will cancel the double-click on the cell where you're using it to activate your procedure but allow a double-click anywhere else on the sheet (assuming you double-click A1 to run your procedure)...Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target = Range("A1") Then
Call MySub
Cancel = True
End If
End Sub

Sub MySub()
MsgBox "MySub called"
End Sub

[Thymen]

Cancel = True

Yep! That does what I want! Thanks!

Thymen

PS: sometimes you get stuck. You want someting simple, easy to explain, but just cannot find out the right piece of code, or the proper command. Even the manuals don't help you out. It is then that this forum shows it's true worth. All you folk out here are very helpfull, and I cannot emphasize enough how much I appreciate it........:clap:

PPS: marking this thread as Solved does not work..... sorry.......