Ambiguous name detected unable to run code

[siddo]

Private Const AuditSheets As String = "|Sheet3|" ' Must start and end with the "|" character
Private Const AuditRange As String = "$C$2:$G$32" ' Change the tracked range as necessary
Private Const LifeSpan As Double = 0.0013 ' 24 hour lifespan
Private NextTime As Date
Private Sub Workbook_Open()

'worksheets to show when macro is enabled

Sheets("Workpaper").Visible = True
Sheets("Configuration and Dashboard").Visible = True
Sheets("Workstep-1").Visible = True
Sheets("Workstep-2").Visible = True
Sheets("Workstep-3").Visible = True
Sheets("Workstep-4").Visible = True
Sheets("Workstep-5").Visible = True
Sheets("Workstep-7").Visible = True
Sheets("Workstep-8").Visible = True
Sheets("Workstep-9").Visible = True
Sheets("Workstep-10").Visible = True
Sheets("Workstep-13").Visible = True
Sheets("Workstep-17").Visible = True
Sheets("system.html").Visible = True
Sheets("specific.html").Visible = True

'worksheet that shows reminder to enable macro

Sheets("Warning").Visible = xlVeryHidden

End Sub
Private Sub Workbook_Open()

' Start the checking
NextTime = Now
Application.OnTime EarliestTime:=NextTime, Procedure:="ThisWorkbook.CheckExpiry"

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'worksheet with reminder

Sheets("Warning").Visible = True

'worksheets to show when macro is enabled

Sheets("Workpaper").Visible = xlVeryHidden
Sheets("Configuration and Dashboard").Visible = xlVeryHidden
Sheets("Workstep-1").Visible = xlVeryHidden
Sheets("Workstep-2").Visible = xlVeryHidden
Sheets("Workstep-3").Visible = xlVeryHidden
Sheets("Workstep-4").Visible = xlVeryHidden
Sheets("Workstep-5").Visible = xlVeryHidden
Sheets("Workstep-7").Visible = xlVeryHidden
Sheets("Workstep-8").Visible = xlVeryHidden
Sheets("Workstep-9").Visible = xlVeryHidden
Sheets("Workstep-10").Visible = xlVeryHidden
Sheets("Workstep-13").Visible = xlVeryHidden
Sheets("Workstep-17").Visible = xlVeryHidden
Sheets("system.html").Visible = xlVeryHidden
Sheets("specific.html").Visible = xlVeryHidden
ThisWorkbook.Save
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.OnTime EarliestTime:=NextTime, Procedure:="ThisWorkbook.CheckExpiry", Schedule:=False

End Sub

Private Sub Workbook_Activate()
Application.CommandBars("Ply").Enabled = False
End Sub
Private Sub Workbook_Deactivate()
Application.CommandBars("Ply").Enabled = True
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim auditSheet As Worksheet
Dim auditCells As Range
Dim auditCell As Range

On Error Resume Next

' Don't worry about audit sheets
If Right$(Sh.Name, 6) = "|Audit" Then Exit Sub

' Check that we need to monitor this sheet
If InStr(1, AuditSheets, "|" & Sh.Name & "|") = 0 Then Exit Sub

' Check to see if any changed cells are tracked
Set auditCells = Application.Intersect(Target, Sh.Range(AuditRange))
If auditCells Is Nothing Then Exit Sub

' Find or add the audit sheet for this sheet
Set auditSheet = Worksheets(Sh.Name & "|Audit")
If auditSheet Is Nothing Then
    Set auditSheet = Worksheets.Add(after:=Sh)
    auditSheet.Name = Sh.Name & "|Audit"
    auditSheet.Visible = xlSheetHidden
End If

' Record the last change date of each cell
Application.EnableEvents = False
For Each auditCell In auditCells
    If auditCell.Value = "" Then
        auditSheet.Range(auditCell.Address).ClearContents
    Else
        auditSheet.Range(auditCell.Address).Value = Now
    End If
Next auditCell
Application.EnableEvents = True

End Sub
Public Sub CheckExpiry()

Dim checkSheets As Variant
Dim auditCell As Range
Dim checkSheet As Worksheet
Dim auditSheet As Worksheet
Dim i As Long

On Error Resume Next

Application.EnableEvents = False

checkSheets = Split(Mid$(AuditSheets, 2), "|")
For i = 0 To UBound(checkSheets)
    Set checkSheet = Nothing
    Set checkSheet = Worksheets(checkSheets(i))
    If Not (checkshet Is Nothing) Then
        Set auditSheet = Nothing
        Set auditSheet = Worksheets(checkSheet.Name & "|Audit")
        If Not (auditSheet Is Nothing) Then
            For Each auditCell In auditSheet.Range(AuditRange)
                If auditCell.Value <> "" Then
                    If (Now - auditCell.Value) >= LifeSpan Then
                        auditCell.ClearContents
                        checkSheet.Range(auditCell.Address).ClearContents
                    End If
                End If
            Next auditCell
        End If
    End If
Next i

Application.EnableEvents = True

NextTime = Now + TimeSerial(0, 1, 0)
Application.OnTime EarliestTime:=NextTime, Procedure:="ThisWorkbook.CheckExpiry"

End Sub

 

[siddo]

It works, Thanks a ton Dante.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.