Private Sub Workbook_BeforeSave variable problem 1

[mscallisto]

When I click "save" I want to trigger some logic to "Save As a new name" plus
do some general cleanup on that new file.


When I click "save" I enter this code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  If CallStatus <> "Yes" Then
     CallStatus = "Yes"
     Call Center
  Else
     CallStatus = "Yes"
  End If
End Sub

The call to Subroutine "Center" in turn calls Sub FileSave (below) that saves
the file thus triggering Workbook_BeforeSave again.

My " If CallStatus <> "Yes" Then logic (above), doesn't work and I'm guessing its
because CallStatus is not a global variable.

How can I do this?

Sub FileSave()
SuggestedFN = "Price List " & Month(Date) & " " & Day(Date) & " " & Year(Date) & " at " & Mid(Time, 1, 2) & " " & Mid(Time, 4, 2) & " " & Mid(Time, 7, 5)

MyPathAndFile = Application.GetSaveAsFilename(SuggestedFN, "Excel Files(*.xls),*.xls")

ActiveWorkbook.SaveAs Filename:= _
     MyPathAndFile, FileFormat:= _
     xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
     , CreateBackup:=False
End Sub

 

[PHV]

Have a look at the Application.EnableEvents property.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mscallisto]

I did have a look and it seems like the following should work:

Sub Center()
  Application.EnableEvents = False
  .
  .
  call FileSave
  .
  .
  Application.EnableEvents = true
end sub

Thank Kindly

 

[mscallisto]

Actually I tried the following and put a breakpoint at Application.EnableEvents = True

At the breakpoint, I stepped thru and saw the value turn to True as expected.

At this point everything looks great but as soon as I step thru the End Sub I get the message:

"Microsoft Office Excel has encountered a problem and needs to close"

I uncheck the [Recover my file and restart excel] and click Don't Send, (I sent the first error).

I look at my output file and everything looks perfect.

Any clues as to why I get the error msg ?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  Application.EnableEvents = False
  Call Center
  Application.EnableEvents = True [b]the breakpoint is here[/b]
End Sub

 

[mscallisto]

I should add that when I remove (Private Sub Workbook_BeforeSave)
and simply run the macro "Center" i don't get the error message.