Use Excel Macro to Show a Message When Necessary

Uploaded by contextures on 14.05.2010

In this work book
we want to make sure that people have entered a customer name here before they print the worksheet
so we
created a macro that
shows a message box
and reminds people to select a customer.
So to see that macro
I can click
the macros command
Here's my macro. When I click Run
it shows the message with a warning red X. It says
Select a Customer Name
and I click OK
But right now it's showing that message whether or not a customer has been selected and we
don't want to bother people or slow them down if they have already selected a customer
So we're going to
revise that macro
so it checks this cell to see if there is a customer name there
We can't record all of that
We're going to see
how the macro recorder refers to a worksheet and to a cell
so in the mac...we're going to record a macro where we select this sheet and this cell. So to do that
we're going to click away from the cell
and select a different sheet.
And then I'll record. I'll come down and click
the Start Recording button.
We'll call this
Test because we aren't really going to keep this macro we're just going to see how it works.
Click OK.
And now we want to select the Order Form sheet and select
cell B5 which is where the customer name goes
and that's all we're going do. We'll click Stop Recording.
To see that macro now, I'll click Macros again
and there's Test.
And click Edit.
And here are the two lines that were created and the rest is just
empty rows and these are little comments that the recorder put in there for us.
So to refer to a sheet it says Sheets
and then in double quotes the name of the sheet and we did a select of that sheet so it shows
the Select
at the end of that line and and then we selected cell B5
so it's called Range("B5") in the code.
So we're going
select that code we created
and copy it. So I'm just pressing in Ctrl + C
And then I'll go to my original module -
and here's my message box code.
Now I wanted to check that cell before it shows the message box, so I'm going to click at
the start of that line and press Enter to create a blank line.
I'll paste in
those two
line that I copied.
Now, instead of having two separate lines we're going to combine them. So, I'm going to click
just after that
and delete
the end of that line
and the start of the next line.
I'm just pressing the Delete key.
And now this is
Sheets("Order Form").Range("B5")
And we don't want to select in our macro; we want to check a value in that cell. So we're
going to double click on Select
and type Value
to check the value we want to see if it's an empty cell. So, I'm going to type an equal sign
and two double quotes, and that will check to see.
So, right now
we're looking at Order Form sheet, cell B5
and we're going to test the value to see if it's empty.
So, to check it, at the beginning of this line I'm going to type If just as we would in
a formula on the worksheet,
At the end of the line I'll click,
type a space,
and the word Then -- T H E N
So, If, that sheet, that cell
is empty then
show the message box
and to finish that I'll just type
End If, so we're just
completing that little sequence we created.
And to make it easier to read, you can
click at the start of that line
and press the tab key to indent it.
So now we're going to check
our cell to see if it's empty. If it is empty it'll show the message box
and then
the macro will end.
I'm going back to Excel.
We have something in the cell now.
A customer name has been selected so when I go to CustomerMessage and run it
nothing shows up, because
we have a customer name.
If I
delete that
and now try and run the macro, CustomerMessage, Run
it warns me to select a customer name. So, I can go back and
complete the worksheet before I print it.