Pressing Buttons and Keeping Control

What happens when a Button is pressed ?

When a button is pressed a value is returned to variable.

In our example

sub dialog
response=MsgBox("I'm sorry I can't do that Dave",0,"Hal 2001")
end sub

The variable Response is given the value 1 when the button is pressed.

Here is a list of all the values associated wit the various buttons pressed

Value	Button Selected
1	OK
2	Cancel
3	Abort
4	Retry
5	Ignore
6	Yes
7	No

If the dialog box displays a Cancel button, pressing the ESC key has the same effect as choosing Cancel.

We can cause Excel to respond to these values.

For example try entering the following onto the module sheet and running the macro called Yesno

Sub yesno()
response= Msgbox("Ready for the next exercise ?",4,"Exercises")
If response =6 then Cells(1,1).Value="You pressed the Yes button"
If response =7 then Cells(1,1).Value="You pressed the No button"
End Sub

The dialog box should appear as follows

When a button is clicked text should appear in cell A1 acknowledging which of the buttons was pressed.

This is a powerful way of controlling how the user interacts with your spreadsheet as the only choices are the ones you give them via the dialog buttons.

---

Practice Exercises

You should attempt exercises 12.4 and 12.5

---