Screen Updating on BeforeSave

[KateNash]

Hi

I have some code which updates the user name and date in an audit sheet each time a workbook is saved. It's working great apart from one thing. When I save, the workbook switches to the Audit sheet, even though the Application.ScreenUpdating = False. Is there a way to have this code run without moving the user from the active cell they were in when they clicked the save button?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Application.ScreenUpdating = False

Dim UserChange
Dim DateChange

UserChange = Application.UserName
DateChange = Date & "  " & Time
       
    
    Sheets("Audit").Select
    Range("a65536").End(xlUp).Offset(1, 0).Activate
    ActiveCell.Value = UserChange
      
    Sheets("Audit").Select
    Range("b65536").End(xlUp).Offset(1, 0).Activate
    ActiveCell.Value = DateChange
        
    ActiveWorkbook.Save
   Application.ScreenUpdating = True

End Sub

 

[Andrew Poulsom]

Just avoid the unnecessary Selects ( and the then unnecessary ScreenUpdating):

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 
   Dim UserChange 
   Dim DateChange 
   UserChange = Application.UserName 
   DateChange = Date & "  " & Time 
   Sheets("Audit").Range("a65536").End(xlUp).Offset(1, 0).Value = UserChange 
   Sheets("Audit").Range("b65536").End(xlUp).Offset(1, 0).Value = DateChange 
   ActiveWorkbook.Save 
End Sub

 

[KateNash]

Thanks - works great but for 1 thing - why is it now putting 2 rows the same on the audit sheet each time I save?

 

[BrianB]

Screenupdating just stops changes being show on screen. When set to true it shows where you have finally got to.

Using Select changes the active sheet and makes it the currently visible one.
One way of doing what you want would be to add a line of code at the end to Select the sheet the user started from. Another way is not to use Select at all and replace your appropriate code with :-

'PARTIAL CODE
    Set ws = Worksheets("Audit")
    nextrow = ws.Range("A65536").End(xlUp).Row + 1
    ws.Cells(nextrow, "A").Value = UserChange
    ws.Cells(nextrow, "B").Value = DateChange

 

[KateNash]

Thanks Brian - both ways work equally well - it was the ActiveWorkbook.Save which was duplicating the save process - once I removed that it only put one line in.

Thanks for your help!

Kate