Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorkBook_BeforeClose (Delete Menu)
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorkBook_BeforeClose (Delete Menu)
On the first line of your sub:
Dim Msg As String, Ans As Integer -- Vasant "Rockee052 " wrote in message ... 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 clicks 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 code 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) --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorkBook_BeforeClose (Delete Menu)
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorkBook_BeforeClose (Delete Menu)
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 - http://www.vangelder.co.nz/excel "Rockee052 " wrote in message ... 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 clicks 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 code 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) --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorkBook_BeforeClose (Delete Menu)
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" wrote in message ... 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 - http://www.vangelder.co.nz/excel "Rockee052 " wrote in message ... 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 clicks 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 code 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) --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorkBook_BeforeClose (Delete Menu)
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 -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Workbook_BeforeClose | Excel Discussion (Misc queries) | |||
Workbook_BeforeClose Question | Excel Worksheet Functions | |||
help me. How to delete a menu | Excel Discussion (Misc queries) | |||
Workbook_Beforeclose vs BeforeClose Event | Excel Programming | |||
Workbook_BeforeClose Event | Excel Programming |