CODEWITHSUNDEEP
excelvbaworksheet
user1894469
user1894469

Reputation: 143

Excel VBA, code in worksheet not working

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.

Upvotes: 3

Views: 5958

Answers (2)

InContext
InContext

Reputation: 2501

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

Upvotes: 1

nutsch
nutsch

Reputation: 5962

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

Upvotes: 3

Related Questions