Record ActiveCell Location prior to cell movement

Hi Bob,

Try adding the following statement as the last line of your code:

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

GregM:

Nope that didn't do it. It's still like the entire routine is being executed after the double-click not before it.

Thanks

Bob

Hi Bob,

I used dummy "SelectSchool" and "SelectRoute" subroutines when testing your code. With these routines in place your code worked when I included the "Cancel = True" command, but would not work without it. When I say your code "worked", I mean that it successfully named the active cell as "rms" while the code was running, and that this name had been deleted when code execution was finished.

In view of this, it seems that the main problem may lie within one or both of the subroutines - you could easily test this by temporarily including an "Exit Sub" command as the first line of code in each of the subroutines. If that locates the problem you could post the subroutines here so that we can take a look at them.

Regards,

Greg M

GregM:

Thank you, sir:

The question that I have is what is the relationship of the "rms" label - is it in the activeCell before you double-clicked elsewhere or in the cell that you double-clicked. The routine has always done the later and I want it to do the former (the "rms" should mark the cell that was active prior to the double-click). Where is your proto-typed code putting the "rms" label?

TIA.

Bob

Hi Bob,

Ok - now I see what you're trying to do!

E.g. the active cell is A1, you doubleclick on cell E5, and you want to return to cell A1 when your code has executed. The main problem is that the first click of the doubleclick selects cell E5 which immediately becomes the active cell. In effect, you're trying to trap a "BeforeCLICK" (not "BeforeDoubleClick") event.

Unfortunately, as far as I know, Excel does not have a worksheet "BeforeClick" event, so it will be necessary to develop a workaround. The approach I used was to continuously track the previously selected cell - e.g. with cell A1 as the current selection, click on cell E5 and cell A1 is stored as the previously selected cell. Now click on cell B3, and E5 is stored as the previously selected cell, and so on.

The following is the code I used in the VBA code module for the worksheet:

Please Login or Register  to view this content.

Plus the following code in a standard VBA code module:

Please Login or Register  to view this content.

This allowed me to select any cell (e.g. AP1) on the worksheet, doubleclick on any cell in the ranges you specify (AT2:AT24 etc.), have the BeforeDoubleClick code execute, and return automatically to cell AP1. Using this approach meant that I didn't need the user-defined name "rms".

I hope the above helps - please let me know how you get on.

Regards,

Greg M

Sir:

You are a steely-eyed Excel-man! (apologies to Apollo-13).

Thanks.

Bob

Hi again Bob,

Many thanks for your feedback and for the compliment - I'm very pleased that I was able to help.

Best regards,

Greg M