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

(Posted before in 'application error' but that seems to be the wrong
discussion)

Mark,

If the posted code is in the "input" sheet module then the code line...
"If ActiveSheet.Name = "Input" Then" is not required.
That also means that your "else" code is never run.

To prevent the circular reference, try adding the lines...
Application.EnableEvents = False at the top of your code.
Application.EnableEvents = True at the bottom of your code.

The second line should also go in your error handler.

Also, depending on your unposted code, it appears that you do not
need the For/Next loop. Your code indicates that you are only
changing one sheet - why loop thru all of them?

Regards,
Jim Cone
San Francisco, USA


"Mark ten berge"

wrote in message

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

(Posted before in 'application error' but that seems to be the wrong
discussion)

Thanks Jim,

This works.
I'm using the for-loop because the summary sheet is not always there. The IF
is required because it's data will be changed while generating the summary
sheets (it summarises the outcomes of all scenarios on the input sheet by
changing the selected scenario and copying the results per scenario).

"Jim Cone" wrote:

Mark,

If the posted code is in the "input" sheet module then the code line...
"If ActiveSheet.Name = "Input" Then" is not required.
That also means that your "else" code is never run.

To prevent the circular reference, try adding the lines...
Application.EnableEvents = False at the top of your code.
Application.EnableEvents = True at the bottom of your code.

The second line should also go in your error handler.

Also, depending on your unposted code, it appears that you do not
need the For/Next loop. Your code indicates that you are only
changing one sheet - why loop thru all of them?

Regards,
Jim Cone
San Francisco, USA


"Mark ten berge"

wrote in message

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

(Posted before in 'application error' but that seems to be the wrong
discussion)