CODEWITHSUNDEEP
excelvbaevents
user2845354
user2845354

Reputation: 31

Previously active cell

For the code I am writing I monitor the changes in certain cell ranges to run functions and private subs. For this I use the Intersect function in the worksheet_change sub.

However, the trigger for the intersect 'test' is always that I 'move out' of the cell I am testing for whether it'd be via mouseclick into a different cell or via cursor move.

What I need is a way to define a variable which contains the .address of the cell I had selected before.

I tried the code below, but all I get is errors.

Does anybody have an idea how to do this?

Public xfrLastCell As String
Public xfrActiveCell As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If xfrActiveCell = "" Then xfrActiveCell = ActiveCell.Address
xfrLastCell = xfrActiveCell
xfrActiveCell = ActiveCell
MsgBox xfrLastCell

End Sub

Upvotes: 3

Views: 26269

Answers (4)

ZygD
ZygD

Reputation: 24458

If is unnecessary. I have used this:

Public sPreviousTarget As String
Public sTarget As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  sPreviousTarget = sTarget
  sTarget = Target.Address
End Sub

Also, Target is different from ActiveCell (ActiveCell refers to one of the cells in Target).

Upvotes: 0

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11181

With this code, referring PreviousActiveCell will return desired result:

Public PreviousActiveCell as Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static pPrevious as Range
    Set PreviousActiveCell = pPrevious
    Set pPrevious = ActiveCell
End Sub

This works inside single worksheet. Do you need a previous cell across other sheets and workbooks?

Upvotes: 3

Gary's Student
Gary's Student

Reputation: 96773

It might be easier to "remember" the Range rather than the address of the range:

Dim Oldcell As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Oldcell Is Nothing Then
        Set Oldcell = Target
        Exit Sub
    End If
    MsgBox "New cell is " & Target.Address & vbCrLf & "Old cell was " & Oldcell.Address
    Set Oldcell = Target
End Sub

Upvotes: 2

Krishna
Krishna

Reputation: 2481

below code works for me - your assignment of activecell was missing an Address meaning activecell variable is always blank

Option Explicit

Public xfrLastCell As String
Public xfrActiveCell As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If xfrActiveCell = "" Then xfrActiveCell = ActiveCell.Address
xfrLastCell = xfrActiveCell
xfrActiveCell = ActiveCell.Address
MsgBox xfrLastCell

End Sub

Upvotes: 5

Related Questions