zoom38
10-17-2007, 09:18 AM
My file contains the following code in a standard module:
Public fname As String
Public WB As String
Public BegSched As Date
Sub NewSchedFileMain()
Application.ScreenUpdating = False
Call SaveAs
Workbooks.Open Filename:=fname
Workbooks(fname).Activate
Cells(1, 19).Value = BegSched
Application.ScreenUpdating = True
Workbooks(WB).Close savechanges:=True
End Sub
Sub SaveAs()
Dim EndSched As Date
Dim bs As String
Dim es As String
'Save The Active Workbook In Format "RVSD_Mar 26, 2007-Apr 24, 2007"
WB = ActiveWorkbook.Name
BegSched = Sheets(1).Cells(1, 19).Value
EndSched = Sheets(1).Cells(1, 26).Value
BegSched = BegSched + 28
EndSched = BegSched + 27
bs = Format(BegSched, "mmm dd, yyyy")
es = Format(EndSched, "mmm dd, yyyy")
fname = "RVSD_" & bs & "-" & es & ".xls"
ActiveWorkbook.SaveCopyAs Filename:=fname
MsgBox Prompt:="The New Schedule File Was Saved As """ & fname & """", Title:="New Workbook."
End Sub
My problem is with the line "Workbooks(WB).Close savechanges:=True". If I include this line the file closes but I get the following message "application-defined or object-defined error". If I comment this line out I don't get the error message but of course the file doesn't close like I want it to. Can someone advise what I did wrong.
By the way I do have code(below) in "This Workbook" which I can't figure out if it is affecting the above:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ThisSheet As Worksheet
Dim wksh As Worksheet
Application.ScreenUpdating = False
Set ThisSheet = ActiveSheet
For Each wksh In Worksheets
wksh.Activate
Range("a1").Select
ActiveSheet.Protect Password:="pword", DrawingObjects:=False, contents:=True, _
Scenarios:=True, userinterfaceonly:=True
Next wksh
ThisSheet.Activate
If ActiveSheet.Index <> 1 And ActiveSheet.Index <> 2 Then
Sheets(1).Activate
End If
'If SaveAs Is Selected, The Following Code Calls The sub
'In Module9b That Updates The Title In The Title Bar.
If SaveAsUI Then
Application.OnTime Now(), "UpdateCaption"
End If
Application.ScreenUpdating = True
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.OnTime Now(), "UpdateCaption"
Application.Calculate
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next 'in case the menu item has already been deleted
Application.CommandBars("Worksheet Menu Bar").Controls("My Tools").Delete 'delete the menu item
End Sub
Public Sub Workbook_WindowActivate(ByVal Wn As Window)
' Insert the Revision Date and Version in the Title Bar
Wn.Caption = ThisWorkbook.Name & " ***** (Revised October 16, 2007 - ver 5.0) *****"
End Sub
Thanks
Gary
Edit by Lucas: Line breaks added to keep code from running off the screen to the right...
Public fname As String
Public WB As String
Public BegSched As Date
Sub NewSchedFileMain()
Application.ScreenUpdating = False
Call SaveAs
Workbooks.Open Filename:=fname
Workbooks(fname).Activate
Cells(1, 19).Value = BegSched
Application.ScreenUpdating = True
Workbooks(WB).Close savechanges:=True
End Sub
Sub SaveAs()
Dim EndSched As Date
Dim bs As String
Dim es As String
'Save The Active Workbook In Format "RVSD_Mar 26, 2007-Apr 24, 2007"
WB = ActiveWorkbook.Name
BegSched = Sheets(1).Cells(1, 19).Value
EndSched = Sheets(1).Cells(1, 26).Value
BegSched = BegSched + 28
EndSched = BegSched + 27
bs = Format(BegSched, "mmm dd, yyyy")
es = Format(EndSched, "mmm dd, yyyy")
fname = "RVSD_" & bs & "-" & es & ".xls"
ActiveWorkbook.SaveCopyAs Filename:=fname
MsgBox Prompt:="The New Schedule File Was Saved As """ & fname & """", Title:="New Workbook."
End Sub
My problem is with the line "Workbooks(WB).Close savechanges:=True". If I include this line the file closes but I get the following message "application-defined or object-defined error". If I comment this line out I don't get the error message but of course the file doesn't close like I want it to. Can someone advise what I did wrong.
By the way I do have code(below) in "This Workbook" which I can't figure out if it is affecting the above:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ThisSheet As Worksheet
Dim wksh As Worksheet
Application.ScreenUpdating = False
Set ThisSheet = ActiveSheet
For Each wksh In Worksheets
wksh.Activate
Range("a1").Select
ActiveSheet.Protect Password:="pword", DrawingObjects:=False, contents:=True, _
Scenarios:=True, userinterfaceonly:=True
Next wksh
ThisSheet.Activate
If ActiveSheet.Index <> 1 And ActiveSheet.Index <> 2 Then
Sheets(1).Activate
End If
'If SaveAs Is Selected, The Following Code Calls The sub
'In Module9b That Updates The Title In The Title Bar.
If SaveAsUI Then
Application.OnTime Now(), "UpdateCaption"
End If
Application.ScreenUpdating = True
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.OnTime Now(), "UpdateCaption"
Application.Calculate
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next 'in case the menu item has already been deleted
Application.CommandBars("Worksheet Menu Bar").Controls("My Tools").Delete 'delete the menu item
End Sub
Public Sub Workbook_WindowActivate(ByVal Wn As Window)
' Insert the Revision Date and Version in the Title Bar
Wn.Caption = ThisWorkbook.Name & " ***** (Revised October 16, 2007 - ver 5.0) *****"
End Sub
Thanks
Gary
Edit by Lucas: Line breaks added to keep code from running off the screen to the right...