Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Message box wil Yes/No option? (97)

    Is it possible to have a message box pop up with a yes or no option?

    And can the macro call on a certain other module based on the answer given?

    Can the question be something other than yes or no, and if so, can there be more than two options (each calling on a separate macro)?

    Thanks in advance. Much appreciated.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Message box wil Yes/No option? (97)

    MsgBox(prompt[, buttons] [, title] [, helpfile, context])

    provides OK, Yes, No, and Cancel options, along with Information symbol, Exclamation symbol, Critical symbol, etc., visual cue options, and user clicks in respons to Yes, No, Cancel can be handled by code or call other routines. See the VBA MsgBox Help for all the options.

    However, if you want to go beyond what MsgBox provides you will need to design a Userform to ask your own questions and handle user responses.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Message box wil Yes/No option? (97)

    In Excel VBA help, look up the MsgBox function:

    MsgBox(prompt[, buttons] [, title] [, helpfile, context])

    The optional Buttons argument determines what buttons are displayed, and what icon is shown.

    For buttons, you can choose between:
    vbOKOnly (default; this is used if you omit the Button argument)
    vbOKCancel
    vbAbortRetryIgnore
    vbYesNoCancel
    vbYesNo
    vbRetryCancel

    I hope these are self-explaining. If you need other buttons than these, you will have to create a user form.

    The MsgBox function returns a value that indicates which button has been pressed by the user. Possible values are:
    vbOK
    vbCancel
    vbAbort
    vbRetry
    vbIgnore
    vbYes
    vbNo

    You can use this as follows:

    Dim intResult As Integer
    intResult = MsgBox("Save this result?", vbYesNoCancel + vbQuestion)
    Select Case intResult
    Case vbYes
    ' code to save results go here
    Case vbNo
    ' code to throw results away go here
    Case vbCancel
    ' code to cancel goes here - usually do nothing
    End Select

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Message box wil Yes/No option? (97)

    Here is a demo userorm with yes no maybe as buttons

    Steve
    Attached Files Attached Files

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Message box wil Yes/No option? (97)

    Yes (easy), Yes (easy), Yes (hard), and Yes (hard). See the API link in this thread.

    Here's some sample code for the easy Yeses: HTH --Sam
    <pre>Option Explicit

    Sub Macro1()
    Dim Rsp As VbMsgBoxResult
    Rsp = MsgBox("Were you good this year?", vbYesNoCancel)
    If Rsp = vbYes Then
    SaidYes
    ElseIf Rsp = vbNo Then
    SaidNo
    End If
    End Sub

    Sub SaidYes()
    MsgBox "Present"
    End Sub

    Sub SaidNo()
    MsgBox "Coal"
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Message box wil Yes/No option? (97)

    Sam,
    I tried the sample macro you posted and it failed. To get it to work on my Excel (97) I had to remark out the last part of the Dim statement (see below).
    Dim Rsp 'As VbMsgBoxResult

    Maybe you developed under higher level Excel.
    It is a handy macro to build on.

    I just love this site for all the information that is available here.

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Message box wil Yes/No option? (97)

    Sorry about that, dim it as Integer as the Help file states. Leave the Option Explicit at the top of the module. It will save you much more time by identifying typing mistakes than it will cost you having to declare all of your variables. Have fun! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Message box wil Yes/No option? (97)

    You can use

    Dim Rsp As Long

    in preference to just Dim Rsp, which defaults to a variant.

    Andrew C

Posting Permissions

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