Close WorkBook with CommandButton

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

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

Thanks

Rocke
 
Back
Top