Excel VBA, MsgBox Yes or No, but must be "Si" or "No"

Question

I have a fairly easy question that I think there should be an easier way to do it, but I haven't been able to find the answer that I want.

Pretty simple, I have a MsgBox with +vbYesNo,

But my end user is a Spanish speaker, so all the statements are in Spanish, except for the "Yes" or "No" message box buttons.

Is there a quick way to change both buttoms to spanish?, well at least the "Yes" by "Si".

I did some research, and seems that the only way is to create the whole msgbox as a userform and add Commandbuttons with custom labels.

Is there a way to change the language of this specific excel file with macros (.xlsm) just for use by the end user?

Also, the end user and I have the default Office language set to English, and changing it is not an option.

What do you recommend?

Answer 1

You could hook into the message box and change the button text there. For 64-bit, your WINAPIs would be:

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 LongPtr
    hHook As Long
End Type

Private MSGHOOK As MSGBOX_HOOK_PARAMS

Private Declare PtrSafe Function GetCurrentThreadId Lib "kernel32" () As Long
Public Declare PtrSafe Function GetDesktopWindow Lib "user32" () As LongPtr
Private Declare PtrSafe Function GetWindowLong Lib "user32" Alias _
    "GetWindowLongA" (ByVal hwnd As LongPtr, ByVal nIndex As Long) As Long
Private Declare PtrSafe Function MessageBox Lib "user32" Alias _
    "MessageBoxA" (ByVal hwnd As LongPtr, ByVal lpText As String, _
    ByVal lpCaption As String, ByVal wType As Long) As Long
Private Declare PtrSafe Function SetDlgItemText Lib "user32" Alias _
    "SetDlgItemTextA" (ByVal hDlg As LongPtr, ByVal nIDDlgItem As Long, _
    ByVal lpString As String) As Long
Private Declare PtrSafe Function SetWindowsHookEx Lib "user32" Alias _
    "SetWindowsHookExA" (ByVal idHook As Long, ByVal lpfn As LongPtr, _
    ByVal hmod As LongPtr, ByVal dwThreadId As Long) As Long
Private Declare PtrSafe Function SetWindowText Lib "user32" Alias _
    "SetWindowTextA" (ByVal hwnd As LongPtr, ByVal lpString As String) As Long
Private Declare PtrSafe 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 mBtn1 As String
Dim mBtn2 As String
Dim mBtn3 As String

Private Function MessageBoxH(hwndThreadOwner As LongPtr, _
    hwndOwner As LongPtr, mbFlags As VbMsgBoxStyle) As LongPtr

    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

Private Function MsgBoxHookProc(ByVal uMsg As Long, _
    ByVal wParam As Long, ByVal lParam As Long) As Long

    If uMsg = HCBT_ACTIVATE Then
        SetWindowText wParam, mTitle
        SetDlgItemText wParam, IDPROMPT, mPrompt
        Select Case mbFlags
            Case vbAbortRetryIgnore
                SetDlgItemText wParam, IDABORT, mBtn1
                SetDlgItemText wParam, IDRETRY, mBtn2
                SetDlgItemText wParam, IDIGNORE, mBtn3
            Case vbYesNoCancel
                SetDlgItemText wParam, IDYES, mBtn1
                SetDlgItemText wParam, IDNO, mBtn2
                SetDlgItemText wParam, IDCANCEL, mBtn3
            Case vbOKOnly
                SetDlgItemText wParam, IDOK, mBtn1
            Case vbRetryCancel
                SetDlgItemText wParam, IDRETRY, mBtn1
                SetDlgItemText wParam, IDCANCEL, mBtn2
            Case vbYesNo
                SetDlgItemText wParam, IDYES, mBtn1
                SetDlgItemText wParam, IDNO, mBtn2
            Case vbOKCancel
                SetDlgItemText wParam, IDOK, mBtn1
                SetDlgItemText wParam, IDCANCEL, mBtn2
        End Select
        UnhookWindowsHookEx MSGHOOK.hHook
    End If

    MsgBoxHookProc = False

End Function

Public Function CustomMsgBox(mhwnd As LongPtr, _
    mMsgbox As VbMsgBoxStyle, Title As String, _
    Prompt As String, Optional mMsgIcon As VbMsgBoxStyle, _
    Optional btnA As String, Optional btnB As String, _
    Optional btnC As String) As String

    Dim customText As LongPtr

    mbFlags = mMsgbox
    mbFlags2 = mMsgIcon
    mTitle = Title
    mPrompt = Prompt
    mBtn1 = btnA
    mBtn2 = btnB
    mBtn3 = btnC
    customText = MessageBoxH(mhwnd, GetDesktopWindow(), mbFlags Or mbFlags2)

    Select Case customText
        Case IDABORT: CustomMsgBox = mBtn1
        Case IDRETRY: CustomMsgBox = mBtn2
        Case IDIGNORE: CustomMsgBox = mBtn3
        Case IDYES: CustomMsgBox = mBtn1
        Case IDNO: CustomMsgBox = mBtn2
        Case IDCANCEL: CustomMsgBox = mBtn3
        Case IDOK: CustomMsgBox = mBtn1
    End Select
End Function

So, you'd simply call the message box like so:

Sub TestYN()
    Const MY_YES As String = "Si"
    Const MY_NO As String = "Non"
    Dim dialogResult As String

    dialogResult = CustomMsgBox(1, vbYesNo, "Enviar Correos...", "¿Desea continuar?", vbInformation, MY_YES, MY_NO)

    Debug.Print "Selected button was " & dialogResult
End Sub