"Excel VBA - msg box problem"

K

kankely

Hello,
Excel gurus. I'm a beginner in VBA. I need the vba code that wil
divide cell M13 by M15 both in same shhet, if the answer is less tha
1.7 will activate a msg box informing the user that system is su
optimal
 
F

Frank Kabel

Hi
try something like the following
sub foo()
dim ret_value
with activesheet
ret_value=.range("M13")/.range("M15")
end with
if ret_value<1.7 then
msgbox "system is suboptimal"
end if
end sub
 
C

Chip Pearson

Try something like


Sub AAA()
If Range("M15") <> 0 Then
If Range("M13") / Range("M15") < 1.7 Then
MsgBox "sub-optimal"
End If
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
K

kankely

Is it possible to have this msg box to auto pop up once a valid entr
meeting the conditions is made, instead of having to use a botton t
activate it as i'm doing now ? Very grateful for your time.
RGDS
 
C

Chip Pearson

Put the following code in the sheet module of the worksheet
containing the cells you want to test:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$M$13" Or Target.Address = "$M$15" Then
If Range("M15") <> 0 Then
If Range("M13") / Range("M15") < 1.7 Then
MsgBox "Sub-optimal"
End If
End If
End If
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
K

kankely

Pasted the codes on a module.Tried it but the msg box didn't come on.
Regards.

Chip said:
*Put the following code in the sheet module of the worksheet
containing the cells you want to test:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$M$13" Or Target.Address = "$M$15" Then
If Range("M15") <> 0 Then
If Range("M13") / Range("M15") < 1.7 Then
MsgBox "Sub-optimal"
End If
End If
End If
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



kankely > said:
Is it possible to have this msg box to auto pop up once a valid entry
meeting the conditions is made, instead of having to use a botton to
activate it as i'm doing now ? Very grateful for your time.
RGDS




Click to expand...
Click to expand...
 
C

Chip Pearson

Did you paste the code in the worksheet's code module? NOT a
regular code module.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


kankely > said:
Pasted the codes on a module.Tried it but the msg box didn't come on.
Regards.

Chip said:
*Put the following code in the sheet module of the worksheet
containing the cells you want to test:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$M$13" Or Target.Address = "$M$15" Then
If Range("M15") <> 0 Then
If Range("M13") / Range("M15") < 1.7 Then
MsgBox "Sub-optimal"
End If
End If
End If
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
Click to expand...
Click to expand...
 
K

kankely

Sorry gave it a second try and there it is running perfectly.
Cheers and warm regards.



Chip said:
*Did you paste the code in the worksheet's code module? NOT a
regular code module.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
Click to expand...
 
S

Stephen Bullen

Hi Riku,
Saying god and hell isnt offensive in a place called england.
Click to expand...

No, but if those who volunteer here only want to answer questions
phrased politely, you're unlikely to get a helpful answer, either.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk
 
D

Dave Peterson

And you're not just in England anymore!

It may be as simple as a typo in your formula:

=avarage(a1:a10)

Where do you get it and what's your formula that's causing the error?
 
R

RagDyeR

He didn't say smack, he said spank.

You'd know the difference if I smacked you!
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------




I have re typed it many times. By the way smacking is illegal.
 
Top