Close WorkBook with CommandButton

[Rockee052]

Hello,

I have a cmdbutton that I would like to use to close a workbook.
think I'm placing the procedures in the wrong area. Honestly, I'm no
sure how to set it up. The reason I say that is because I have a custo
menu with some code in Workbook_BeforeClose that duplicates excels sav
dialog box to stop the custom menu from being deleted if the user clic
on cancel. I have tried putting a call commandbutton_click() in th
Workbook_BeforeClose but, I get an error. In th
commandbutton_click(), I used Workbooks("MyFile.XLS").Close. Is thi
the right method or am I missing something? Can you even call
commandbutton? I guess that defeats the whole purpose of
commandbutton.

Here is the code I have:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

' If possible, what method would I use to call the cmdbutton?

If Not Me.Saved Then
Dim Msg As String
Dim Ans As Integer
Beep
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

Rockee
Excel 200

 

[Ron de Bruin]

Hi Rockee

Try to put your code to add and delete the menu in this events

Private Sub Workbook_Activate()

End Sub

Private Sub Workbook_Deactivate()

End Sub

 

[Rockee052]

Ron,

Thanks for your reply. I found a soulution before reading you reply.

Private Sub CommandButton3_Click()
Application.ScreenUpdating = False
With ActiveWorkbook
.RunAutoMacros xlAutoClose
.Close
End With
Application.ScreenUpdating = True
End Sub

So far so good, it also calls the custom save dialog box. I'm going t
play around with it and see what errors I can cause .

Thanks

Rocke

 

[Rockee052]

I thought is was good until I actually started playing with th
workbook. The result are not what I'm trying to achieve. If I clic
yes on the save dialog box it brings up excels save dialog box. If
click on no or cancel it works fine
The code I have been working with is:

Private Sub CommandButton3_Click()
' Tried using Application.DisplayAlerts = False
Application.ScreenUpdating = False
With ActiveWorkbook
.RunAutoMacros xlAutoClose
.Close
End With
End Sub

I'm trying to incorporate my Workbook_BeforeClose code, then exi
excel. I was also wondering if there was a way to close excel whe
closing the workbook. I have read in the newsgroup to us
Application.Quit but like the rest I can't figure out how to apply it.


If anyone has any suggestion, please reply

Thanks

Rocke