Msg Box pops up many times

[Gentile]

Hi!

I have a simple UFD in VBA for Excel. This user defined function has 6 parameters. If the value of parameter b is greater than parameter c a msg-box opens.

At that special case when all parameters are in and I want to change an input I get more than one times the same msg box. I get this boxes before I confirm the entries with return and also when I reopen the function button.

Also, when I click the scroll bar on the right side of the function window (parameter functions), the program gets crazy and the msg box opens more then 50 times.

Can I avoid the plural opening of the same msg-box?


This is the VBA Code

[vba]Function Testpopup(a As String, b As Double, c As Double, d As Double, _
e As Double, f As Double) As Variant
If b > c Then MsgBox ("b has a value greater than c")
Testpopup = (b + c + d + e + f) & a
End Function
[/vba]
I use Excel 2003 and 2007.

Thanks for an answer.

Gentile

[Kenneth Hobs]

Do not use a MsgBox in a UDF. Everytime calculation occurs, it will execute. Use Debug.Print if you really need to see what happened.

[Paul_Hossler]

I agree with Ken, but a 'sort of' OK might be to return the error message

[VBA]
Function Testpopup(a As String, b As Double, c As Double, d As Double, _
e As Double, f As Double) As Variant

If b > c Then
TestPopup = "b has a value greater than c"

else
Testpopup = (b + c + d + e + f) & a
endif

End Function
[/VBA]

Paul