MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Message Box opens twice

  Asked By: Henry    Date: Sep 02    Category: MS Office    Views: 732

I have a stupid problem with a function like this one (I had the
same problem in another similar function).

The function works, but once the user has clicked "OK", the message
box opens again, but only once, then the function ends.
It is annoying for the user, sounds like he/she had not understood
the first time :-)
Please what is wrong in this function?
'Checks if the user entered a date is not later then today
Public Function DateAlert(YourDate, TodayDate)
Dim Msg, Style, Title
Msg = "You entered a date that is later then today!"
Style = vbOKOnly + vbDefaultButton2
Title = "Wrong date!"
If (YourDate > TodayDate) Then
Response = MsgBox(Msg, Style, Title)
End If
End Function



5 Answers Found

Answer #1    Answered By: Abbad Akhtar     Answered On: Sep 02

I can't see anything in your function  that is going to pop up a second
message box. I expect the function is being called twice. Please supply
the relevant calling code too.

I do see that you have defined your routine as a function, rather than a
sub, but you are not returning a value. This is bad practice, and might
also be causing trouble at the calling end.

Also, please declare your variables and parameters as specific types. And
note that commas within a Dim statement do not cause multiple variables to
be declared with the same type - all the variables still need to be given
separate "As" statements.

Answer #2    Answered By: Cais Nguyen     Answered On: Sep 02

You write:
"I expect the function  is being called twice."

You were right on this! The function was really called twice!
I have corrected that and it works!

But your other comments make me curious.

1. You write:
"I do see that you have defined your routine as a function, rather than
sub, but you are not returning a value."

The context:
The person enters a date, let's say in cell C37, that is not allowed to
be later then the cell named Today that (as you could expect) contains
"=today()". So in the spreadsheet cell C38, I write:


If I had written my function as a sub, let's say sub(AlerteDate(Jour,
Aujourdhui), I guess I could not written:
= sub AlerteDate(Jour, Aujourdhui).
I don't think (that would be great in some circumstances) that I could
write in a cell "=sub macroname(Jour, Aujourdhui)".
Could I?

2. You write:
Please declare your variables and parameters as specific types.

In this specific case, the function works  as well without any dim
I copied that from an example in the VB HELP provided with Excel.

But let's assume I would have needed Dim statements, I should have
Dim Jour As Date
Dim Aujourdhui As Date
Dim Msg As String
Dim Title As String
Dim style  as ... What? as vbOKOnly?

I would have so many other questions.
I have learned so many programming languages, only one in a official
course (Pascal where I can see similarities, but it's so long ago).
Many others (I started with the "Basic" of Commodore Vic 20, the
Commodore 64!) simply from the users' manual, or from books, (HTML,
Javascript - this was before Internet was so well documented and there
was no Google yet [:(] . )

I wrote also wrote many PERL scripts by just copying and adapting from
other scripts.
But with VB, it's not as easy.
I guess now I should really buy a book!

Just another little question:

Can I call a sub from a function?
Assuming I send a MsgBox of the vbYesNo style?
I know I can do it when the MsgBox is in a sub, but this brings me back
to my earlier question:
Can I call a sub from a cell?
Until now, all my sub are running when the user  clicks on a button to
which I have assigned a macro name.
Is there another way to start a macro in the case the user enters
something in a cell.
For example, I would like to be able to write:
If (B4 = somevalue, macroname)

Sorry for that long reply full of questions.

I thank you again, and I'm sorry to have used your knowledge for
something so stupid I had done!

Answer #3    Answered By: Jaspreet Kapoor     Answered On: Sep 02

> =AlerteDate(C37,Today)

is an unusual way of doing a validation check. (It's not wrong, it's just

Using it this way, it needs to be a function. You can't call a sub directly
from a cell.

A minor problem  is that you are completely dependent on Excel for when it
will recalculate the cell, and therefore call the function. As long as
recalculate is set to automatic, it will certainly recalculate the cell
every time  you change it. However, it is quite possible that it'll call it
at other times - e.g. when you open the file. This might or might not be a

I don't know off-hand what type "style" will be. If you look at the help
for MsgBox, it should tell you what the types of the various parameters are.

Answer #4    Answered By: Elaine Stevens     Answered On: Sep 02

You wrote:
"=AlerteDate(C37,Today) is an unusual way of doing a validation check
...snip... Using it this way, it needs to be a function."
But it IS a function.
But I understand what you mean as a minor problem  it could be:
After your last message, as a matter of fact, I had a problem: the cell
that contains "Today" was used somewhere else where there was no alert
necessary if some cells had a value later then today: when I opened the
file, I started getting alerts, so I had to create another cell
containing "=today()) reserved for that function.

So now, it is not a problem, because it the user  does not change the
wrong date  in cell A37, a larger sub will be interrupted, and the user
needs to have the larger sub do its job!
And the last step of that larger sub is to clear the content of the cell

I know I could do the same inside the larger job, but apart from the
date, the user must enter other values in others cells, and I waited for
the sub to find the date error, I feel it would be a loss of time  for
the user as when he/she is alerted right away.
I have done it somewhere else where the error can not be detected before
the sub had run: it is a sub that updates a webquery and compares new
data with data in the spreadsheet. So as soon as the sub detects the
error, it stops.

Back to your quote: "is an unusual way of doing a validation check".
What else could it be?

I have looked at so many tutorial and examples, and I could not clearly
find how to pass values from the spreadsheet to a sub: all examples that
I found looked like: select a cell, or an array, it give values that are
set in the sub.

A simple example taken from the VB HELP:
The following example shows how you might define a procedure with a
parameter array.

Sub AnyNumberArgs(strName As String, ParamArray intScores() As Variant)
Dim intI As Integer

Debug.Print strName; " Scores"
' Use UBound function  to determine upper limit of array.
For intI = 0 To UBound(intScores())
Debug.Print " "; intScores(intI)
Next intI
End Sub

The following examples show how you can call this procedure.

AnyNumberArgs "Jamie", 10, 26, 32, 15, 22, 24, 16

AnyNumberArgs "Kelly", "High", "Low", "Average", "High"

Others get the value using a prompt. I do not want that!

So, what if the sub needs values from the spreadsheet?

I have started to find a way by using


then moving to another cell with:

ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate

and so on. But I find it not very elegant...

Maybe you think this would be too complicated to answer here, but you
might give me a reference where I could study and find how to do it.

Answer #5    Answered By: Alexis Castillo     Answered On: Sep 02

Sorry, I expressed myself badly. Yes it is a function, and I'm confirming
that you wouldn't be able to use a sub in this way.

> Back to your quote: "is an unusual way of doing a validation check".
> What else could it be?

Normally, if you're wanting to do a validation of a cell from another cell,
you'd use a simple formula (or function) that returns a warning message.
This then shows in the cell, which is suitably positioned and coloured so as
to draw attention to the error.

Another common way of flagging an error is to use conditional formatting on
the input cell itself.

Users don't like MsgBoxes very much. They are intrusive in those cases when
the user  has already realised (or will realise when he/she looks at the
cell) that the value is wrong, and is going to go back and fix it anyway.
Having to cancel an error pop-up is annoying, when you know full well that
there is a problem  anyway. Having an adjacent cell show a warning message
is much more "friendly", as is having the input cell flag itself with a red

Didn't find what you were looking for? Find more on Message Box opens twice Or get search suggestion and latest updates.