Custom Message Box Buttons using Hooking in Excel VBA
Q. How can I change the button caption for the Message Box (MsgBox)?
A. You need to use Windows Hooking API in your Excel VBA:
- You must create a CBT hook
- Run a Message Box with CBT hook
- Catch a HCBT_ACTIVATE message in the Hook procedure
- Set new cputions for the buttons using the SetDlgItemText function
(example below changes “Yes” and “No” captions to smiles: “:-)” and “:-(” ) - Release the CBT hook
Example: Change Button Captions for Message Box using VBA in Excel:
Option Explicit ' Import Private Declare Function GetCurrentThreadId Lib "kernel32" () 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 UnhookWindowsHookEx Lib "user32" _ (ByVal hHook As Long) As Long ' Handle to the Hook procedure Private hHook As Long ' Hook type Private Const WH_CBT = 5 Private Const HCBT_ACTIVATE = 5 ' Constants Public Const IDOK = 1 Public Const IDCANCEL = 2 Public Const IDABORT = 3 Public Const IDRETRY = 4 Public Const IDIGNORE = 5 Public Const IDYES = 6 Public Const IDNO = 7 Public Sub MsgBoxSmile() ' Set Hook hHook = SetWindowsHookEx(WH_CBT, _ AddressOf MsgBoxHookProc, _ 0, _ GetCurrentThreadId) ' Run MessageBox MsgBox "Smiling Message Box", vbYesNo, "Message Box Hooking" End Sub Private Function MsgBoxHookProc(ByVal lMsg As Long, _ ByVal wParam As Long, _ ByVal lParam As Long) As Long If lMsg = HCBT_ACTIVATE Then SetDlgItemText wParam, IDYES, ":-)" SetDlgItemText wParam, IDNO, ":-(" ' Release the Hook UnhookWindowsHookEx hHook End If MsgBoxHookProc = False End Function