WorkBook_BeforeClose (Delete Menu)

[Rockee052]

Hi Ya'll,

I have a code that works like excels "save workbook before closing"
except it will stop my commandbar from being deleted if the user click
on cancel.. My problem is that I am getting a "Variable not defined
error. Not sure what I missed with the code.. I am following the cod
from a book and the code is exactly how the book has it...

Does anyone see anything wrong with my code?
Any help will be appreciated...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not Me.Saved Then
Msg = "Do you want to save the changes you made to "
Msg = Msg & Me.Name & "?"
Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel)
Select Case Ans
Case vbYes
Me.Save
Case vbNo
Me.Saved = True
Case vbCancel
Cancel = True
Exit Sub
End Select
End If
Call DeleteMenu
End Sub

Thanks,

Rockee

Excel 2003 (11.0

 

[Vasant Nanavati]

On the first line of your sub:

Dim Msg As String, Ans As Integer

 

[Rockee052]

Vasant,

Ahh, Thank you very much....

Rocke

 

[Rob van Gelder]

I believe the error is with Me.Name


See if this one suits...


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim strTemp As String

With ThisWorkbook
If Not .Saved Then
Do
strTemp = "Do you want to save the changes you made to '" &
..Name & "'?"
Select Case MsgBox(strTemp, vbExclamation + vbYesNoCancel)
Case vbYes
If .Path = "" Then
strTemp = Application.GetSaveAsFilename
If strTemp <> False Then .SaveAs strTemp
Else
.Save
End If
Case vbNo
.Saved = True
Case vbCancel
Cancel = True
Exit Sub
End Select
Loop Until .Saved
End If
End With

'code goes here
End Sub

 

[Rob van Gelder]

A few things I noticed wrong in the code I posted...

Try this instead:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim strTemp As String

With ThisWorkbook
If Not .Saved Then
Do
strTemp = "Do you want to save the changes you made to '" &
..Name & "'?"
Select Case MsgBox(strTemp, vbExclamation + vbYesNoCancel)
Case vbYes
If .Path = "" Then
strTemp = Application.GetSaveAsFilename(.Name &
".xls", _
"Microsoft Excel Workbook (*.xls),
*.xls")
If strTemp <> "False" Then .SaveAs strTemp,
AddToMRU:=True
Else
.Save
End If
Case vbNo
.Saved = True
Case vbCancel
Cancel = True
Exit Sub
End Select
Loop Until .Saved
End If
End With

'code goes here
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel

I believe the error is with Me.Name


See if this one suits...


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim strTemp As String

With ThisWorkbook
If Not .Saved Then
Do
strTemp = "Do you want to save the changes you made to '" &
.Name & "'?"
Select Case MsgBox(strTemp, vbExclamation + vbYesNoCancel)
Case vbYes
If .Path = "" Then
strTemp = Application.GetSaveAsFilename
If strTemp <> False Then .SaveAs strTemp
Else
.Save
End If
Case vbNo
.Saved = True
Case vbCancel
Cancel = True
Exit Sub
End Select
Loop Until .Saved
End If
End With

'code goes here
End Sub

 

[Rockee052]

Rob,

Thanks for the reply

Adding Dim Msg As String and Dim Ans As Integer fixed the code.
But, its nice to see a different way of coding... I'm going to put tha
in my example file...

Again Thanks,

Rocke