Results 1 to 6 of 6

SelectionChange event not firing

Threaded View

  1. #5
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,056

    Re: SelectionChange event not firing

    Respectfully, there is no way the code you posted ever worked. Target(c).Value and PreviousValues(c) make no sense at all if c is a Range object, in addition to the errors I mentioned previously.

    You could do something like this:

    Private Sub Worksheet_Change(ByVal Target As Range)
        
        Dim LR     As Long
        Dim x As Long, y As Long
        Dim a As Range
        Dim sel As Range
        Dim PreviousValues As Variant
        
    '    On Error Resume Next
        Application.EnableEvents = False
        Set sel = Selection
        Set a = Target
            PreviousValues = a.Value
            Application.Undo
            If a.Count > 1 Then
            For x = 1 To a.Rows.Count
                For y = 1 To a.Columns.Count
                    If a.Cells(x, y) <> PreviousValues(x, y) Then
                With Sheets("AuditTrail")
                         LR = .Cells(Rows.Count, "A").End(xlUp).Row + 1
                        .Cells(LR, "A").Value = Now
                        .Cells(LR, "B").Value = Environ("UserName")
                        .Cells(LR, "C").Value = ActiveSheet.Name & "!" & a.Cells(x, y).Address
                        If PreviousValues(x, y) = "" Then
                            .Cells(LR, "D").Value = "BLANK!"
                        Else
                            .Cells(LR, "D").Value = PreviousValues(x, y)
                        End If
                        If a.Cells(x, y).Value = "" Then
                            .Cells(LR, "E").Value = "BLANK!"
                        Else
                            .Cells(LR, "E").Value = a.Cells(x, y).Value
                        End If
                End With
            End If
            Next y
        Next x
        Else
                     If a.Value <> PreviousValues Then
                With Sheets("AuditTrail")
                         LR = .Cells(Rows.Count, "A").End(xlUp).Row + 1
                        .Cells(LR, "A").Value = Now
                        .Cells(LR, "B").Value = Environ("UserName")
                        .Cells(LR, "C").Value = ActiveSheet.Name & "!" & a.Address
                        If PreviousValues = "" Then
                            .Cells(LR, "D").Value = "BLANK!"
                        Else
                            .Cells(LR, "D").Value = PreviousValues
                        End If
                        If a.Value = "" Then
                            .Cells(LR, "E").Value = "BLANK!"
                        Else
                            .Cells(LR, "E").Value = a.Value
                        End If
                End With
            End If
           End If
        Target.Value = PreviousValues
        sel.Select
    Ending:
        On Error GoTo 0
        Application.EnableEvents = True
        
    End Sub
    Last edited by romperstomper; 01-30-2019 at 08:09 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. SelectionChange() Event Stops Firing
    By jo15765 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-21-2017, 09:25 AM
  2. [SOLVED] Userform multipage control - exit event not firing or event order
    By jane serky in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-14-2013, 10:23 AM
  3. How to prevent SelectionChange event firing before Change event?
    By franklyn in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-29-2012, 05:17 AM
  4. [SOLVED] VBA + SelectionChange event
    By HuskerBronco in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-13-2011, 06:36 PM
  5. Worksheet SelectionChange Event
    By mjack003 in forum Excel General
    Replies: 2
    Last Post: 05-08-2006, 03:35 PM
  6. SelectionChange event
    By Kate in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-22-2005, 02:10 PM
  7. SelectionChange event
    By Hayeso in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-01-2005, 11:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1