Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: the classic vbYes and vbNo macro

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    hi ive got a delete student macro that i need to build - the actual code is no problem - a simple For...Next and If combination. The problem is that i need a little confirmation dialog box to pop up with Yes and No buttons - and when i click Yes for it to run the code but if No then to display another message box saying its cancelled and then exit sub. ive been trying to do it all day, looking at books but excel wont let me write the msg box with the Yes/No buttons as it keeps asking for the msg box to be equal to something. How can i accomplish it all in one single macro rather than a few? Thanks very much in advance.

    Daniel C.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    dyou know Dave, i expect you do

  3. #3
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try something like this:

    Sub MessageBoxMania()

    Dim Resp As Integer
    Resp = MsgBox("Are you sure you want to proceed?", 36, "Please confirm")
    If Resp = 6 Then

    'Yes was pressed, your macro goes here

    Else
    'No was pressed, here's message tellingthem action is cancelled.
    MsgBox "Please click OK, no harm no foul.", 64, "Macro cancelled."
    End If

    End Sub


  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    cheers tom ! how come you use numbers 36 and 64 do they represent vbyes and vbno?

  5. #5
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It's a habit I got into; those particular numbers represent icons on the message box, one for vbQuestion with a YesNo button combination, and the other for vbInformation, with just an OK button.

    After I got used to those button integer values, it kinda stuck in my head as more logical and efficient to type a 2 digit number than all those boring code words.

    The 6 refers to vbYes.

    Just a personal preference for code writing.

  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Daniel

    I see Tom has already supplied you answer, but the VbYes VbNo VbCancel VbOk etc etc all have values, as Tom has shown. I am more inclined to pasre VbYes or VbNo myself as it makes the code a bit easier to read.




  7. #7
    Board Regular
    Join Date
    Feb 2002
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thanks to both of you... Ill get back to coding in a minute...

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    May below code work for you...


    Private Sub workingmessagebox()
    Dim msg As String
    Dim response As Integer

    msg = "Was this file opened up through " & vbCrLf
    msg = msg & "Excel or was it double clicked on."


    response = MsgBox(msg, vbYesNoCancel + vbInformation)

    ' It is easy to work with select when there is several
    ' conditions to check.
    ' practicaly it gives same performance as if ...endif
    'but the code with select statement is easy to read
    ' if you need more help in message box type "msgbox"
    ' in search box of agent you will given full help
    ' how combobox works.

    Select Case response
    Case Is = vbYes
    MsgBox ("You pressed yes button")
    Case Is = vbNo
    MsgBox ("you pressed no button")
    Case Is = vbCancel
    MsgBox ("You pressed cancel button")
    End Select

    'I have written code for if....elseif...then..endif
    ' you can remove coment marks and try with this also


    'If response = vbYes Then
    '
    'MsgBox ("You pressed yes button")
    'ElseIf response = vbNo Then
    'MsgBox ("You pressed No button")
    'ElseIf response = vbCancel Then
    'MsgBox ("You pressed Cancel button")
    '
    'End If
    ' hope you will enjoy
    End Sub


    ni****h desai
    http://www.pexcel.com


  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Cool!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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