BENSON
01-11-2008, 03:59 AM
I have a worksheet that when opened adds the date to the worksheets name see code:
Private Sub Workbook_Open()
ThisWorkbook.Worksheets(1).Name = "CHECK LIST " & Format(Date, "d mmm yy")
My problem is as the worksheets name will be different each day how should I alter the code below to take this in to account.I have mrked the line of code which gives me the problem in red.
Thanks
Public Sub NextCheck()
With Worksheets("CHECK LIST")
If Application.CountA(.Range("C6:E10")) <> .Range("C6:E10").Cells.Count Then
MsgBox "Data incomplete"
End If
End With
Select Case True
Case Time < TimeSerial(10, 5, 0)
mgNextRun = TimeSerial(10, 5, 0)
Case Time < TimeSerial(14, 5, 0)
mgNextRun = TimeSerial(14, 5, 0)
Case Time < TimeSerial(17, 5, 0)
mgNextRun = TimeSerial(17, 5, 0)
Case Else
mgNextRun = Date + 1 + TimeSerial(10, 5, 0)
End Select
Application.OnTime mgNextRun, "NextCheck"
End Sub
Private Sub Workbook_Open()
ThisWorkbook.Worksheets(1).Name = "CHECK LIST " & Format(Date, "d mmm yy")
My problem is as the worksheets name will be different each day how should I alter the code below to take this in to account.I have mrked the line of code which gives me the problem in red.
Thanks
Public Sub NextCheck()
With Worksheets("CHECK LIST")
If Application.CountA(.Range("C6:E10")) <> .Range("C6:E10").Cells.Count Then
MsgBox "Data incomplete"
End If
End With
Select Case True
Case Time < TimeSerial(10, 5, 0)
mgNextRun = TimeSerial(10, 5, 0)
Case Time < TimeSerial(14, 5, 0)
mgNextRun = TimeSerial(14, 5, 0)
Case Time < TimeSerial(17, 5, 0)
mgNextRun = TimeSerial(17, 5, 0)
Case Else
mgNextRun = Date + 1 + TimeSerial(10, 5, 0)
End Select
Application.OnTime mgNextRun, "NextCheck"
End Sub