the classic vbYes and vbNo macro

[Daniel Cremin]

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.

[Daniel Cremin]

dyou know Dave, i expect you do

[Tom Urtis]

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

[Daniel Cremin]

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

[Tom Urtis]

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.

[Dave Hawley]

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.

[Daniel Cremin]

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

[nisht]

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


nishith desai
http://www.pexcel.com

[Cosmos75]

Cool!