Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Button Text

  1. #1

    Button Text



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,027
    Articles
    79
    Blog Entries
    14
    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.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    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?

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,027
    Articles
    79
    Blog Entries
    14
    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!
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Quote Originally Posted by Ken Puls View Post
    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

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,027
    Articles
    79
    Blog Entries
    14
    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..
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    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.

  8. #8
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,027
    Articles
    79
    Blog Entries
    14
    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.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  9. #9
    Perfect! I have another question but its not relevant to this thread. Thanks for your help on this!

  10. #10
    Quote Originally Posted by Ken Puls View Post
    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

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •