Avoid unwanted messages

[Mandy]

Hi to all:hi:

i have following problem

suppose i have write three different functions(area,factorial,binomila coeffiieient) in a class module.
each function contain 1 msgbox.
what happen when i execute any funcation all the messages from different function execute automatically even though that function is not called.:dunno

can any tell me how to avoid this problem, so that only that msgbox execute that is specified within the called function.

Thanks:friends:

[unmarkedhelicopter]

Either name your messages specifically to the called function or use more "fully" addressed calls e.g. call module.mymessage

[Mandy]

sorry one mistake
my function are defined in a simple module not class module.
these funcation are given below
what is happening, suppose i call last fuction bio_co from the excel before this execute two msgbox that are defined within first two fuction execute first.
so how can i avoid this problem?
plz help.


Function Area(Length As Double, Optional width As Variant)
If IsMissing(width) Then
Area = Length * Length
MsgBox "Area is Calculated in CellA1"
Else
Area = Length * width
End If
End Function

Function factorial(a As Integer)
Dim i As Integer, j As Integer
factorial = 1
For i = 1 To a
factorial = factorial * i
Next i
MsgBox "Factorial of " & a & "is " & factorial
End Function

Function Bio_co(a As Integer, b As Integer)
c = 1
d = a
e = b
For i = 1 To e
c = c * d
d = d - 1
Next i
e = b
m = 1
For i = 1 To e
m = m * b
b = b - 1
Next i
Bio_co = c / m
MsgBox "value of Bio_co is " & Bio_co
End Function



Thanks

[lucas]

could you show how you are calling it?

[Mandy]

i can call these fuction from any cell.
suppose a1 is active cell
i write as follows to call these
To call area function

=area(a3,a4) press enter

where a3 contains a number that represent Length, and a4 represent Width

To call Factorial fuction

=Factorial(any number or any cell address) press enter

To cal Bio_co

=Bio_co(two number or two cell addresses)



all functions are called as normal fuction as we call a excel function.

[lucas]

Well..I tried that to begin with and I get no message at all.....using your code...

could you post your workbook?

[Norie]

You shouldn't really be using message boxes with user defined functions.

They are intended to return values to cells.

[rbrhodes]

Hi Mandy,

You will need to put this in the Sheet Module. Right click the tab of the sheet, click View Code and paste this in:



Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
not1 = False
not2 = False
not3 = False
End Sub


This will reset the public variables on every change in the worksheet.

This code will go into a standard module, replacing your original code:



Option Explicit

Public not1 As Boolean
Public not2 As Boolean
Public not3 As Boolean

Function Area(Length As Double, Optional width As Variant)
'disable msgboxes
not2 = True
not3 = True
If IsMissing(width) Then
Area = Length * Length
Else
Area = Length * width
End If
If not1 = False Then
'do msgbox
MsgBox "Area is Calculated in CellA1"
Else
'reset for next
not1 = False
End If
End Function


Function factorial(a As Integer)
Dim i As Integer
Dim j As Integer
'disable msgboxes
not1 = True
not3 = True
factorial = 1
For i = 1 To a
factorial = factorial * i
Next i
If not2 = False Then
MsgBox "Factorial of " & a & " is " & factorial
Else
'reset for next
not2 = False
End If
End Function

Function Bio_co(a As Integer, b As Integer)
Dim c As Integer
Dim d As Integer
Dim e As Integer
Dim i As Integer
Dim m As Integer
'disable msgboxes
not1 = True
not2 = True
c = 1
d = a
e = b
For i = 1 To e
c = c * d
d = d - 1
Next i
e = b
m = 1
For i = 1 To e
m = m * b
b = b - 1
Next i
Bio_co = c / m
If not3 = False Then
MsgBox "Value of Bio_co is " & Bio_co
Else
'reset for next
not3 = False
End If
End Function


Note: If all three are on the sheet and all three point to the same range then the message box that will pop up when the cells are changed is the one for the last function entered on the sheet. There is no way to predict or control that except for entering the Functions on the sheet in the preferred order. In other words if you put:

= area(c1,d1)
= factorial(c1)
= bio_co(c1,d1)

The message that will pop up when you change C1 or D1 will be the bio_co message as it was entered on the sheet last.

If you put:

= bio_co(c1,d1)
= factorial(c1)
= area(c1,d1)

Then the msgbox for area will pop up by default when c1 or d1 changes.

HTH,

dr

[Mandy]

Hi to all
thanks to all of you for your suggestions
my problem is solved now due to code given by rbrhodes. thanks Rbrhodes.

once again Thanks all of you
Have a nice day.