the classic vbYes and vbNo macro

Daniel Cremin

Board Regular
Joined
Feb 23, 2002
Messages
64
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.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top