Hi,
I have got a spreadsheet which has our daily figures into. This has been working ok in an older spreadsheet but we need to recreate the spreadsheet in Office 365 due to compatibility issues.
I've recreated the spreadsheet in the same format however I'm getting an error when I input this formula. It inputs our username and date/time stamp at the end of the row each day to confirm sign off.
I either get stopped at Call CheckDateForExistingEntry(UseDate) or if I remove this line out it stops at Range("A1").Formula = "=TODAY()-1" unless I have =today()-1 already in cell A1.
Can anyone offer me their wisdom on this? Any help would be appreciated
I have got a spreadsheet which has our daily figures into. This has been working ok in an older spreadsheet but we need to recreate the spreadsheet in Office 365 due to compatibility issues.
I've recreated the spreadsheet in the same format however I'm getting an error when I input this formula. It inputs our username and date/time stamp at the end of the row each day to confirm sign off.
Code:
Sub ConfirmEnter()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Range("A1").Formula = "=TODAY()-1"
UseDate = Range("A1").Value
Range("A1").ClearContents
Response = MsgBox("Confirm entry for - " & UseDate, vbYesNoCancel + vbQuestion, "Confirm Entry")
If Response = 2 Then
End
ElseIf Response = 7 Then
NewDate = InputBox("Enter date to confirm entry for...(dd/mm/yy)", "Custom Date")
If IsDate(NewDate) = False Then
Do Until IsDate(NewDate) = True
NewDate = InputBox("The date you entered has not been recognised - please try again.....or enter 'abort' to quit.", "Try Again")
If UCase(NewDate) = "ABORT" Then End
Loop
End If
UseDate = CDate(NewDate)
End If
Call CheckDateForExistingEntry(UseDate)
Sheets("Summary").Unprotect Password:="4010"
RangeDateRow = Application.Match(CLng(UseDate), Range("A1:A50000"), 0)
LastColumn = Range("IV6").End(xlToLeft).Column
UserId = Environ("UserName")
Cells(RangeDateRow, LastColumn - 6).Formula = UserId
Cells(RangeDateRow, LastColumn - 5).Formula = "=NOW()"
Cells(RangeDateRow, LastColumn - 5).Copy
Cells(RangeDateRow, LastColumn - 5).PasteSpecial xlPasteValues
Range("B7").Select
Application.CutCopyMode = False
Sheets("Summary").Protect Password:="4010"
End Sub
I either get stopped at Call CheckDateForExistingEntry(UseDate) or if I remove this line out it stops at Range("A1").Formula = "=TODAY()-1" unless I have =today()-1 already in cell A1.
Can anyone offer me their wisdom on this? Any help would be appreciated
Last edited by a moderator: