Results 1 to 3 of 3

Thread: Unable to add OK/No button to existing Msg Box in Excel 2010

  1. #1

    Thread Starter
    New Member
    Join Date
    Sep 2014
    Posts
    1

    Unable to add OK/No button to existing Msg Box in Excel 2010

    Hi! Everyone

    I have this customized welcome message in Excel 2010 and I would like to add Ok and Cancel button with a message "Do you wish to Continue" below my customized message.

    If Ok, then enter the workbook and if cancel then close the workbook.

    Code:
    Private Sub Workbook_Open()
        Dim sName As String
        Dim sTxt As String
        Dim CurrTime As Long
        Dim NameOfWorkbook
        
        NameOfWorkbook = Left(ThisWorkbook.Name, (InStrRev(ThisWorkbook.Name, ".", -1, vbTextCompare) - 1))
        sName = Environ("UserName")
            
        CurrTime = Hour(Now)
        Select Case CurrTime
        Case Is > 18
            sTxt3 = "Disclaimer: This document is a property of Selected Personnel. This contains confidential information and is intented only for the authorised personnel.  If you are not the intended personnel you are notified that disclosing, disseminating, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited."
            sTxt = "Good Evening ! "
            sTxt1 = "Welcome  "
            sTxt2 = "To - "
             End Select
    
        MsgBox Format(Now(), "dd/mmmm/yyyy             HH:mm:ss AM/PM") & vbCrLf & vbCrLf & sTxt & vbCrLf & vbCrLf & sTxt1 & sName & vbCrLf & vbCrLf & sTxt2 & NameOfWorkbook & Chr(10) & vbCrLf & vbCrLf & sTxt3
         
    End Sub
    Any help would be grateful.
    Last edited by Ravana; Sep 21st, 2014 at 01:46 AM.

  2. #2
    PowerPoster
    Join Date
    Sep 2006
    Location
    Egypt
    Posts
    2,579

    Re: Unable to add OK/No button to existing Msg Box in Excel 2010

    Use IF statement with MsgBox

    Code:
        If MsgBox("Do you wish to Continue?", vbOKCancel) = vbOK Then
            ' enter the workbook.
        Else
            ' close the workbook.
        End If
    If the message box contains 3 buttons, receive the user answer in a variable and check it

    Code:
       Dim a As Integer
       
       a = MsgBox("Do you wish to Continue?", vbYesNoCancel)
        If a = vbYes Then
            ' Yes
        ElseIf a = vbNo Then
            ' No
        Else
            ' Cancel
        End If
    Last edited by 4x2y; Sep 21st, 2014 at 04:32 PM.



  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Unable to add OK/No button to existing Msg Box in Excel 2010

    Welcome to VBForums

    Thread moved from the 'VB.Net' forum to the 'Office Development/VBA' forum.

Posting Permissions

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



Click Here to Expand Forum to Full Width