Record ActiveCell Location prior to cell movement

Hi Bob,

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

Cancel = True

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:

Option Explicit


Dim rPreviousCell   As Range
Dim rActiveCell     As Range


Private Sub Worksheet_Activate()
    Set rActiveCell = ActiveCell
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Set rPreviousCell = rActiveCell
    Set rActiveCell = ActiveCell

End Sub


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

    Dim doReturn As Boolean

    doReturn = False

    With Target

        If Not Intersect(.Cells, Me.Range("AQ2:AQ3")) Is Nothing Then

            doReturn = True
            If LCase(.Value) = False Then
                  .Value = True
            Else
                  .Value = False
            End If

        End If

        If Not Intersect(.Cells, Me.Range("AT2:AT24")) Is Nothing Then
            doReturn = True
            Call SelectRoute
        End If

        If Not Intersect(.Cells, Me.Range("BD5:BD17")) Is Nothing Then
            doReturn = True
            Call SelectSchool
        End If

        If doReturn Then
            If Not rPreviousCell Is Nothing Then
                rPreviousCell.Select
            End If
        End If

    End With

    Cancel = True

End Sub

Plus the following code in a standard VBA code module:

Option Explicit


Public Sub SelectRoute()
    MsgBox "Selecting route ..."
End Sub


Public Sub SelectSchool()
    MsgBox "Selecting school ..."
End Sub

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