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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,018
Messages
5,834,976
Members
430,331
Latest member
Syed Yasir Hannan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top