MsgBox with custom button texts

This post provides code that you can use to customise the text in the buttons of a message box. You can show the message box with 1, 2 or 3 buttons and get the text of the clicked button back.

I'm aware there are other versions of this online ... but I couldn't find one that:

• Includes code that has been updated for VBA7, and

• Allows use of Unicode characters (in the title bar text, in the message box body, and in the button texts)

The code includes comments to explain what is going on but the high-level summary is:

• When the Show function is called, a 'window hook' is set using SetWindowsHookEx ... the target of this hook is the Callback function which will then be notified about window-related events

• The code to show a message box (in the Select Case line) runs ... initially, the message box buttons are defined as having non-customised text ("OK", "Cancel" etc)

• Obviously ... the message box is in a window ... and so when the system is creating (or doing anything else with) that window, the hook target (i.e. the Callback function) is called

• The Callback function checks that the type of event (there are a lot of window-related events and we don't want to do the customising of the button texts until the right event has occurred) ... if it is an HCBT_ACTIVATE event (which is defined as "the system is about to activate a window") then ...

• ... the button texts are customised, and

• ... the 'red X' (i.e. used for closing the message box without clicking a button) is removed (see the code comments for the reasons why), and

• ... the hook is removed as it has served its purpose

Note also that when customising the texts of the buttons, each String with the button text is not passed to the SetDlgItemText Windows API function directly, rather StrPtr is used in order to pass a pointer to the String. We are using the Unicode variant of SetDlgItemText (i.e. SetDlgItemTextW where "W" stands for "wide" meaning Unicode ... the alternative is SetDlgItemTextA where "A" means "ANSI"). The advantage of using the Unicode variant is that any non-ANSI characters will then be shown correctly ... if you use the English language then this may not make a huge difference to you, but other languages can frequently use non-ANSI characters. The downside of using the Unicode variant is that the built-in VBA behaviour is to convert all Strings to ANSI (even though VBA itself uses Unicode for Strings!) before passing them to Windows API functions. Using a pointer to the String avoids this problem.

So here we go. I recommend putting this code in a standard Module (I call it MBox) all by itself so that all of the Windows API functions and other complexity are hidden away.

If you are using this code in Excel then you can use it 'out of the box'. However, if you are using it in any other host application then Application.hWnd will need to be replaced ... you can always replace it with 0 (the message box will then have no 'owner' meaning it can end up being hidden behind the host application's window), or:

• If using this code in Word, you can replace it with Application.ActiveDocument.ActiveWindow.hWnd

• If using this code in Access, you can replace it with Application.hWndAccessApp

• Use the GetHwnd function from my Get the application window handle in any app (well ... in Excel, Word, Access, PowerPoint or Outlook) post

Here's a usage example:

And another using a Unicode symbol:

So is there any great advantage over using this compared to using a UserForm with a few CommandButtons? Errr, not really. It could be argued that because it looks (and sounds, depending on which MsgBoxIcon you use) exactly like a 'normal' MsgBox then the user is more familiar with it ... but given sufficient time and effort, it is obviously possible to create a UserForm that looks exactly the same ... and having done that you can add as many buttons (with as much text on each button) as you want. But it's handy just to drop into your VBA Project if you don't want to be bothered with setting up a UserForm.

To read more about the MessageBoxW function, see https://learn.microsoft.com/en-us/windows/win32/api/winuser/nf-winuser-messageboxw.

Documentation for SetDlgItemTextW is here https://learn.microsoft.com/en-us/windows/win32/api/winuser/nf-winuser-setdlgitemtextw.

And to read more about hooks and callbacks, good starting points are the SetWindowsHookExA function https://learn.microsoft.com/en-us/windows/win32/api/winuser/nf-winuser-setwindowshookexa and the CBTProc callback function https://learn.microsoft.com/en-us/windows/win32/winmsg/cbtproc (this is the type of callback used in this code ... note the WH_CBT constant being passed into the SetWindowsHookEx function).