Screen Updating on BeforeSave

KateNash

Board Regular
Joined
Sep 23, 2004
Messages
86
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?

Code:
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
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Just avoid the unnecessary Selects ( and the then unnecessary ScreenUpdating):

Code:
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

Board Regular
Joined
Sep 23, 2004
Messages
86
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

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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 :-
Code:
'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

Board Regular
Joined
Sep 23, 2004
Messages
86
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,670
Messages
5,573,569
Members
412,537
Latest member
Mohamed_5966
Top