Inserting A Record With Vba

•

Here is how I did it: 1. Open Visual Basic Editor (Alt+F11) and select Insert>Module 2. Paste the following code in Module1 that was just created Function UserNameWindows() As String UserNameWindows = Environ("USERNAME") End Function Function InsertHistoryRow() Dim uname As String uname = UserNameWindows() Application.ScreenUpdating = False Sheets(3).Select Sheet3.Rows("1").Select Sheet3.Rows("1").Insert Shift:=xlDown Sheet3.Range("A1").Value = uname Sheet3.Range("B1").Value = Date Sheet3.Range("C1").Value = Time Sheets(1).Select Application.ScreenUpdating = True End Function 3. Add one line to your BeforeClose BeforeSave event handlers InsertHistoryRow Like this: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If ThisWorkbook.Saved = True Then Sheet1.Range("B1").Value = Date Sheet1.Range("C1").Value = Time InsertHistoryRow End If End Sub This will insert a new row at the top of Sheet3 each time the workbook is saved. I did also change the line of code "If ThisWorkbook.Saved = True Then" so that it happens when the save event is fired.

......................................... If you wanted a header row on sheet3 for the history you just have to change the rows in the InsertRowHistory function. Function InsertHistoryRow() Dim uname As String

uname = UserNameWindows() Application.ScreenUpdating = False Sheets(3).Select Sheet3.Rows("2").Select Sheet3.Rows("2").Insert Shift:=xlDown Sheet3.Range("A2").Value = uname Sheet3.Range("B2").Value = Date Sheet3.Range("C2").Value = Time Application.ScreenUpdating = True End Function