none
runtime error 424: Object Required RRS feed

  • Question

  • Hi all, error occurred when i click on any cells. What i want to do is to detect the changes in cell, once detected, it will have a msgbox showing that there is a change in the cell value.

    This is my code:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     Dim r As Range
        For Each r In Target.Cells
            'Has the value actually changed?
            If r.Value <> Sheet1_Mirror.Range(r.Address).Value Then
                'Yes it has. Do whatever needs to be done.
                MsgBox "Value of cell " And r.Address And " was changed. " And vbCrLf _
                    And "Was: " And vbTab And Sheet1_Mirror.Range(r.Address).Value And vbCrLf _
                    And "Is now: " And vbTab And r.Value
                'Mirror this new value.
                Sheet1_Mirror.Range(r.Address).Value = r.Value
            Else
                'It hasn't really changed. Do nothing.
            End If
        Next
    End Sub

    Friday, May 17, 2013 3:07 AM

Answers

  • I don't know what version of Excel you are using but in Excel 2010 I could not find Sheet1_Mirror in the help reference.  When I did Debug->Compile VBAProject, that is where I got an error.

    I would suggest if you are trying to detect changes in the worksheet you use the

    Worksheet_Change event instead of the Worksheet_SelectionChange event.  That would only fire when an actual change is made to the worksheet.

    If Sheet1_Mirror is another worksheet in the same workbook then the code needs to be

    If r.Value <> WorkSheets("Sheet1_Mirror").Range(r.Address).Value Then

    Friday, May 17, 2013 7:47 AM

All replies

  • I don't know what version of Excel you are using but in Excel 2010 I could not find Sheet1_Mirror in the help reference.  When I did Debug->Compile VBAProject, that is where I got an error.

    I would suggest if you are trying to detect changes in the worksheet you use the

    Worksheet_Change event instead of the Worksheet_SelectionChange event.  That would only fire when an actual change is made to the worksheet.

    If Sheet1_Mirror is another worksheet in the same workbook then the code needs to be

    If r.Value <> WorkSheets("Sheet1_Mirror").Range(r.Address).Value Then

    Friday, May 17, 2013 7:47 AM
  • Thanks Lescott !! I had changed it to Change event handler.
    Friday, May 17, 2013 8:40 AM