I had some code running on opening of a file and it worked great then
I introduced a further bit and its debugging, hoping someone can help.
Full code is below

The part its debuging on is "For Each sh In ActiveWorkbook.Worksheets"
and "Password", saying variables are not defined, yet I have the same
code with ThisWorkbook as Private Sub Workbook_Open() and it works
fine. Guess its simple but I can't understand it

Thanks


Option Explicit
Sub Auto_Open()

Application.ScreenUpdating = False


Dim OLKApp As Outlook.Application
Dim WeStartedIt As Boolean


For Each sh In ActiveWorkbook.Worksheets
On Error Resume Next
sh.Unprotect Password = "1234"
sh.Unprotect
On Error GoTo 0
sh.Activate
sh.Range("A1").Select
Next sh
With ActiveWorkbook.Worksheets("Week")
.Activate

Application.GoTo Range("C6"), True
Range("C6").Activate


ActiveWindow.Zoom = 75
End With




On Error Resume Next
Set OLKApp = GetObject(, "Outlook.Application")
On Error GoTo 0
If OLKApp Is Nothing Then
Set OLKApp = CreateObject("Outlook.Application")
If OLKApp Is Nothing Then
' can't create app
' error mesage then exit
MsgBox "Can't Get Outlook"
Exit Sub
End If
WeStartedIt = True
Else
WeStartedIt = False
End If


Dim OkToCallMacro As Boolean
OkToCallMacro = False
Select Case Weekday(Date)
Case vbMonday To vbFriday
If Time = TimeSerial(9, 49, 0) _
And Time < TimeSerial(9, 52, 0) Then
OkToCallMacro = True
End If
Case Is = vbSaturday, vbSunday
If Time = TimeSerial(9, 49, 0) _
And Time < TimeSerial(9, 52, 0) Then
OkToCallMacro = True
End If
End Select

If OkToCallMacro Then
Application.WindowState = xlMinimized
Call RefreshHOBOSales
Call Copy_Paste

If Workbooks.Count = 1 Then
'only this workbook is open
ThisWorkbook.Save
'close the application
'(which will close thisworkbook)
Application.Quit
Else
ThisWorkbook.Close savechanges:=True
End If
End If

If WeStartedIt = True Then
OLKApp.Quit

End If

End Sub

You have to declare the variable sh as you have Option Explicit.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Sean" wrote in message
ps.com...
I had some code running on opening of a file and it worked great then
I introduced a further bit and its debugging, hoping someone can help.
Full code is below

The part its debuging on is "For Each sh In ActiveWorkbook.Worksheets"
and "Password", saying variables are not defined, yet I have the same
code with ThisWorkbook as Private Sub Workbook_Open() and it works
fine. Guess its simple but I can't understand it

Thanks


Option Explicit
Sub Auto_Open()

Application.ScreenUpdating = False


Dim OLKApp As Outlook.Application
Dim WeStartedIt As Boolean


For Each sh In ActiveWorkbook.Worksheets
On Error Resume Next
sh.Unprotect Password = "1234"
sh.Unprotect
On Error GoTo 0
sh.Activate
sh.Range("A1").Select
Next sh
With ActiveWorkbook.Worksheets("Week")
.Activate

Application.GoTo Range("C6"), True
Range("C6").Activate


ActiveWindow.Zoom = 75
End With




On Error Resume Next
Set OLKApp = GetObject(, "Outlook.Application")
On Error GoTo 0
If OLKApp Is Nothing Then
Set OLKApp = CreateObject("Outlook.Application")
If OLKApp Is Nothing Then
' can't create app
' error mesage then exit
MsgBox "Can't Get Outlook"
Exit Sub
End If
WeStartedIt = True
Else
WeStartedIt = False
End If


Dim OkToCallMacro As Boolean
OkToCallMacro = False
Select Case Weekday(Date)
Case vbMonday To vbFriday
If Time = TimeSerial(9, 49, 0) _
And Time < TimeSerial(9, 52, 0) Then
OkToCallMacro = True
End If
Case Is = vbSaturday, vbSunday
If Time = TimeSerial(9, 49, 0) _
And Time < TimeSerial(9, 52, 0) Then
OkToCallMacro = True
End If
End Select

If OkToCallMacro Then
Application.WindowState = xlMinimized
Call RefreshHOBOSales
Call Copy_Paste

If Workbooks.Count = 1 Then
'only this workbook is open
ThisWorkbook.Save
'close the application
'(which will close thisworkbook)
Application.Quit
Else
ThisWorkbook.Close savechanges:=True
End If
End If

If WeStartedIt = True Then
OLKApp.Quit

End If

End Sub