Hi

Is it possible to have a Msgbox pop up with two
options "Close and Save Workbook" and "Keep working on
Workbook"

Obviously when someclicks on "Close and Save Workbook" it
will close and save it.

No. You need to create a Userform for that. However, why not change your
tact and promt the user with the following message:

"Do you want to Close and Save the Workbook?"

Then use either the Yes/No or OK/Cancel buttons on the message box (see
below for an example)

Sub MessageBoxExampleForCloseAndSave()

Dim lAnswer As Long

'Assume the answer will be Cancel
lAnswer = vbCancel
'Prompt the user
'Note: I want to show both the exclamation icon AND the OK/Cancel
buttons in the message box.
' I used a plus (vbExclamation + vbOKCancel) to add both.
lAnswer = MsgBox("Do you want to Close and Save the Workbook?",
vbExclamation + vbOKCancel, "Close and Save")

'Close and save the workbook if the user click OK
If lAnswer = vbOK Then ActiveWorkbook.Close True

End Sub
--
_______________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel

"Ed" wrote in message
...
Hi

Is it possible to have a Msgbox pop up with two
options "Close and Save Workbook" and "Keep working on
Workbook"

Obviously when someclicks on "Close and Save Workbook" it
will close and save it.

A User-Form will do that but not a MsgBox. But you can display a MsgBox
with Yes and No buttons. Just formulate your question to achieve the same
thing with a Yes or No response. HTH Otto
"Ed" wrote in message
...
Hi

Is it possible to have a Msgbox pop up with two
options "Close and Save Workbook" and "Keep working on
Workbook"

Obviously when someclicks on "Close and Save Workbook" it
will close and save it.

Ed

The following response to a similar question was posted in the group a while
back. I think it will do what you want though there's a fair amount of code
to achieve it ... just copy and paste to a standard module. There's test
code at the end. Also note that you'd have to reduce the length of your
message
as this code has short option buttons.

All in all, a User Form is probably the best option here.

Regards

'Trevor


'Groups
' Advanced Groups Search Preferences Groups Help
'
'
'Groups search result 6 for msgbox button text
group:microsoft.public.excel.* author:harald author:staff
'
'
' Custom VB Programming . Find Freelance VB programmers at Smartbids
..www.smartbids.co.uk Sponsored Links
'
' VisualASP Components . ASP Components for Tree, Grid, Calendar and
TabStrip .www.visualasp.com
' ActiveX Q&A . ActiveX Discussion Group Free E-mail Based Support
..visualbasic.ittoolbox.com
'
'Search Result 6
'From: Harald Staff )
'Subject: Custom MsgBox Buttons
'View: Complete Thread (9 articles)
'Original Format
'Newsgroups: microsoft.Public.Excel.programming
'Date: 2002-08-14 01:31:31 PST
'
'
'"Jon Peltier" skrev i melding
news:#c#p1TzQCHA.4244@tkmsftngp08...
' Harald -
'
' Is that API stuff, or is it even uglier?
'
'API stuff.
'This method has evolved for a while at PlanetSourceCode, the origin is
'unknown. Current version is written by "MrBobo" for VB6 and found at
'http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=21772&l
ngWId = 1
'
'Here we go -test code at the very bottom:
'******************
Option Explicit

Private Const MB_YESNOCANCEL = &H3&
Private Const MB_YESNO = &H4&
Private Const MB_RETRYCANCEL = &H5&
Private Const MB_OKCANCEL = &H1&
Private Const MB_OK = &H0&
Private Const MB_ABORTRETRYIGNORE = &H2&
Private Const MB_ICONEXCLAMATION = &H30&
Private Const MB_ICONQUESTION = &H20&
Private Const MB_ICONASTERISK = &H40&
Private Const MB_ICONINFORMATION = MB_ICONASTERISK
Private Const IDOK = 1
Private Const IDCANCEL = 2
Private Const IDABORT = 3
Private Const IDRETRY = 4
Private Const IDIGNORE = 5
Private Const IDYES = 6
Private Const IDNO = 7
Private Const IDPROMPT = &HFFFF&
Private Const WH_CBT = 5
Private Const GWL_HINSTANCE = (-6)
Private Const HCBT_ACTIVATE = 5
Private Type MSGBOX_HOOK_PARAMS
hwndOwner As Long
hHook As Long
End Type
Private MSGHOOK As MSGBOX_HOOK_PARAMS
Private Declare Function GetCurrentThreadId Lib "kernel32" () As Long
Public Declare Function GetDesktopWindow Lib "user32" () As Long
Private Declare Function GetWindowLong Lib "user32" Alias _
"GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
Private Declare Function MessageBox Lib "user32" Alias _
"MessageBoxA" (ByVal hwnd As Long, ByVal lpText As String, _
ByVal lpCaption As String, ByVal wType As Long) As Long
Private Declare Function SetDlgItemText Lib "user32" Alias _
"SetDlgItemTextA" (ByVal hDlg As Long, ByVal nIDDlgItem As Long, _
ByVal lpString As String) As Long
Private Declare Function SetWindowsHookEx Lib "user32" Alias _
"SetWindowsHookExA" (ByVal idHook As Long, ByVal lpfn As Long, _
ByVal hmod As Long, ByVal dwThreadId As Long) As Long
Private Declare Function SetWindowText Lib "user32" Alias _
"SetWindowTextA" (ByVal hwnd As Long, ByVal lpString As String) As Long
Private Declare Function UnhookWindowsHookEx Lib "user32" _
(ByVal hHook As Long) As Long
Dim mbFlags As VbMsgBoxStyle
Dim mbFlags2 As VbMsgBoxStyle
Dim mTitle As String
Dim mPrompt As String
Dim But1 As String
Dim But2 As String
Dim But3 As String
Public Function MessageBoxH(hwndThreadOwner As Long, _
hwndOwner As Long, mbFlags As VbMsgBoxStyle) As Long
'This function calls the hook
Dim hInstance As Long
Dim hThreadId As Long
hInstance = GetWindowLong(hwndThreadOwner, GWL_HINSTANCE)
hThreadId = GetCurrentThreadId()
With MSGHOOK
.hwndOwner = hwndOwner
.hHook = SetWindowsHookEx(WH_CBT, AddressOf MsgBoxHookProc, _
hInstance, hThreadId)
End With
MessageBoxH = MessageBox(hwndOwner, Space$(120), Space$(120), mbFlags)
End Function
Public Function MsgBoxHookProc(ByVal uMsg As Long, _
ByVal wParam As Long, ByVal lParam As Long) As Long
'This function catches the messagebox before it opens
'and changes the text of the buttons - then removes the hook
If uMsg = HCBT_ACTIVATE Then
SetWindowText wParam, mTitle
SetDlgItemText wParam, IDPROMPT, mPrompt
Select Case mbFlags
Case vbAbortRetryIgnore
SetDlgItemText wParam, IDABORT, But1
SetDlgItemText wParam, IDRETRY, But2
SetDlgItemText wParam, IDIGNORE, But3
Case vbYesNoCancel
SetDlgItemText wParam, IDYES, But1
SetDlgItemText wParam, IDNO, But2
SetDlgItemText wParam, IDCANCEL, But3
Case vbOKOnly
SetDlgItemText wParam, IDOK, But1
Case vbRetryCancel
SetDlgItemText wParam, IDRETRY, But1
SetDlgItemText wParam, IDCANCEL, But2
Case vbYesNo
SetDlgItemText wParam, IDYES, But1
SetDlgItemText wParam, IDNO, But2
Case vbOKCancel
SetDlgItemText wParam, IDOK, But1
SetDlgItemText wParam, IDCANCEL, But2
End Select
UnhookWindowsHookEx MSGHOOK.hHook
End If
MsgBoxHookProc = False
End Function


Public Function BBmsgbox(mhwnd As Long, _
mMsgbox As VbMsgBoxStyle, Title As String, _
Prompt As String, Optional mMsgIcon As VbMsgBoxStyle, _
Optional ButA As String, Optional ButB As String, _
Optional ButC As String) As String
'This function sets your custom parameters and returns
'which button was pressed as a string
Dim mReturn As Long
mbFlags = mMsgbox
mbFlags2 = mMsgIcon
mTitle = Title
mPrompt = Prompt
But1 = ButA
But2 = ButB
But3 = ButC
mReturn = MessageBoxH(mhwnd, GetDesktopWindow(), _
mbFlags Or mbFlags2)
Select Case mReturn
Case IDABORT
BBmsgbox = But1
Case IDRETRY
BBmsgbox = But2
Case IDIGNORE
BBmsgbox = But3
Case IDYES
BBmsgbox = But1
Case IDNO
BBmsgbox = But2
Case IDCANCEL
BBmsgbox = But3
Case IDOK
BBmsgbox = But1
End Select
End Function

Sub Test()
Dim mReturn As String
mReturn = BBmsgbox(1, vbYesNoCancel, _
"Hi There", "Cool ?", , "Go for it", "No way", "Let me think")
MsgBox "You pressed " + mReturn
End Sub

Sub Test2()
Dim mReturn As String
mReturn = BBmsgbox(1, vbYesNo, _
"Workbook Options", "Please Choose ...", , "Close + Save", "Keep
working")
MsgBox "You pressed " + mReturn
End Sub


"Ed" wrote in message
...
Hi

Is it possible to have a Msgbox pop up with two
options "Close and Save Workbook" and "Keep working on
Workbook"

Obviously when someclicks on "Close and Save Workbook" it
will close and save it.