WorkBook_BeforeClose (Delete Menu)

  • Thread starter Thread starter Rockee052
  • Start date Start date
R

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? :confused:
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
 
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
 
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


Rob van Gelder said:
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
Click to expand...
 
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
 
Back
Top