S
Sean
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 Autpen()
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
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 Autpen()
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