Type Mismatch Error and Wrong Order of Output

[drmingle]

I am having two issues with the below working code:

1. It places the last action at the bottom instead of the top.
2. When a user completes an auto-fill selection it kicks out a bug (as if it is getting overloaded with things to report on) [The actual error is a Run-Time Error '13': Type mismatch] which bugs on this line ==> If Target.Value <> PreviousValue Then.

Regarding 2, I would be okay with suspending the code while an auto-fills are complete (since they don't happen that often).

As a side not this is running on a single sheet in the workbook rather than a module.

Dim PreviousValue

'Private Sub Worksheet_Change(ByVal Target As Range)


 ' If Target.Value <> PreviousValue Then
  '   Sheets("log").Cells(65000, 1).End(xlUp).Offset(1, 0).Value = _
   '    Application.UserName & " changed cell " & Target.Address _
    ' & " from " & PreviousValue & " to " & Target.Value & " at " & Now() & " for file: " & ActiveWorkbook.FullName
' End If
'End Sub

'Private Sub Worksheet_SelectionChange(ByVal Target As Range)


 ' PreviousValue = Target.Value
'End Sub

 

[Norie]

Perhaps.

Option Explicit

Dim PreviousValue

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub

    If Target.Value <> PreviousValue Then
        Application.EnableEvents = False
        Sheets("log").Range("A2").Insert xlShiftDown
        Sheets("log").Range("A2").Value = _
        Application.UserName & " changed cell " & Target.Address _
                                          & " from " & PreviousValue & " to " & Target.Value & " at " & Now() & " for file: " & ActiveWorkbook.FullName
        Application.EnableEvents = True
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    PreviousValue = Target.Value
End Sub

 

[BrianMH]

I can't reproduce the error you are getting but if you want it to write to the top row of the log you need to do this instead.

Dim PreviousValue

Private Sub Worksheet_Change(ByVal Target As Range)




    If Target.Value <> PreviousValue Then
        Sheets("log").Rows(1).Insert
        Sheets("log").Cells(1, 1).Value = _
        Application.UserName & " changed cell " & Target.Address _
        & " from " & PreviousValue & " to " & Target.Value & " at " & Now() & " for file: " & ActiveWorkbook.FullName
    End If
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    PreviousValue = Target.Value
End Sub

Edit: yeah forgot about it erroring with more than 1 cell. That's why Norie added the count>1

 

[drmingle]

Thanks guys for the quick response....

Norie :: When I copy the sheet to another part of the workbook I get the following error:

Run-Time error '6': Overflow

It's buggin on the code below in red...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
PreviousValue = Target.Value
End Sub

 

[Norie]

Are you copying the sheet or cells on the sheet?

 

[drmingle]

Are you copying the sheet or cells on the sheet?

I am selecting the entire sheet and pasting it as a new sheet within the same workbook.

 

[Norie]

Sorry I don't follow.

Are you right clicking the sheet tab, selecting Move or Copy... and then selecting to copy the sheet?

 

[shg]

If Target.Cells.CountLarge > 1 Then

 

[drmingle]

If Target.Cells.CountLarge > 1 Then

shg,

That did the trick...

Norie,

Thank you for hanging in there with me...

 

[shg]

You're welcome