Worksheet SelectionChange - Need value from previous cell (the one that was just changed)

[BigShango]

Hi,

I've had a search but can't find a similar question.

I am trying to get whatever a user has just typed into a cell and copy it to a different worksheet. I figured this was best to run in the SheetSelectionChange section, the idea being someone types something into cell B4 (or any cell) then whenever they hit enter or click away whatever was typed into B4 copies to another worksheet.

So simply fill the variable "MyCell" with the contents of the cell the person just changed, then I can do what I like with it. I thought MyCell = activecell.value would do but that gives the value of the cell the user is now on

Example, enter "TEST" in B4 then press enter, MyCell contains the value of B5. Enter "TEST" then press tab, MyCell contain C4's value. I can't use offset as I don't know which way the user might go after entering the text, or they might click to a totally different cell.

Basically I need MyCell = PreviousActiveCell.Value or a different way to fill a variable with the text a user has just entered into a cell as soon as they leave that cell.


Any ideas?

Thanks

 

[jmacleary]

Hi there. You could use worksheet change event to store the activecell value in a temporary variable (e.g. "Changes"). Combine this with a selection change event that copies Changes to MyCell. Bear in mind that you will need more checking than I have built in (basically only ensuring the change is to a single cell)

Dim Changes As String
Dim MyCell As String
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
Changes = Target.Value
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MyCell = Changes
End Sub

 

[BigShango]

Thanks, that looks like it'll do the job nicely.

 

[RoryA]

I don't really see why you need both events for that. Just use the Change event.

 

[jmacleary]

I don't really see why you need both events for that. Just use the Change event.

Of course you're right Rory - me being dim. It only needs to be:

Dim MyCell As String
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
MyCell = Target.Value
End If
End Sub

 

[pbornemeier]

This may cause problems with other macros that change the active cell, but it meets your requirements. All code into the ThisWorkbook CodePage.
Value of the previous ActiveCell is in column C of the 2nd to last row of ActiveCell Hist

Option Explicit

Private Sub Workbook_Open()
    SimulateWorkbookOpen
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    RecordActiveCell False
End Sub

Sub RecordActiveCell(bInitial As Boolean)
    If ActiveCell.Worksheet.Name <> "ActiveCell Hist" Then
        Dim lACHWriteRow As Long
        Application.EnableEvents = False
        
        With Worksheets("ActiveCell Hist")
            If bInitial Or .Cells(1, 1).Value = vbNullString Then
                .Cells.ClearContents
                lACHWriteRow = 1
            Else
                lACHWriteRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
            End If
            
            .Cells(lACHWriteRow, 1).Value = ActiveCell.Worksheet.Name
            .Cells(lACHWriteRow, 2).Value = ActiveCell.Address
            If lACHWriteRow > 1 Then
                .Cells(lACHWriteRow - 1, 3).Value = "='" & .Cells(lACHWriteRow - 1, 1).Value & "'!" & .Cells(lACHWriteRow - 1, 2).Text
            Else
                .Cells(lACHWriteRow, 3).Value = ActiveCell.Text
            End If
            
            'Comment out following to keep full record
            If lACHWriteRow > 10 Then
                .Range("A1:A" & lACHWriteRow - 5).EntireRow.Delete
            End If
            
        End With
        Application.EnableEvents = True
    End If
End Sub

Private Sub SimulateWorkbookOpen()
    Application.EnableEvents = False
    Const sWorksheet As String = "ActiveCell Hist"
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets(sWorksheet).Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    Worksheets.Add(After:=Sheets(Sheets.Count)).Name = sWorksheet 'After last
    
    Application.EnableEvents = True
    RecordActiveCell True
End Sub

Private Sub StopTracking()
    Application.EnableEvents = False
    Debug.Print "Tracking Disabled"
End Sub

Private Sub StartTracking()
    Application.EnableEvents = True
    Debug.Print "Tracking Enabled"
End Sub