M
Mark ten berge
Hi,
I've got a workbook with several sheets, one is called 'Input", another
"Summarysheet". When the user changes a cell on "Input", the summary needs to
be recalculated, and Summarysheet!A2 is changed into such text.
So the code on the input sheet module is below
Now the question:
If the user finishes his input with a press on the Enter key, all is fine.
If the user finishes his input by clicking another cell, a circular
reference error is displayed.
Anyone suggestions how to prevent this?
Many thanks in advance!
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws_Summ As Excel.Worksheet
If ActiveSheet.Name = "Input" Then
For Each ws_Summ In Excel.ActiveWorkbook.Sheets
If ws_Summ.Name = "SummarySheet" Then
If ws_Summ.Cells(2, 1) <> "Summary sheet needs recalculation!" Then
ws_Summ.Cells(2, 1) = "Summary sheet needs recalculation!"
ws_Summ.Cells(2, 1).Font.Color = RGB(255, 255, 255)
ws_Summ.Cells(2, 1).Font.Bold = True
ws_Summ.Cells(2, 1).Interior.Color = RGB(255, 0, 0)
Exit For
End If
End If
Next
Else
' changes made by code...
End If
End Sub
I've got a workbook with several sheets, one is called 'Input", another
"Summarysheet". When the user changes a cell on "Input", the summary needs to
be recalculated, and Summarysheet!A2 is changed into such text.
So the code on the input sheet module is below
Now the question:
If the user finishes his input with a press on the Enter key, all is fine.
If the user finishes his input by clicking another cell, a circular
reference error is displayed.
Anyone suggestions how to prevent this?
Many thanks in advance!
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws_Summ As Excel.Worksheet
If ActiveSheet.Name = "Input" Then
For Each ws_Summ In Excel.ActiveWorkbook.Sheets
If ws_Summ.Name = "SummarySheet" Then
If ws_Summ.Cells(2, 1) <> "Summary sheet needs recalculation!" Then
ws_Summ.Cells(2, 1) = "Summary sheet needs recalculation!"
ws_Summ.Cells(2, 1).Font.Color = RGB(255, 255, 255)
ws_Summ.Cells(2, 1).Font.Bold = True
ws_Summ.Cells(2, 1).Interior.Color = RGB(255, 0, 0)
Exit For
End If
End If
Next
Else
' changes made by code...
End If
End Sub