Button Text

revilo

New member
Joined
Sep 30, 2011
Messages
22
Reaction score
0
Points
0
Is it possible to change the text in a MsgBox Button? I.e. have "Next" and "back" instead of "Yes" and "No"?

Thanks in advance.
 
Hi revilo, and welcome to the forum!

We can't change the captions on the built in buttons, but we can roll our own msgbox and have complete control over it. I've attached a sample that does exactly that. It doesn't have any error handling, but it will give you an idea of the route you need to go.

If you need any help customizing it (or using it), let us know. :)
 

Attachments

  • xlgf467-1.xls
    43 KB · Views: 39
Thanks Ken, exactly what I need!

and... what if i wanted that to run when the spreadsheet opened, and can you close the spreadsheet when the Sub has finished?
 
If you want to run a routine when the spreadsheet opens, then you need to call it from the Workbook_Open event in the ThisWorkbook module.

If you want to close the workbook when the macro has finished running, then you need to end the routine with one of the following lines just before the End Sub line:

Code:
'To save changes then close the workbook
ThisWorkbook.Close SaveChanges:=True

'To exit without saving changes
ThisWorkbook.Close SaveChanges:=False

Hope that heps!
 
We can't change the captions on the built in buttons, but we can roll our own msgbox and have complete control over it. I've attached a sample that does exactly that. It doesn't have any error handling, but it will give you an idea of the route you need to go.

ACtually, we can, but it is notoriously flaky, so best avoided :)
 
Hey Bob,

My first reaction to the question was "Yes", but then I went back to the OM and didn't see anything in there native. Remind me again how we do this? An API call, or something different? I'm sure I've seen something on this before somewhere, but it was a long time ago..
 
ACtually, we can, but it is notoriously flaky, so best avoided :)

Fair enough! With what Ken gave me, and some copy/paste & fiddling around, i am sure i can get what i need in the future!

then you need to call it from the Workbook_Open event in the ThisWorkbook module

Ken, would you be able to explain this a little more? I have tried playing around with it, but i can't seem to be able to get it to work.
 
Yeah, you bet.

The Workbook_Open event fires when the workbook is opened up. To access it, you need to go into the Visual Basic Editor, and browse into the ThisWorkbook module. The Workbook_Open routine looks like this:

Code:
Private Sub Workbook_Open()
'Call the custom msgbox here
    MsgBox ("You selected " & GetMsgResult("Click a button", "Back", "Next", "Custom msg box"))
    
End Sub

I've attached an updated example file with the routine called from the ThisWorkbook module as well. If you need any help adapting this to work with your solution, just let us know. :)
 

Attachments

  • xlgf467-2.xls
    45 KB · Views: 20
Perfect! I have another question but its not relevant to this thread. Thanks for your help on this!
 
Hey Bob,

My first reaction to the question was "Yes", but then I went back to the OM and didn't see anything in there native. Remind me again how we do this? An API call, or something different? I'm sure I've seen something on this before somewhere, but it was a long time ago..

Your wish is ...

Code:
Option Explicit

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

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
   
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 cMsgBox(hWnd As Long, _
                            mMsgbox As VbMsgBoxStyle, _
                            Title As String, _
                            Prompt As String, _
                            Optional mMsgIcon As VbMsgBoxStyle, _
                            Optional Button1 As String, _
                            Optional Button2 As String, _
                            Optional Button3 As String) As String
    '---------------------------------------------------------------------------
    ' Function:     Controls the display of the custom MsgBox and returns the
    '               selected button
    ' Synopsis:     Sets supplied custom parameters and returns text of
    '               the button that was pressed as a string
    '---------------------------------------------------------------------------
    Dim mReturn As Long
    
        mbFlags = mMsgbox
        mbFlags2 = mMsgIcon
        mTitle = Title
        mPrompt = Prompt
        But1 = Button1
        But2 = Button2
        But3 = Button3
        
        'show the custom messagebox
        mReturn = MessageBoxH(hWnd, GetDesktopWindow(), mbFlags Or mbFlags2)
        
        'test which button of the 7 possible options has been pressed
        Select Case mReturn
            Case vbAbort
                cMsgBox = But1
            Case vbRetry
                cMsgBox = But2
            Case vbIgnore
                cMsgBox = But3
            Case vbYes
                cMsgBox = But1
            Case vbNo
                cMsgBox = But2
            Case vbCancel
                cMsgBox = But3
            Case vbOK
                cMsgBox = But1
        End Select
        
    End Function

'-------------------------------------------------------------------------------
Public Function MessageBoxH(hWndThreadOwner As Long, _
                            hWndOwner As Long, _
                            mbFlags As VbMsgBoxStyle) As Long
'-------------------------------------------------------------------------------
' 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
'-------------------------------------------------------------------------------
'Function:      Formats and shows the custom messagebox
' Synopsis:     Setups the window text
'               Setups the dialog box text
'               Checks which buttons have been added to messagebox (choice of 6
'               combinations ofthe 7 buttons), then sets the button text
'               accordingly
'               Then removes the hook
'-------------------------------------------------------------------------------

    If uMsg = HCBT_ACTIVATE Then
        SetWindowText wParam, mTitle
        SetDlgItemText wParam, IDPROMPT, mPrompt
        
        Select Case mbFlags
            Case vbAbortRetryIgnore
                SetDlgItemText wParam, vbAbort, But1
                SetDlgItemText wParam, vbRetry, But2
                SetDlgItemText wParam, vbIgnore, But3
            Case vbYesNoCancel
                SetDlgItemText wParam, vbYes, But1
                SetDlgItemText wParam, vbNo, But2
                SetDlgItemText wParam, vbCancel, But3
            Case vbOKOnly
                SetDlgItemText wParam, vbOK, But1
            Case vbRetryCancel
                SetDlgItemText wParam, vbRetry, But1
                SetDlgItemText wParam, vbCancel, But2
            Case vbYesNo
                SetDlgItemText wParam, vbYes, But1
                SetDlgItemText wParam, vbNo, But2
            Case vbOKCancel
                SetDlgItemText wParam, vbOK, But1
                SetDlgItemText wParam, vbCancel, But2
        End Select
        
        UnhookWindowsHookEx MSGHOOK.hHook
        
    End If
    
    MsgBoxHookProc = False
    
End Function

Sub Test()
Dim mReturn As String

    mReturn = cMsgBox(1, _
                       vbYesNoCancel, _
                       "Customize your message box buttons", _
                       "Do you not agree that this is pretty cool?", _
                       , _
                       "I Like It", _
                       "Not For Me", _
                       "I'll Get Back To You")
    cMsgBox 1, _
            vbOKOnly, _
            "Customize your message box buttons", _
            "You selected the '" & mReturn & " 'button", _
            , _
            "Okay"
    
End Sub


Sub test2()
Dim a
Dim ary
    ary = Array("vbOKOnly", "vbOK", "vbCancel", "vbAbort", "vbRetry", "vbIgnore", "vbYes", "vbNo")
    a = MsgBox("Hello", vbAbortRetryIgnore)
    MsgBox (ary(a))
End Sub
 
Hmmm... it's starting to come back to me now... Interesting but I think I'll stick with rolling my own, especially if this is notoriously flaky. ;)
 
Back
Top