EXCELY


Free Excel Templates and Articles
  • Excel VBA
  • Custom Message Box Buttons using Hooking in Excel VBA

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