I need help with this error. I get the error on line
"ws.Range("E7").Value = mondaysDate(Weekday(Date)) - 7"
The function run fine, its just that the erroe comes up when control is
returned to the subroutine. And I don't know what the problem is. Any help
will be appreciated.
Thanks.

Private Sub Workbook_Open()
Dim ws As Worksheet
Const PWORD As String = "Ayo"
Application.ScreenUpdating = False

If Me.Name = "Northeast AAV Project Outlook_ver2.xls" Then
If Weekday(Date) = 2 Then
For Each ws In Worksheets
If ws.Name <> "BO Download" Then
ws.Visible = True
ws.Range("E7").Value = Date - 7
ws.Range("F7").Value = Date
ws.Range("J6").Value = Date
End If
Next
ElseIf Weekday(Date) <> 2 Then
For Each ws In Worksheets
If ws.Name <> "BO Download" Then
ws.Visible = True
ws.Range("F7").Value = Date
ws.Range("E7").Value = mondaysDate(Weekday(Date)) - 7
ws.Range("J6").Value = mondaysDate(Weekday(Date))
End If
Next
End If
Else
Exit Sub
End If
Application.ScreenUpdating = True
End Sub

Function mondaysDate(dayNumber) As Date
Select Case dayNumber
Case 1
mondayDate = Date - 6
Case 3
mondayDate = Date - 1
Case 4
mondayDate = Date - 2
Case 5
mondayDate = Date - 3
Case 6
mondayDate = Date - 4
Case 7
mondayDate = Date - 5
End Select
End Function

Ayo

--Within the function you have mentioned 'mondayDate' and not 'mondayDates'.
The 's' is missing. Use the Option Explicit statement on top of your module
to avoid such errors

--To find previous mondays date you can try the below
MsgBox Date - (Weekday(Date)) - 5

If this post helps click Yes
---------------
Jacob Skaria


"Ayo" wrote:

> I need help with this error. I get the error on line
> "ws.Range("E7").Value = mondaysDate(Weekday(Date)) - 7"
> The function run fine, its just that the erroe comes up when control is
> returned to the subroutine. And I don't know what the problem is. Any help
> will be appreciated.
> Thanks.
>
> Private Sub Workbook_Open()
> Dim ws As Worksheet
> Const PWORD As String = "Ayo"
> Application.ScreenUpdating = False
>
> If Me.Name = "Northeast AAV Project Outlook_ver2.xls" Then
> If Weekday(Date) = 2 Then
> For Each ws In Worksheets
> If ws.Name <> "BO Download" Then
> ws.Visible = True
> ws.Range("E7").Value = Date - 7
> ws.Range("F7").Value = Date
> ws.Range("J6").Value = Date
> End If
> Next
> ElseIf Weekday(Date) <> 2 Then
> For Each ws In Worksheets
> If ws.Name <> "BO Download" Then
> ws.Visible = True
> ws.Range("F7").Value = Date
> ws.Range("E7").Value = mondaysDate(Weekday(Date)) - 7
> ws.Range("J6").Value = mondaysDate(Weekday(Date))
> End If
> Next
> End If
> Else
> Exit Sub
> End If
> Application.ScreenUpdating = True
> End Sub
>
> Function mondaysDate(dayNumber) As Date
> Select Case dayNumber
> Case 1
> mondayDate = Date - 6
> Case 3
> mondayDate = Date - 1
> Case 4
> mondayDate = Date - 2
> Case 5
> mondayDate = Date - 3
> Case 6
> mondayDate = Date - 4
> Case 7
> mondayDate = Date - 5
> End Select
> End Function
>