Excel VBA, code in worksheet not working

Question

I have the following code in sheet1 (note - this code is in the wroksheet object, not the workbook object or a module):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Integer
r = ActiveCell.Row
Cells(r - 1, 7).Value = Now()
ActiveWorkbook.save
End Sub

Can someone tell me why: 1. the ActiveWorkbook.save doesnt work above - it gets stuck in an infinite loop instead; 2. why I cant step throught the code by just pressing F8

I tried to put the ActiveWorkbook.save in a separate module and then call that function from the code in the worksheet but that got stuck in an infinite loop as well.

By updating the worksheet you again trigger the change event, and so on to create an infinite loop. You need to disable events using Application.EnableEvents = False just before you do the update, then switch events back on after. — Tim Williams
– Tim Williams, Commented Dec 14, 2012 at 17:19
Thnks for the quick reply, if I put Application.EnableEvents = False right before Cells(r - 1, 7).Value = Now(), then Cells(r - 1, 7).Value = Now() does not work. I dont get infinite loop if I take out the ActiveWorkbook.save, so is this line trigering a change event (does saving a file constitute a change event) — user1894469
– user1894469, Commented Dec 14, 2012 at 17:39
Do you have any other event handling in your code? You may have inadvertently switched off event handling while working on your code (the setting is persistent), so try running Application.EnableEvents = True in the Immediate pane of the VBE. Or put a breakpoint in your code so you can be sure it's actually getting triggered. — Tim Williams
– Tim Williams, Commented Dec 14, 2012 at 17:49
ok, so I got the application.enableevents = true working, but I am back to square1. now I have application.enableevents = false right above ActiveWorkbook.save and then I have application.enableevents = false right after it, but still getting stuck in a loop. Also what I find surprising is that if I just take the line ActiveWorkbook.save out of my code above, it works fine, doesnt get stuck a in a loop, though logically thinking I should expect it to because as soon as Cells(r - 1, 7).Value = Now() is executed it should call the change event. but it works fine. — user1894469
– user1894469, Commented Dec 14, 2012 at 20:31

nutsch · Accepted Answer · 2012-12-14 17:20:59Z

3

You need to disable events to avoid the infinite loop

Private Sub Worksheet_Change(ByVal Target As Range)
application.enableevents=false
    Cells(target.row - 1, 7).Value = Now()
application.enableevents=true

ActiveWorkbook.save
End Sub

answered Dec 14, 2012 at 17:20

nutsch

5,9622 gold badges23 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1894469 Over a year ago

I have copied and pasted your code exactly as given, I dont get the infinite loop, but then Cells(target.row - 1, 7).Value = Now() is not working

brettdj · Accepted Answer · 2012-12-14 23:59:20Z

1

The infinite loop is caused because when you update the cell with the current date this is causing a worksheet change event which is calling the event code again. You need to disable events as shown below:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim r As Integer
r = ActiveCell.Row

Application.EnableEvents = False    
Cells(r - 1, 7).Value = Now()    
Application.EnableEvents = True    
ActiveWorkbook.Save

End Sub

edited Dec 14, 2012 at 23:59

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

answered Dec 14, 2012 at 17:23

InContext

2,52116 silver badges25 bronze badges

2 Comments

user1894469 Over a year ago

Hello, I opened a new workbook and copied your code exactly to the sheet 1 object and ran it. It doesnt get stuck in a loop any more, but also, it doesnt do anything. In fact if I put a break in the code, this sub is not even being called anymore. Any ideas? I do appreciate your help.

brettdj Over a year ago

@user1894469 if you broke this code part way through, you may have disabled Events and not re-enabled them. in the Immediate window of the VBE, type Application.EnableEvents = True and then try your code again

Collectives™ on Stack Overflow

Excel VBA, code in worksheet not working

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related