Microsoft Excel - Workbook will not open when called from OnTime macro

I have a macro that is called from the before save event:

Sub ToGlobal()
ThisWorkbook.Activate
Dim strSheet As String
strSheet = Range("TargetSheet").Value
Dim StartPst As String
StartPst = Range("CV").Value
ActiveWorkbook.Worksheets(1).Activate
ActiveSheet.Unprotect Password:="TP"
Application.ScreenUpdating = False
Range("A42:AJ48").Select
Application.CutCopyMode = False
Selection.Copy
Range("A1:AJ7").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.ScreenUpdating = False
ActiveSheet.Protect Password:="TP", DrawingObjects:=False, Contents:=True, Scenarios:= _
True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True
ActiveSheet.EnableSelection = xlNoRestrictions
Range("BB37").Select
Selection.Copy
Application.ScreenUpdating = True
Dim myFile As String, myFolder As String
myFolder = Range("Filepath")
myFile = myFolder & Range("Filename")
If Not IsFolderExists(myFolder) Then
CreateObject("Scripting.FileSystemObject").CreateFolder myFolder
End If
If Not IsFileExists(myFile) Then
MsgBox "The folder path & file " & myFile & " could not be found and the global TP cannot be updated."
UserForm1.Hide
Exit Sub
End If
If ActiveWorkbook.Saved = False And ActiveWorkbook.ReadOnly = False Then
UserForm1.Show
UserForm1.Repaint
Application.DisplayAlerts = False
ActiveWorkbook.Worksheets(1).Activate
Range("A3:AJ7").Select
Selection.Copy
Dim CurrentGlobal As String
CurrentGlobal = Replace(Range("Filename"), "\", "")
Workbooks.Open Filename:=Range("FullName").Text, WriteResPassword:="GTP"
If ActiveWorkbook.ReadOnly = True Then
UserForm1.Hide
MsgBox "Another user has the global TP open. Could not update- please click save again to update the global TP"
ActiveWorkbook.Close
Application.DisplayAlerts = True
Exit Sub
End If
ActiveWorkbook.Worksheets(strSheet).Activate
Range(StartPst).Select
ActiveWorkbook.Worksheets(strSheet).Paste
Application.CutCopyMode = False
Range("A1").Select
ActiveWorkbook.Save
ActiveWindow.Close
Application.DisplayAlerts = True
Range("B3").Select
UserForm1.Hide
End If
End Sub

The macro runs fine when the user clicks save, and also if the user closes the workbook and selects save changes.

However, I have an on time macro which saves and then closes the workbook programatically after 30 seconds. When this happens, the 'ToGlobal' macro run, but breaks at the following point:
ActiveWorkbook.Worksheets(strSheet).Activate
I have found that the 'subscript out of range' error is beause the workbook whose name is contained in the range "FullName" has not been opened.
I have tried replacing the range with the full filepath of the workbook I want to open, but the same thing happens, and I have tried placing the 'ToGlobal' code directly in the before save event.

What I have found however, is that I place the code of the ToGlobal macro directly in the macro that the OnTime calls every 30 seconds, the code from ToGlobal runs fine without a break. However, I can't use that as a solution as that macro also needs to shut down the containing workbook.

Hi John,
This is only a guess as I don't think there is any way that I could easily test it.  I wonder whether you are getting the error because the workbook in question is not actually the active workbook.  So could you try referring to it by name or make sure it is active by activating it and see what happens then.
Meantime, I will think about it some more to see if anything else springs to mind.
Regards
Harry

Thanks Harry. The workbook doesn't actually open when it's called from the OnTime macro- curiously it doesn't break at that point.
ToGlobal is called in the before save event and works if save is clicked, or if the workbook is closed and the user clicks the prompt to save changes.

Hi John,
I'm a bit confused as to where the actual problem is and exactly what the program flow is.  Does the OnTime macro call the ToGlobal macro?  Presumably it does, because you say that the ToGlobal is failing because the file wasn't opened by the OnTime macro.  However, as far as I can see, even if it isn't opened by OnTime it should be opened by ToGlobal provided that the original workbook hasn't been saved and isn't readonly.  And the latter two conditions have to be met  and the master file has to be available for the code to get to where you say it is failing.
As an aside, this Copy does not seem to have a corresponding Paste -

Range("BB37").Select

Selection.Copy

Also on another coding matter you don't have to use Copy/Paste if you are only working with values, you can use Range(XX).Value = Range(YY).Value and this can be faster to execute and eliminates any need to turn ScreenUpdating on and off.  It is also a bit tidier and more compact.
I also think your code would be much more readable if you defined variables for your various workbooks and then used those to refer to them rather than the generic ActiveWorkbook etc.
I probably haven't advanced the cause a great deal but sometimes it is hard to see where the breakthrough might spring from.
Regards
Harry

Hi John,
I haven't been able to test this obviously but I have 're-written' your ToGlobal macro using variables to refer to your various workbooks and sheets and replacing your copy/paste code with assignments.  The latter assumes that the string from range CV would be a range designator corresponding in size to A3:AJ7.  I guess that would be the advantage of using copy as you only have to specify the topleft corner.  If you want the formatting as well you would have to add an extra line for the Style as in the earlier case.  I have also highlighted a couple of pieces of code (one mentioned previously) that seem to serve no purpose in this context.

Hopefully I have worked through the logic correctly but I am happy to be straightened out if I have crossed wires somewhere.

Option Explicit

Sub ToGlobal()
Dim Mywb As Workbook, Otherwb As Workbook
Dim Myws As Worksheet, Otherws As Worksheet, Myotherws As Worksheet
Set Mywb = ThisWorkbook
Dim strSheet As String
strSheet = Mywb.Range("TargetSheet").Value
Dim StartPst As String
StartPst = Mywb.Range("CV").Value
Set Myws = Mywb.Worksheets(1)
Myws.Unprotect Password:="TP"
Application.ScreenUpdating = False
Myws.Range("A1:AJ7").Style = Myws.Range("A42:AJ48").Style
Myws.Protect Password:="TP", DrawingObjects:=False
Myws.EnableSelection = xlNoRestrictions
Range("BB37").Select
Selection.Copy
Application.ScreenUpdating = True
Dim myFile As String, myFolder As String
myFolder = Range("Filepath")
myFile = myFolder & Range("Filename")
If Not IsFolderExists(myFolder) Then
    CreateObject("Scripting.FileSystemObject").CreateFolder myFolder
End If
If Not IsFileExists(myFile) Then
    MsgBox "The folder path & file " & myFile & " could not be found and the global TP cannot be updated."
    UserForm1.Hide
    Exit Sub
End If
If Mywb.Saved = False And Mywb.ReadOnly = False Then
    UserForm1.Show
    UserForm1.Repaint
    Application.DisplayAlerts = False
    Set Myotherws = ActiveWorkbook.Worksheets(1)
    Dim CurrentGlobal As String
    CurrentGlobal = Replace(Range("Filename"), "\", "")
    Set Otherwb = Workbooks.Open(Filename:=Mywb.Range("FullName").Text, WriteResPassword:="GTP")
    If Otherwb.ReadOnly = True Then
      UserForm1.Hide
      MsgBox "Another user has the global TP open. Could not update- please click save again to update the global TP"
      Otherwb.Close
      Application.DisplayAlerts = True
      Exit Sub
    End If
    Set Otherws = Otherwb.Worksheets(strSheet)
    Otherws.Range(StartPst).Value = Myotherws.Range("A3:AJ7").Value
    Application.CutCopyMode = False
    Range("A1").Select
    Otherwb.Save
    ActiveWindow.Close
    Application.DisplayAlerts = True
    Range("B3").Select
    UserForm1.Hide
    End If
End Sub

Regards
Harry
PS I seem to remember we were discussing this maybe about Christmas - So Easter wishes!
H

Hello Harry, Easter wishes to you too.
I've tried the code you posted, but it breaks at:
strSheet = Mywb.Range("TargetSheet").Value with run- time error 438- obbject doesn't support this property or method.

I never had the time to tidy up my code which is why in it is inelegant and you're right, there are a few lines that are not necessary- they are from earlier versions, but I will have to test to double check.

I hope the below makes the issue clearer:

The ToGlobal macro is called from the before save workbook event and works fine when the user saves the workbook. However, there is also an OnTime macro running- after a set period of time, the OT macro calls a macro called ShutDown. That macro checks to see if changes were made and if not, it somply closes the workbook, but if changes were made, it saves the workbook, which in turn causes the ToGlobal macro to fire as to is called on the before save workbook event. However, this is where the problem is because the ToGlobal code breaks at the line:
ActiveWorkbook.Worksheets(strSheet).Activate with the message subscript out range.
Regards, John

Too add to my previous- it appears to break at that line because the Global TP has not actually opened- I have checked. This is odd to me because when the ToGlobal is called because the user clicks save, it works fine. I have tried placing the code contained in ToGlobal directly in the Before Save event, but this makes no difference.

Hello John,
Try modifying the line to
strSheet = Mywb.ActiveSheet.Range("TargetSheet").Value
It might be that your named ranges are only local to the sheet.  That means you might have to modify similar lines of code further on if this fix moves the error somewhere else.
I've got a feeling that I made a mock up of your system last time around but would have to go back over the previous discussion to see if that holds any more clues.
Regards
Harry

Thanks Harry, will give that a try when I get a chance- it would certainly be a good idea to make a more explicit reference to named ranges to save any conflicts.

Re my original problem, I have devised a work around- in the macro which shuts down the workbook which is itself called by the OnTime macro, I have placed a copy of the coding in 'ToGlobal'. To prevent it running twice when the workbook times out, I have prefixed the duplicate with a global boolean variable that is set to True. In the actual 'ToGlobal' macro, I have prefixed it with an If statement, so that if the variable is set toTrue, the ToGlobal doesn't run. Although the global variable clears when the workbbok closes, I have nevertheless set it to flase before the workbook closes.

Hi again Harry

Re the version of my code you kindly supplied- I have made some ammendments as suggested. Have also had to put the paste formats back in as opposed to syles as that wasn't functioning correctly- I'm using 2003 so not sure if that's why. The following works fine:

Sub ToGobal()
Dim Mywb As Workbook, Otherwb As Workbook
Dim Myws As Worksheet, Otherws As Worksheet, Myotherws As Worksheet
Set Mywb = ThisWorkbook
Dim strSheet As String
Set Myws = Mywb.Worksheets(1)
Dim StartPst As String
StartPst = Myws.Range("CV").Value
strSheet = Myws.Range("TargetSheet").Value
Myws.Unprotect Password:="TP"
Application.ScreenUpdating = False
Myws.Range("A42:AJ48").Select
    Application.CutCopyMode = False
    Selection.Copy
    Myws.Range("A1:AJ7").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
      SkipBlanks:=False, Transpose:=False
Myws.Protect Password:="TP", DrawingObjects:=False
Myws.EnableSelection = xlNoRestrictions
Range("BB37").Select
Selection.Copy
Application.ScreenUpdating = True
Dim myFile As String, myFolder As String
myFolder = Range("Filepath")
myFile = myFolder & Range("Filename")
If Not IsFolderExists(myFolder) Then
    CreateObject("Scripting.FileSystemObject").CreateFolder myFolder
End If
If Not IsFileExists(myFile) Then
    MsgBox "The folder path & file " & myFile & " could not be found and the global TP cannot be updated."
    UserForm1.Hide
    Exit Sub
End If
If Mywb.Saved = False And Mywb.ReadOnly = False Then
    UserForm1.Show
    UserForm1.Repaint
    Application.DisplayAlerts = False
    Set Myotherws = ActiveWorkbook.Worksheets(1)
    Dim CurrentGlobal As String
    CurrentGlobal = Replace(Range("Filename"), "\", "")
    Set Otherwb = Workbooks.Open(Filename:=Myws.Range("FullName").Text, WriteResPassword:="GTP")
    If Otherwb.ReadOnly = True Then
    UserForm1.Hide
    MsgBox "Another user has the global TP open. Could not update- please click save again to update the global TP"
    Otherwb.Close
    Application.DisplayAlerts = True
    Exit Sub
    End If
    Set Otherws = Otherwb.Worksheets(strSheet)
    Otherws.Range(StartPst).Value = Myotherws.Range("A3:AJ7").Value
    Application.CutCopyMode = False
    Range("A1").Select
    Otherwb.Save
    ActiveWindow.Close
    Application.DisplayAlerts = True
    Range("B3").Select
    UserForm1.Hide
    End If
End Sub

Still none the wiser re my original problem, but at least I've found a workaround.

Regards,

John

Hi John,
Good to hear.  Where there's a will there's a way - if not a lawsuit /;-{)}
Harry