msgbox
Posted by CAM on January 26, 2002 12:41 PM
I am having trouble getting a message to appear when the cells contents exceeds a certain number. I've tried writing a formula into the conditional formating but it didn't work, I've written a formula into the sub worksheet_change event but that didn't work either. I need for a message box to appear when cell "k28" exceeds $6.75. Any help?
Also, I have these cells, "d5:d9" that will contain salary figures, then I have cells "d11:d26" that will contain other salary figures. at the bottom in cell "d30" it calculates all the above figures. Is there any way that I can write a formula not in cell "d30" but for the worksheet itself that cell "d30" will not calculate the figures unless at least one figure has been entered into cells "d11:d26"? I will need for the cell to calculate the figures for cells "d11:d26" even if there are no figures in cells "d5:d9". I don't want someone entering a 1 in the cells "d5:d9" and cell "d30" calculates that 1 unless they also enter a 1 into cells "d11:d26".
I hope you can understand what I wrote.
thanks,
Posted by Ivan F Moala on January 26, 2002 1:00 PM
1st Q -
have you tried something along these lines
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$K$28" Then
If Target > 6.75 Then
MsgBox "Value in " & Target.Address & " is > $6.75"
End If
End If
End Sub
BUT if K28 is cahnge via formula then you need
to use the worksheet caculate event eg
Private Sub Worksheet_Calculate()
If [K28] > 6.75 Then
MsgBox "Value in K28:= " & Format([K28], "$#.00") & " is > $6.75"
End If
End Sub
2nd Q-
Try using a formula eg.
=IF(OR(COUNTA(D5:D9)=0,COUNTA(D11:D26)=0),"Can't calculate! Data not complete",your formula here)
HTH
Ivan
Posted by CAM on January 26, 2002 1:33 PM
Thanks very much.
Posted by CAM on January 26, 2002 9:45 PM
Re: worked great, but?
Ivan,
the formula for question 1 worked great except for when I cleared the cells that k28 figured. I changed the formula to:
if [k28] is > 6.75 then
MsgBox "AHW is to high"
else
end if
I need for the formula to do nothing except for when/if k28 is greater than 6.75.
Posted by Ivan F Moala on January 26, 2002 10:28 PM
Re: worked great, but?
Cam
What is happening when you clear the cells
I'm assuming you are using the calculate event
ONLY....
Ivan
: Thanks very much. :
Posted by CAM on January 26, 2002 10:50 PM
Re: worked great, but?
Ivan,
The cells that k28 figures are blank until someone enters the figures. At the start of each new week the cells need to be cleared for the new weeks numbers. While testing the formula I entered the numbers into the cells and k28 caculated them correctly and brought up the message, but when I cleared the cells of their content(like I was preparing for a new week) I got an error message(debug-if [k28] > 6.75 then, was highlighted). The formula works great when there are figures entered but if left blank I get the error after clearing them.
Cam What is happening when you clear the cells I'm assuming you are using the calculate event ONLY.... Ivan : Ivan, : the formula for question 1 worked great except for when I cleared the cells that k28 figured. I changed the formula to
Posted by Ivan F Moala on January 27, 2002 12:01 AM
Re: worked great, but?
What is the error
Plus what formula have you got in K28
I suspect that when cleared it is not a numerical
result and you could be getting a type mismatch ? Ivan, The cells that k28 figures are blank until someone enters the figures. At the start of each new week the cells need to be cleared for the new weeks numbers. While testing the formula I entered the numbers into the cells and k28 caculated them correctly and brought up the message, but when I cleared the cells of their content(like I was preparing for a new week) I got an error message(debug-if [k28] > 6.75 then, was highlighted). The formula works great when there are figures entered but if left blank I get the error after clearing them. : What is happening when you clear the cells : I'm assuming you are using the calculate event : ONLY.... : Ivan
