Results 1 to 6 of 6

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

  1. #1
    Board Regular
    Join Date
    May 2014
    Posts
    94
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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
    Last edited by BigShango; Aug 8th, 2019 at 07:20 AM.

  2. #2
    Board Regular jmacleary's Avatar
    Join Date
    Oct 2015
    Location
    at a desk in the UK
    Posts
    629
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    0 Thread(s)

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

    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)
    Code:
    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
    If my answer has helped, please vote using the Thanks or Like buttons on the left.
    John

  3. #3
    Board Regular
    Join Date
    May 2014
    Posts
    94
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

  4. #4
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,815
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

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

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

  5. #5
    Board Regular jmacleary's Avatar
    Join Date
    Oct 2015
    Location
    at a desk in the UK
    Posts
    629
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by RoryA View Post
    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:
    Code:
    Dim MyCell As String
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count = 1 Then
    MyCell = Target.Value
    End If
    End Sub
    If my answer has helped, please vote using the Thanks or Like buttons on the left.
    John

  6. #6
    Board Regular pbornemeier's Avatar
    Join Date
    May 2005
    Location
    Virginia Beach, VA USA
    Posts
    3,659
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

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

    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


    Code:
    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
    Phil

    - Use CODE tags to keep your code formatted. See: BB Tags
    - How to attach Screenshots
    - Try searching for your answer first, see how
    - Test and validate results for all code on a copy of your worksheet!! How do you use the code you just found?
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •