I seem to have several issues with a sign in log I created for work.
Problem 1. We need to open the master file and auto date Cell B1 with current date with overwrite protection if file is reopened at a later date.
Problem 2. Once the file is renamed with the current daily file name from above we need the new workbook to be auto saved every 5 minutes while it is opened.
Problem 3. On Close for the workbook to be saved before closing.
I have tried to patch this code from other codes I have made and some from other posts. I have the code working except for these 3 issues. Any help would be appreciated greatly !!
This is code from ThisWoorkBook.
'Auto Date Sign In Log Cell "B1"
Private Sub Worksheet_Activate()
If Range("B1").Value <= 0 Then Range("B1").Value = Date
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'ThisWorkbook Module code!
ActiveWorkbook.Save
End Sub
Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:05:00"), "SaveWb"
End Sub
'Working Code For Sign In Log
Sub SignInDailyLogSave()
'Check for Year & Month folders Exists, if not Then create
Dim strPath As String
strPath = "E:\Shared\xxxxxx\Sign In Logs\" & Format(Date, "yyyy") & "\"
If Dir(strPath, vbDirectory) = "" Then MkDir strPath
strPath = strPath & Format(Date, "MM") & " " & Format(Date, "YYYY") & "\"
If Dir(strPath, vbDirectory) = "" Then MkDir strPath
'Sign In Log Daily File Save Code
ActiveWorkbook.SaveAs Filename:= _
strPath & Format(Now, "MM") & "-" & Format(Now, "DD") & "-" & Format(Now, "YYYY") & " Sign-In-Log", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub
Sub RunDateWorkbook()
'Auto Date Sign In Log Cell "B1"
Private Sub Worksheet_Activate()
If Range("B1").Value <= 0 Then Range("B1").Value = Date
End Sub
'ThisWorkbook Module code!
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWorkbook.Save
End Sub
'This Autosaves Workbook every 5 minutes
Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:05:00"), "SaveWb"
End Sub
This is the code from workbookSignIn page in workbook:
Private Sub Worksheet_Change(ByVal Target As Range)
'Timestamp coloumns "F" and "G" non overwriting date and
'clear coloumn "H" if "L" has a value of "BT" or "TA"
'If value of col L equals "RLCC" then col H ="CC"
'If value of col L equals "RLMO" then col H ="MO"
Dim n As Long
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
On Error GoTo EndItAll
Application.EnableEvents = False
n = Target.Row
'When entering data in a cell in Col L, timestamp it in col G same row
If Target.Column = 12 Then
If Range("G" & n).Value = "" Then Range("G" & n).Value = Now
'Check cell in Col H in same row, If cell in Col L same row, If it has
'Value of "BT" Or "TA" then clear cell H in same row
If Target.Value = "TA" Or Target.Value = "BT" Then Range("H" & n).ClearContents
'If value of col L equals "RLCC" then col H ="CC"
If Target.Value = "RLCC" Then Range("H" & n) = "CC"
'If value of col L equals "RLFW" then col H ="FW"
If Target.Value = "RLFW" Then Range("H" & n) = "FW"
'If value of col L equals "RLMO" then col H ="MO"
If Target.Value = "RLMO" Then Range("H" & n) = "MO"
End If
'When entering data in a cell in Col A, timestamp it in col F same row
If Target.Column = 1 Then
If Range("F" & n).Value = "" Then Range("F" & n).Value = Now
End If
EndItAll:
Application.EnableEvents = True
End Sub
Sub SaveWb()
ThisWorkbook.Save
Application.OnTime Now + TimeValue("00:05:00"), "SaveWb"
End Sub
Thanks again for all the help with this.
Please forgive the code organization as I am new to this VBA.
Bookmarks