Icons are a valuable addition
to a message box, providing a visual cue to the user. Adding an
icon can often mean that a simpler message is required. Four different
icons are available:
|
vbInformation
|
vbQuestion
|
vbExclamation
|
vbCritical
|
The "Information" icon lets
the user know that the message is providing information and requires
no action other than an acknowledgement. The "Question" icon tells
the user that they have to make a decision. The "Exclamation" and
"Critical" icons display different levels of alarm. Only one icon
can be displayed at a time.
Six different combinations
of buttons are available:
|
|
vbOKOnly
|
|
|
vbOKCancel
|
|
|
vbYesNo
|
|
|
vbYesNoCancel
|
|
|
vbRetryCancel
|
|
|
vbAbortRetryIgnore
|
In addition, a "Help" button
can be added to any of these button combinations:
|
|
vbYesNo + vbMsgBoxHelpButton
|
There are two aspects to
programming a message box. One that merely serves to convey information
will probably carry just an "OK" button and its programming consists
of a specification of its design and its message. A message box
that is designed to take some input from the user will have more
than one button, and its programming will also include the interpreting
and processing of the user's input.
A message box will close
when the user clicks one of the buttons. The exception is the "Help"
button which does not close the message box when clicked. Instead,
its purpose is to display help to the user before they decide which
button to click. If a Help button is used the programming must include
information about where the help is located.
The Message box can accept
up to 5 arguments but all except Prompt are optional:
|
Prompt
|
The
Prompt is the text of the message itself and should
be enclosed in quotes ("). The message can be up to 1024
characters long. Although the text will automatically wrap
within the message box, you can control the flow of text
by inserting commands that force a new line (see:
Multi-line Messages below).
|
|
Buttons
|
The
Buttons argument (optional) is used to specify the chosen
combination of buttons to appear on the message box, as
well as a choice of icons, and which button is to be the
default. This information is supplied as VBA constants,
a list of which appears when you enter the buttons argument.
Choose more than one option by entering a plus sign (+)
between constants, e.g.:
vbYesNo + vbQuestion.
If no buttons are specified the default
vbOKOnly
is displayed.
|
|
Title
|
The
Title argument (optional) is the text that appears in
the title bar at the top of the message box and should be
enclosed in quotes ("). The amount of text possible is limited
by the width of the message box and depends upon your display
settings. With normal Windows settings about 65 characters
can be displayed.
|
|
Help
File
|
If a Help button
is displayed, this argument is used to specify the path
to the relevant Help file. The user can also access this
help by pressing the F1 function key whilst
the message box is open (even if no Help button is used).
This argument is used in combination with the
Context argument.
|
|
Context
|
The
Context argument gives the location of the help within
the help file specified in the previous argument. Both
Help File and Context arguments
are optional but if used, both arguments
must be supplied.
|
Insert the code for the
message box at the point in your procedure where the message is
to appear.
1.
On a new line enter the word
MsgBox
and type a space. Help is displayed showing the various arguments
required:
2.
Type the text of the message enclosed
in quotes, followed by a comma and a space to display the list of
choices, and scroll to the item you want to display:
3.
Double-click the item you want
to insert, then type a comma and space and enter the text of the
title in quotes:
As with all VBA code you
should not finish a line of code with a comma. Typing a comma after
the closing quote mark of the title would imply that you were going
to supply another argument, and the Visual Basic Editor would display
an error message if you then failed to do so.
The resulting message box
looks like this:
If you want to skip one
or more arguments, accepting the defaults, type commas to move you
from one argument to another, but without entering an argument e.g.:
MsgBox "The macro is
complete", , "Job done"
This code omits the choice
of icon. The resulting message box looks like this:
Note that even though no
button combination is specified, the default "OK" button is displayed.
The flow of message text
can be controlled using special commands. This can be done in several
ways, my preference being to use the vbCrLf constant.
The message is split into sections, each enclosed in quotes, and
the command inserted between the sections using the ampersand (&)
character, e.g.:
MsgBox "The macro is
complete." _
& vbCrLf
& "Please remember to save the file." _
& vbCrLf & "Have a nice day!" _
, vbInformation, "Job done"
The resulting message box
looks like this:
The command can be doubled-up
to create an empty line in the message, e.g.:
MsgBox "The macro is
complete." _
& vbCrLf
& "Please remember to save the file." _
& vbCrLf & vbCrLf & "Have a nice day!" _
, vbInformation, "Job done"
The resulting message box
looks like this:
Note how, in the previous
code example, the code has been written on several lines using the
space underscore combination ("
_") to denote a break in the code line. This has no bearing
on the way the message box is displayed and is done to make the
code easier to read, as is the indenting of code lines. Remember
that code lines should not be broken between pairs
of quote marks.
As an alternative to the
vbCrLf (carriage return+line feed) command you can
use vbCr (carriage return), vbLf
(line feed), Chr(13) (carriage return),
Chr(10) (line feed) or Chr(13) & Chr(10)
(carriage return+line feed) commands. All essentially do the same
thing although they may not work in the same way in all circumstances.
Choose whichever you prefer.
When using multiple buttons
it is necessary to process the user's response. When the user clicks
one of the buttons a value equivalent to one of the VBA constants
is returned is returned. There is one for each type of button:
VbOK
= 1, vbCancel = 2, vbAbort =
3, vbRetry = 4, vbIgnore
= 5, vbYes = 6, vbNo = 7
You can then refer to this
response either by its numerical value or by its name. I prefer
the latter as it is easier to understand when reading code. The
value can be stored in a variable and evaluated with an
If Statement or a Case Statement.
This example uses an
If Statement to evaluate the user's response to
determine whether or not to continue with the procedure of deleting
the current worksheet:
Sub
DeleteSheet()
Dim
conResponse As VbMsgBoxResult
conResponse = MsgBox("You are about to delete the current
sheet." _
& vbCrLf & "Do you wish to continue?" _
, vbQuestion + vbYesNo, "Are you sure?")
If conResponse = vbNo
Then
Exit
Sub
Application.DisplayAlerts
=
False
ActiveSheet.Delete
Application.DisplayAlerts
=
True
End
Sub
In the first line the variable
is declared with the data type
vbMsgBoxResult.
This data type was introduced with Excel 2000. If you are using
Excel 97 you could use
String
or
Variant,
or
Integer
if you were going to refer to the numeric value of the constants.
The next line places a value
into the variable by displaying a message box (note the use of brackets
enclosing the message box arguments. This is obligatory when
MsgBox
follows an equals (=) sign). The message box looks like this:
The If Statement
checks to see if the user chose "No" and if so the macro exits,
otherwise it continues (because if the user didn't choose "No" the
must have chosen "Yes").
The line of code that deletes
the active sheet is bracketed by commands to turn off, and then
turn on alerts. This prevents the usual warning being displayed
before the sheet is deleted. If you use this command to turn off
alerts you must remember to include the command
to turn them on again, otherwise no more warnings will be displayed
until you restart Excel!
The next example uses a
Case Statement to evaluate the user's response.
The macro inserts the current date into the active cell, giving
the user the choice of formatting the date. The user can choose
"Yes", "No" or "Cancel". In this example the button choices are
referred to by their numerical values.
Sub
InsertDate()
Dim
intResponse
As
Integer
intResponse = MsgBox("Would
you like the long date format?" _
, vbQuestion + vbYesNoCancel, "Inserting the date...")
Select
Case
intResponse
Case
6
' "Yes"
chosen
ActiveCell.Value = Format(Date, "dddd, dd mmmm yyyy")
Case
7
' "No"
chosen
ActiveCell.Value = Date
Case
2
' "Cancel"
chosen
Exit
Sub
End
Select
With
ActiveCell.Font
.Bold =
True
.Italic =
True
End
With
End
Sub
The first line declares
the variable as an
Integer.
The next line places a value into the variable by displaying a message
box. The message box looks like this:
The user's response (6 =
"Yes", 7 = "No", 2 = "Cancel") is evaluated with a Case
Statement which enters a date with or without formatting it
according to the user's instruction. If the user clicks the "Cancel"
button, the macro is cancelled without a date being entered. If
they chose "Yes" or "No" it goes on to format the active cell's
font.
NOTE: If the user presses
the Escape key on their keyboard, it is the equivalent
of clicking the "Cancel" button. The message box will close and
any programming associated with this button will run.
The Buttons
argument of a message box can be used to specify which button
is the default (i.e. already highlighted). This is useful where
users are prone to hit the Enter key to quickly
dismiss a message box without reading it, or where you want to prompt
a user to make a particular choice. Most people quickly learn that,
if they are not sure what to do next, pressing the default button
is probably best.
If the code of the last
example was modified as follows:
intResponse = MsgBox("Would you like the long date format?"
_
, vbQuestion + vbYesNoCancel + vbDefaultButton3 _
, "Inserting the date...")
... the third button (vbDefaultButton3)
would be the default and already highlighted when the message box
appeared:
A message box can display
up to four buttons, and any one can be designated the default.
The creation of custom help
is an advanced topic involving the use of special help compiling
software. If you want to create your own help you can use the tools
supplied with Microsoft Office Developer Edition, download the tools
from Microsoft's web site, or use one of the many third-party applications
available (some of which are shareware or freeware).
You can, however, offer
a link to the built-in help. When programming the "Help" button
you need to supply two pieces of information: HelpFile
(the name and location of the file that contains the help), and
Context (an ID number – like a bookmark – to identify
the individual help topic).
This information differs
between versions of Excel. Excel 97 uses the old-style Windows help
files (the main help file named Xlmain8.hlp); Excel
2000 and 2002 both use the newer HTML help files (named
Xlmain9.chm and Xlmain10.chm respectively).
You should check the location of these files on your particular
installation.
To ascertain the Context
ID of the appropriate bit of help is not so easy. Various authors
have compiled helpful lists (for example see: http://j-walk.com/ss/excel/tips/tip89.htm).
In Excel 2000/2002 you can navigate to the desired help then right-click
on the help page and choose View Source. Look for
a reference to "Tnum" followed by a 6-digit number
somewhere near the top of the source code.
The message box code in
the last example, modified to include a help button linked to relevant
help would look something like this:
intResponse = MsgBox("Would
you like the long date format?" _
, vbQuestion + vbYesNoCancel + vbMsgBoxHelpButton _
, "Inserting the date..." _
, "C:\Program Files\Microsoft Office\Office10\1033\XLMAIN10.CHM"
_
, 5198679)
NOTE: When the "Help" button
is clicked the message box remains open. All other buttons cause
the message box to close when clicked.
Message boxes are
Modal, meaning they are always "on top" of the active
window. Two further constants can be applied to the message box:
vbApplicationModal
and
vbSystemModal.
All VBA message boxes are ApplicationModal by default.
When a message box is open the user can not activate or work in
the current program until the message box is closed. It is possible,
however, to switch to other programs and work normally. If
SystemModal is specified the message box remains
on top even when switching to other programs, although it does not
prevent the user from working in another program.
It is important to remember
that, even when the display of a message box is the last action
or a macro, the procedure is still running as long as the message
box is open. When you are testing your code, you will find that
an open message box will prevent you from working in the Visual
Basic Editor.
This exercise demonstrates
the use of VBA Message Boxes (and an Input Box) to interact with
the user. Message boxes are used to gather and process information,
and provide feedback to the user allowing them to control the running
of a macro.
Although this exercise uses
Excel, the techniques it uses can be applied equally to any Office
program.
The exercise starts by adding
data to an empty workbook. Then a basic macro is written, and gradually
improved by adding user interaction.
1.
Open Excel and in a new workbook.
Use the keyboard shortcut Ctrl+G to open the
Go To dialog. You cursor will already be in the
Reference box. Type: A1:A10000 and click
OK. This selects the range A1:A10000.
2.
Do not click on the worksheet!
Type the formula: =INT(RAND()*1001)
and press Ctrl+Enter. The key press Ctrl+Enter gives
the command Block Fill which fills the entire selection
with the entry you typed. The formula has filled the selected range
with random whole numbers between 1 and 1000.
3.
The range is still selected. Press
Ctrl+C (or right click the selection and choose
Copy), then right-click the selection and choose
Paste Special (or go to Edit > Paste
Special). Choose Values from the Paste Special
dialog and click OK. Press Esc to
cancel Copy Mode.
You have filled ten thousand
cells in column A (from A1 to A10000) with random whole numbers
between zero and 1000. The reason for using Paste Special was to
remove the formulas behind the visible numbers. This is important
because the RAND
function is one of Excel's volatile functions, one
which recalculates itself whenever the worksheet calculates. If
you left the formulas in place the numbers would change each time
another calculation was performed. Removing the formulas effectively
"freezes" the numbers.
This macro will loop through
the column of numbers. When it finds a number larger that a specific
value, the number is copied into the next column, making a continuous
list of numbers.
4.
Open the Visual Basic Editor (Keys:
Alt+F11). Locate and select the current workbook
in the Project Explorer window and choose
Insert > Module. In the new module's code window
type:
Sub MessageDemo
and press Enter.
The Visual Basic Editor adds the
End Sub
statement, placing your cursor in the empty line between the two
statements.
5.
First, you need to declare the
variables that will be used in the macro. Press Tab
to indent your code and type:
Dim i As Integer
Dim x As Integer
6.
Add the following lines to set
the value of both variables to zero:
i = 0
x = 0
7.
Add the line:
Range("A1").Select
This starts the macro by making
sure that the correct cell (A1) is selected. Cell A1 is now the
Active Cell.
8.
Now type the loop code that does
the main work of the macro:
Do
If ActiveCell.Offset(i, 0).Value >
900 Then
ActiveCell.Offset(i,
0).Copy Destination:=ActiveCell.Offset(x, 1)
x = x + 1
End If
i = i + 1
Loop
Until IsEmpty(ActiveCell.Offset(i, 0))
This procedure uses a
Do...Loop
Until loop to repeat the macro
code until it finds an empty cell in column A. The data in column
A contains no gaps so this method will loop through all the data.
The If Statement
checks to see if a cell contains a value greater than 900. The cell
that gets checked depends upon the value of the variable
i. This is achieved by defining the cell's address
location as
ActiveCell.Offset(i,
0). The value if i
is going to change each time the loop repeats. It starts with a
value of zero, so the first cell to get checked is
ActiveCell.Offset(0,
0) which is cell A1 itself (no
offsets). The next cell is
ActiveCell.Offset(1,
0) which is cell A2, and so on.
Later in the loop the line
i = i + 1
increments the value of the variable by one.
If the If Statement
finds that the cell contains a suitable number, it copies the value
to another cell using a similar method to decide its address. This
uses the value of the variable x. The value of
x is going to change each time a number is copied.
This is done with the line
x = x + 1.
Note that x is inside the
If Statement so its value is incremented
only when a number is copied, unlike
i which is incremented ever time the loop cycles. The value
of x starts at zero, so the first cell to receive
a number is
ActiveCell.Offset(0,
1) which is cell B1. The next cell
is
ActiveCell.Offset (0,
2) which is cell B2, and so on.
9.
Type the following line to display
a confirmation message when the macro finishes:
MsgBox "The values have
been copied to column B" _
, vbInformation, "Macro Complete"
It is good practice to add
confirmation messages to macros when it is not always obvious to
the user that the macro has finished.
You finished code should
look like this:
Sub MessageDemo()
Dim i As Integer
Dim x As Integer
i = 0
x = 0
Range("A1").Select
Do
If ActiveCell.Offset(i,
0).Value > 900 Then
ActiveCell.Offset(i, 0).Copy Destination:=ActiveCell.Offset(x,
1)
x = x
+ 1
End If
i = i + 1
Loop Until IsEmpty(ActiveCell.Offset(i,
0))
MsgBox "The values have been
copied to column B" _
, vbInformation, "Macro Complete"
End Sub
This is a good time to check
your code and test run the macro...
10.
First let the Visual Basic Editor
carry out a check of the code by choosing Debug > Compile
VBAProject. This causes the code compiler to perform a "dry
run" of the macro. Whilst not completely infallible, this tool helps
to locate any undeclared variables, spelling errors or other typos
before running the macro for real.
11.
Switch to Excel and make sure that
you are on the worksheet containing the column of numbers. Press
Alt+F8 (or go to Tools > Macro > Macros)
to open the Macros dialog and select
MessageDemo. Click Run.
When the macro finishes
click the OK button on the confirmation message
box to close the box and terminate the macro.
12.
Clear the contents of column B,
ready for the next test, but leave column A intact.
This step uses the VBA
Timer function to calculate how long the macro takes
to run. Timer returns the number of seconds since
midnight.
You will write code to make a note of this is value at the start
of the macro, and again when the macro finishes, subtracting one
from the other to give the total time elapsed.
You will code to message
boxes. One will ask the user if they want to time the macro, the
other will display the elapsed time.
13.
This procedure requires two new
variables. Add the following lines after the existing variable declarations
at the start of the macro:
Dim Response As VbMsgBoxResult
Dim StartTime As Double
If you are using Office 97, change
VbMessageBoxResult
to
Variant
(the former VBA data type was not introduced until Office 2000).
14.
Add the following lines
before the line
Do
(the start of the loop):
Response = MsgBox("Would
you like to time this operation?", vbYesNo)
StartTime = Timer
The first line uses a "Yes/No" message box to put a value into
the
Response
variable. The second line makes a not of the time by placing the
Timer
value in the
StartTime
variable.
15.
Move to the end of the loop, to
the line for the confirmation message box (MsgBox
"The values have been copied...
) and type these additional lines so the result looks like this:
If Response = vbNo Then
MsgBox "The values have been copied
to column B" _
, vbInformation, "Macro Complete"
Exit Sub
End If
This code uses an If Statement to check the
users response to the earlier message box asking whether they wanted
to time the macro. The user's answer was stored in the
Response
variable. If the user answered "No" they are shown the confirmation
message and the macro is terminated with the line
Exit Sub.
If the user answered "Yes" the macro continues without displaying
the confirmation message.
16.
Add this final line to the end
of the macro to display a different confirmation message, showing
how long the macro took:
MsgBox "The procedure
took " & Timer - StartTime & " seconds." _
, vbInformation, "Macro complete"
At this point your code
should look like this:
Sub
MessageDemo()
Dim
i
As
Integer
Dim
x
As
Integer
Dim
Response
As
VbMsgBoxResult
Dim
StartTime
As
Double
i = 0
x = 0
Range("A1").Select
Response = MsgBox("Would you like to
time this operation?", vbYesNo)
StartTime = Timer
Do
If
ActiveCell.Offset(i, 0).Value > 900
Then
ActiveCell.Offset(i,
0).Copy Destination:=ActiveCell.Offset(x, 1)
x = x + 1
End
If
i = i + 1
Loop
Until
IsEmpty(ActiveCell.Offset(i, 0))
If
Response = vbNo
Then
MsgBox "The values have
been copied to column B" _
, vbInformation, "Macro Complete"
Exit
Sub
End
If
MsgBox "The procedure took " & Timer
- StartTime & " seconds." _
, vbInformation, "Macro complete"
End
Sub
17.
Compile and test run the macro
as before. This time you are asked a question at the start and shown
a confirmation message at the end. The confirmation message you
see depends upon your answer to the question.
Step 6: Add Statistics to the Confirmation
Message
In this step you will ask
the user if they want to see some macro statistics, by changing
the confirmation message into a question. The variables
i and x have recorded how many cells
were checked and how many numbers were copied. These variables will
form the basis of a message box that the user will see if they answer
"Yes".
18.
Change the last line of the macro
(the confirmation message) as follows:
Response = MsgBox("The
procedure took " & Timer - StartTime _
& " seconds." & vbCrLf _
& "Would you like to see the statistics?" _
, vbQuestion + vbYesNo, "Macro complete")
There are several changes here. The message box is being used
as a function to place a value into the
Response
variable. You can reuse a variable as many times as you want, but
take care to be aware of which value it is holding at any time.
When
MsgBox
is used as a function (i.e. when it follows an equals sign) its
arguments must be enclosed in brackets.
The icon has been
changed to
vbQuestion
and the box will display both "Yes" and "No" buttons, and a question
has been added to the prompt.
19.
After the above lines, type the
following If Statement which examines the response
to the question about viewing statistics:
If
Response = vbNo Then Exit Sub
This is an If Statement at its simplest. If
the user answered "No" then the macro finishes, otherwise it continues.
20.
Finally, add code for a message
box to display the macro statistics which are contained in the variables
i and x:
MsgBox "The macro processed
" & i & " rows." _
& vbCrLf & x & " values were found." _
, vbInformation, "Statistics"
At this point your code
should look like this:
Sub
MessageDemo()
Dim
i
As
Integer
Dim
x
As
Integer
Dim
Response
As
VbMsgBoxResult
Dim
StartTime
As
Double
i = 0
x = 0
Range("A1").Select
Response = MsgBox("Would you like to
time this operation?", vbYesNo)
StartTime = Timer
Do
If
ActiveCell.Offset(i, 0).Value > 900
Then
ActiveCell.Offset(i,
0).Copy Destination:=ActiveCell.Offset(x, 1)
x = x + 1
End
If
i = i + 1
Loop
Until
IsEmpty(ActiveCell.Offset(i, 0))
If
Response = vbNo
Then
MsgBox "The values have
been copied to column B" _
, vbInformation, "Macro Complete"
Exit
Sub
End
If
Response = MsgBox("The procedure took
" & Timer - StartTime _
& " seconds." & vbCrLf _
& "Would you like to see the statistics?" _
, vbQuestion + vbYesNo, "Macro complete")
If
Response = vbNo
Then
Exit
Sub
MsgBox "The macro processed " & i &
" rows." _
& vbCrLf & x & " values were found." _
, vbInformation, "Statistics"
End
Sub
21.
Compile and test run the macro
as before. If you answer "Yes" to the question about timing the
macro at the start, you are shown the time taken when the macro
concludes but this time the confirmation message also asks you if
you want to see statistics. If you answer "Yes" again you will see
a message showing the number of cells checked and values copied.
Step 7: Let the User Choose the Minimum
Number
It is always a good thing
to build into your macros as much flexibility as possible. Until
now the macro has selected numbers greater than 900. In this step
you will ask the user if they would like to specify a minimum number.
To do this you will make use of a VBA Input Box, an object similar
to the message box but which can receive a typed input from the
user. If the user prefers not to choose a number the computer will
choose a number at random and the user will be informed of its choice.
22.
This procedure needs another variable
to hold the user's choice of number (or the computer's choice) so
add the following variable declaration to the list at the beginning
of the macro:
Dim MinNumber As Integer
23.
Enter the code to ask the user
if they want to specify a minimum number. Their choice will be placed
in the existing
Response
variable. It is safe to do this because the macro will have read
the value before the variable is needed again. Type this code near
the start of the macro, above the line
Range("A1").Select
:
Response = MsgBox("The
computer can pick a number or you can." _
& vbCrLf & "Would you like to choose a number yourself?"
_
, vbQuestion + vbYesNoCancel, "Number required")
This message box will be the
first one the user sees, so it includes a "Cancel" button that you
will program to allow the user to cancel the macro.
24.
Immediately underneath the code
you just typed, enter the following If Statement
which will evaluate the users response to the message box and take
three possible courses of action:
If Response = vbCancel Then
MsgBox "You chose to terminate the procedure." _
& vbCrLf & "The worksheet has not been altered." _
, vbInformation, "Macro cancelled"
Exit Sub
ElseIf Response
= vbYes Then
MinNumber
= InputBox("The macro will look for numbers greater " _
& "than the number you choose." _
& vbCrLf & "Please enter a whole number between 1 and 999"
_
, "Enter a number")
Else
Randomize
MinNumber = Int(Rnd * 1000)
MsgBox "The computer chose
" & MinNumber, vbInformation _
, "Number chosen"
End If
The If Statement has three conditions: "vbCancel",
"vbYes" and "anything else" which by definition must mean "vbNo".
If the user chooses "Cancel" They are shown a message box which
confirms that they have cancelled the macro. The macro is then cancelled
with the line
Exit Sub.
If the user chooses "Yes" they are shown an input box inviting them
to enter a number, which is stored in the
MinNumber
variable.
If the user chooses "No" the command
Randomize
initialises the random number generator before a random number is
calculated and stored in the
MinNumber
variable. The user is then notified of the computer's choice of
number.
25.
All that remains is to change the
reference to a minimum number in the loop. Find the number
900
in the code of the loop and change it to the text
MinNumber.
The code for the completed macro is shown on the next page.
26.
Compile and test run the macro
as before. Then run the macro and explore all the different ways
that is can be run by asking the user to make choices.
This exercise has not included
any error handling. It is always important to anticipate any errors
that might arise and write the code to deal with them. The most
obvious errors would arise from the user making an invalid entry
in the Input Box. If the user typed a text entry error 13 (type
mismatch) would occur. If they typed a very large number (greater
than 32767) error 6 (overflow) would occur.
Before distributing a macro
like this you should include code to deal with errors like these,
and notifying the user of their mistake. Error handling it the topic
of another tutorial.
The completed macro should
look like this:
Sub
MessageDemo()
Dim
i
As
Integer
Dim
x
As
Integer
Dim
Response
As
VbMsgBoxResult
Dim
StartTime
As
Double
Dim
MinNumber
As
Integer
i = 0
x = 0
Response = MsgBox("The
computer can pick a number or you can." _
& vbCrLf & "Would you like to choose a number yourself?"
_
, vbQuestion + vbYesNoCancel, "Number required")
If
Response = vbCancel
Then
MsgBox "You chose to terminate
the procedure." _
& vbCrLf & "The worksheet has not been altered." _
, vbInformation, "Macro cancelled"
Exit
Sub
ElseIf
Response = vbYes
Then
MinNumber = InputBox("The
macro will look for numbers greater " _
& "than the number you choose." _
& vbCrLf & "Please enter a whole number between 1 and 999"
_
, "Enter a number")
Else
Randomize
MinNumber = Int(Rnd * 1000)
MsgBox "The computer chose
" & MinNumber, vbInformation _
, "Number chosen"
End
If
Range("A1").Select
Response = MsgBox("Would you like to
time this operation?", vbYesNo)
StartTime = Timer
Do
If
ActiveCell.Offset(i, 0).Value > MinNumber
Then
ActiveCell.Offset(i,
0).Copy Destination:=ActiveCell.Offset(x, 1)
x = x + 1
End
If
i = i + 1
Loop
Until
IsEmpty(ActiveCell.Offset(i, 0))
If
Response = vbNo
Then
MsgBox "The values have
been copied to column B" _
, vbInformation, "Macro Complete"
Exit
Sub
End
If
Response = MsgBox("The procedure took
" & Timer - StartTime _
& " seconds." & vbCrLf _
& "Would you like to see the statistics?" _
, vbQuestion + vbYesNo, "Macro complete")
If
Response = vbNo
Then
Exit
Sub
MsgBox "The macro processed " & i &
" rows." _
& vbCrLf & x & " values were found." _
, vbInformation, "Statistics"
End
Sub