Hi all;


I'm writing a code for a macro in ms excel 2000 and I'm having some
troubles with MsgBox. Here is an example of the code:


Public Function RCL(PLI As Single, RMA As Single)


If RMA 49 And RMA < 71 Then
RCL = 0.91568 * (1 - Exp(-Exp(-1.8956) * (PLI - 1)))


Else
YesNo = MsgBox("RMA out of range!", vbYesNo)
Select Case YesNo
Case vbYes
RCL = 0.75624 * (1 - Exp(-Exp(-1.070025) * (PLI - 1)))
End


Case vbNo
MsgBox "Error"
End


End Select
End If
End Function


.............
After pressing YES/NO I want to MsgBox doesn't show up more than one
time. In the current situation, when I enter a value out of range, the
message appear several times and I have to press OK until I get some
result


Any hint?

Thanks

The code below seems fine. It ran okay on my machine. Look at the code that
calls the function; there may be something there that's recalling the
function.

" wrote:

Hi all;


I'm writing a code for a macro in ms excel 2000 and I'm having some
troubles with MsgBox. Here is an example of the code:


Public Function RCL(PLI As Single, RMA As Single)


If RMA 49 And RMA < 71 Then
RCL = 0.91568 * (1 - Exp(-Exp(-1.8956) * (PLI - 1)))


Else
YesNo = MsgBox("RMA out of range!", vbYesNo)
Select Case YesNo
Case vbYes
RCL = 0.75624 * (1 - Exp(-Exp(-1.070025) * (PLI - 1)))
End


Case vbNo
MsgBox "Error"
End


End Select
End If
End Function


.............
After pressing YES/NO I want to MsgBox doesn't show up more than one
time. In the current situation, when I enter a value out of range, the
message appear several times and I have to press OK until I get some
result


Any hint?

Thanks

Functions may not be the best place for a msgbox. I'm not even sure if they
will really work there.
Another factor is that a function won't recalculate until one of its input
values changes unless you put Volatile into the code (not sure how that is
supposed to go in), but go in it must to make it fire every time the
worksheet re-calculates.

What you might consider is a worksheet change event to fire only when one of
the cells feeding RMA is changed. Put in the condition and call the msgbox
from there.

--
steveB

Remove "AYN" from email to respond
wrote in message
oups.com...
Hi all;


I'm writing a code for a macro in ms excel 2000 and I'm having some
troubles with MsgBox. Here is an example of the code:


Public Function RCL(PLI As Single, RMA As Single)


If RMA 49 And RMA < 71 Then
RCL = 0.91568 * (1 - Exp(-Exp(-1.8956) * (PLI - 1)))


Else
YesNo = MsgBox("RMA out of range!", vbYesNo)
Select Case YesNo
Case vbYes
RCL = 0.75624 * (1 - Exp(-Exp(-1.070025) * (PLI - 1)))
End


Case vbNo
MsgBox "Error"
End


End Select
End If
End Function


............
After pressing YES/NO I want to MsgBox doesn't show up more than one
time. In the current situation, when I enter a value out of range, the
message appear several times and I have to press OK until I get some
result


Any hint?

Thanks

You could write this recursively but what will be the value that will change
each iteration, and how will we know when to stop?

--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
oups.com...
Hi all;


I'm writing a code for a macro in ms excel 2000 and I'm having some
troubles with MsgBox. Here is an example of the code:


Public Function RCL(PLI As Single, RMA As Single)


If RMA 49 And RMA < 71 Then
RCL = 0.91568 * (1 - Exp(-Exp(-1.8956) * (PLI - 1)))


Else
YesNo = MsgBox("RMA out of range!", vbYesNo)
Select Case YesNo
Case vbYes
RCL = 0.75624 * (1 - Exp(-Exp(-1.070025) * (PLI - 1)))
End


Case vbNo
MsgBox "Error"
End


End Select
End If
End Function


............
After pressing YES/NO I want to MsgBox doesn't show up more than one
time. In the current situation, when I enter a value out of range, the
message appear several times and I have to press OK until I get some
result


Any hint?

Thanks