PDA

View Full Version : Msg Box pops up many times



Gentile
08-01-2011, 06:39 AM
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

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

I use Excel 2003 and 2007.

Thanks for an answer.

Gentile

Kenneth Hobs
08-01-2011, 06:43 AM
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
08-01-2011, 08:49 AM
I agree with Ken, but a 'sort of' OK might be to return the error message


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


Paul