Ambiguous name detected unable to run code

S

siddo

Board Regular
Joined
May 26, 2020
Messages
106
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
VBA Code: 
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
You have repeated these events

Rich (BB code): 
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

You should unify them, maybe like this:
VBA Code: 
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

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

________________________________________________________________________________________
The same for this:
Rich (BB code): 
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

Try:
VBA Code: 
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
  
  Application.OnTime EarliestTime:=NextTime, Procedure:="ThisWorkbook.CheckExpiry", Schedule:=False
End Sub
 
Upvote 0
DanteAmor said:
You have repeated these events

Rich (BB code): 
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

You should unify them, maybe like this:
VBA Code: 
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

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

________________________________________________________________________________________
The same for this:
Rich (BB code): 
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

Try:
VBA Code: 
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

  Application.OnTime EarliestTime:=NextTime, Procedure:="ThisWorkbook.CheckExpiry", Schedule:=False
End Sub
Click to expand...
Tried it before, it gives an error Application on time object failed
1590586121471.png
 
Last edited:
Upvote 0
You cannot have two events with the same name, you must review the logic of what your process needs to unite the codes.

In which line do you have the problem?
If you have an error on this line:
VBA Code: 
Application.OnTime EarliestTime:=NextTime, Procedure:="ThisWorkbook.CheckExpiry", Schedule:=False

Before that line put this instruction:
VBA Code: 
On Error Resume Next
Application.OnTime EarliestTime:=NextTime, Procedure:="ThisWorkbook.CheckExpiry", Schedule:=False
 
Upvote 0
DanteAmor said:
You cannot have two events with the same name, you must review the logic of what your process needs to unite the codes.

In which line do you have the problem?
If you have an error on this line:
VBA Code: 
Application.OnTime EarliestTime:=NextTime, Procedure:="ThisWorkbook.CheckExpiry", Schedule:=False

Before that line put this instruction:
VBA Code: 
On Error Resume Next
Application.OnTime EarliestTime:=NextTime, Procedure:="ThisWorkbook.CheckExpiry", Schedule:=False
Click to expand...
So what im trying to do is first im hiding all the sheets to force user to enable the macro then i want excel to auto delete itself or clear the contents after the defined interval
 
Upvote 0
Press Debug button, and tell me which line is highlighted.
 
Upvote 0
DanteAmor said:
Press Debug button, and tell me which line is highlighted.
Click to expand...
Thanks for the help Dante, The issue was this code only clears the stuff which one writes after opening the file not the which already exists, I think ill need to change the code..You have any code in mind to autodelete the excel file on its own
 
Upvote 0
siddo said:
You have any code in mind to autodelete the excel file on its own
Click to expand...

Try this
VBA Code: 
Sub test()
    Application.DisplayAlerts = False
    With ActiveWorkbook
      .ChangeFileAccess xlReadOnly
      Kill .FullName
      .Close
    End With
    Application.DisplayAlerts = True
End Sub
 
Upvote 0
DanteAmor said:
Try this
VBA Code: 
Sub test()
    Application.DisplayAlerts = False
    With ActiveWorkbook
      .ChangeFileAccess xlReadOnly
      Kill .FullName
      .Close
    End With
    Application.DisplayAlerts = True
End Sub
Click to expand...
Is It possible to schedule a time for the delete for eg current time + Defined time and when it hits the default time it should be deleted also where should i paste the above code, in the module or in the worksheet
 
Upvote 0
siddo said:
Is It possible to schedule a time for the delete for eg current time + Defined time
Click to expand...

It's an example. with the following you open the book and it is killed in 5 seconds.

Put the following code in the events of thisworkbook:
VBA Code: 
Private Sub Workbook_Open()
  Application.OnTime Now + TimeValue("00:00:05"), "AutoKill"
End Sub

In a module:
VBA Code: 
Sub AutoKill()
  Application.DisplayAlerts = False
  With ActiveWorkbook
    .ChangeFileAccess xlReadOnly
    Kill .FullName
    .Close
  End With
  Application.DisplayAlerts = True
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
ActiveX Checkbox fails after sheetname change
Replies
4
Views
158
bobsan42
bobsan42
B
  • Solved
Combining vba
Replies
1
Views
372
Joe4
Joe4
I
  • Question
Userform does not show leading 0 for telephone number
Replies
1
Views
76
ipbr21054
I
N
  • Solved
VBA Question
Replies
3
Views
144
RoryA
RoryA
M
  • Question
Creating and emailing Excel sheet with only visible data
Replies
0
Views
215
mecerrato
M

Forum statistics

Threads
1,215,375
Messages
6,124,588
Members
449,174
Latest member
chandan4057

Share this page

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
Back
Top