Run-time error '1004': Out of stack space

[MACATTACK_VA]

Hello all - 'long-time visitor, first-time poster.

I've created a macro in protected worksheets that enters the date and time in the "I" column whenever a cell in the associated row has been modified.

This was working great when I left the office on Friday but this morning whenever I try to modify a cell it stops me and gives me an error message that reads Run-time error '1004': Out of stack space.

The code I've inserted for two worksheets that populate the data into another dozen worksheets is:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row > 1 Then Cells(Target.Row, "i") = Now()
End Sub

The portion in red is what's highlighted when I go to the debugger.

Any thoughts/assistance would be greatly appreciated!

 

[nuked]

[FONT=courier new]Private Sub Worksheet_Change(ByVal Target As Range)
application.enableevents = FALSE
If Target.Row > 1 Then Cells(Target.Row, "i") = Now().
application.enableevents = TRUE
End Sub

[/FONT]

Before, when you were assigning Now(), it was triggering the change event again.


and again, and again, and .....

You get the point

 

[MACATTACK_VA]

Thanks, Nuked.

I'm getting a Compile error: Syntax error message when I enter data into the cells now;

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Row > 1 Then Cells(Target.Row, "i") = Now().
Application.EnableEvents = True
End Sub

Any thoughts?

 

[nuked]

Remove the fullstop after Now()?

 

[MACATTACK_VA]

I probably shoud've mentioned that I'm a novice at VBA

I don't get the error messages anymore and I'm able to insert data into the unlocked cells but the date isn't populating into the "I" column now. I don't see any macros when I hit the 'macro' button in the developer ribbon either; this was the case before (on Friday) but it still ran the code. 'Not sure if this has anything to do with it. Also, can you let me know if I replace 'private' with 'public' it would run the code for other users - i.e. if they were working on a version saved on a SharePoint site? Thanks for your help so far - 'hoping I can get this working today!

 

[nuked]

It works for me. Are the cells in column I protected?

The reason it doesn't show on the Macro list is because it's not a Macro. It's some code that we've placed on the Change Event of the worksheet in question. As it stands, it will work for whoever uses the spreadsheet. No need to make the declaration public.

 

[nuked]

Try this if Column I is protected:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect "YourPassword"
Application.EnableEvents = False
If Target.Row > 1 Then Cells(Target.Row, "i") = Now()
Application.EnableEvents = True
ActiveSheet.Protect "YourPassword"
End Sub

 

[MACATTACK_VA]

Column "I" was protected (locked but not hidden). Removing the lock and removing the full stop after Now() worked.

Thanks, 'nuked'!!! I'll be sure to add another post if something whacky comes up with this code again but it's working so far!

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Row > 1 Then Cells(Target.Row, "i") = Now()
Application.EnableEvents = True
End Sub