Continuous Macro with Activate and Change - stack space error

[treepanic]

Hello, all! I have a very similar situation except I can't get the second part to work. I have an order form with certain items that need to be ordered in quantities of six. The "Worksheet_Activate()" bit works; if I click into 'Sheet 2' and back into 'Sheet 1', the macro auto-runs and rounds the quantities in the select cells to multiples of 6.

Here's my code:

Private Sub Worksheet_Activate() 'This part is working fine
For Each cell In [N46:N51, N56:N59]
If cell = " " Then Exit Sub
cell.Value = Application.MRound(cell.Value, 6)
Next cell
End Sub


Private Sub Worksheet_Change(ByVal Target As Range) 'This part isn't working
For Each cell In [N46:N51, N56:N59]
If cell = " " Then Exit Sub
cell.Value = Application.MRound(cell.Value, 6)
Next cell
End Sub

The error I receive is "Run-time error '28': Out of stack space"; when I click to debug, "For Each cell In [N46:N51, N56:N59]" (under 'Worksheet_Change...") is highlighted.

Any ideas?

Thanks!

 

[Andrew Poulsom]

Because your procedures are changing the cells you need to disable the Worksheet_Change event procedure. Put this at the beginning of your procedures:

Application.EnableEvents = False

and set it back to True at the end.

 

[treepanic]

Awesome! For the record, this is my final, working script:

Private Sub Worksheet_Activate() 'This part is working fine
For Each cell In [N46:N51, N56:N59]
If cell = " " Then Exit Sub
cell.Value = Application.MRound(cell.Value, 6)
Next cell
End Sub


Private Sub Worksheet_Change(ByVal Target As Range) 'This part isn't working
Application.EnableEvents = False
For Each cell In [N46:N51, N56:N59]
If cell = " " Then Exit Sub
cell.Value = Application.MRound(cell.Value, 6)
Next cell
Application.EnableEvents = True
End Sub

Thanks for all your help!

 

[Andrew Poulsom]

Your Worksheet_Change event procedure is still being called unnecessarily by Worksheet_Activate.

 

[Andrew Poulsom]

Please don't ask the same question multiple times:

http://www.mrexcel.com/forum/excel-questions/95341-running-macro-continuously.html