| View previous topic :: View next topic |
| Author |
Message |
steeeeevo
General User
Joined: 29 Aug 2009
Posts: 5
|
 Posted: Sat Aug 29, 2009 10:08 pm Post subject: Need help with simple macro in Calc |
 |
|
I have a formula for sales tax. If the result is less than zero, I want a message to pop up and notify me that we have an invalid sale.
The validate thing only works for data I enter, not date entered by a formula. Can anyone tell me how to make my pop up dreams come true?
I know the asnwer has to be with a macro somehow, but I really don't know my stuff and could use your help. THANKS! |
|
| Back to top |
|
 |
JohnV
Administrator
Joined: 07 Mar 2003
Posts: 8979
Location: Lexinton, Kentucky, USA
|
| Posted: Sun Aug 30, 2009 8:57 am Post subject: |
|
|
How about a formula in the sales tax cell(s) for less than or equal zero.
=IF(A1*0.06<=0;"INVALID";A1*0.06) |
|
| Back to top |
|
|
steeeeevo
General User
Joined: 29 Aug 2009
Posts: 5
|
| Posted: Sun Aug 30, 2009 3:00 pm Post subject: |
|
|
| That's essentially what I have set up now, but I'd love for it to actually pop up a message on the screen that requires user input so that it forces the user's attention to be drawn to the issue. Thanks for any additional help. |
|
| Back to top |
|
|
keme
Moderator
Joined: 30 Aug 2004
Posts: 2732
Location: Egersund, Norway
|
| Posted: Thu Sep 03, 2009 12:04 am Post subject: |
|
|
AFAIK it's possible to enable a "cell listener", but I don't know how to do that. A workaround when it's a formula is to write a function that returns a "safe" result (zero), then insert that in your formula.
Enter the following function in an OOo basic module:
| Code: | function Notify(Notification as string)
' Give the notification
MsgBox(Notification,16,"Bad value")
' return a "safe" value from this function
Notify=0
End function |
Append this to your formula: +IF(CURRENT()<0;Notify("The result is invalid."))
This will give a notification at each recalculation, so in some cases you may want to turn automatic recalculation off... |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Thu Sep 03, 2009 5:51 am Post subject: |
|
|
Data>Validity...
Allow decimal >= 0
Error alert: Show error message
Action: Stop (reject)
Unless you don't specify something else, the default message is "Invalid value" in the respective GUI language.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
steeeeevo
General User
Joined: 29 Aug 2009
Posts: 5
|
| Posted: Sat Sep 05, 2009 7:31 am Post subject: |
|
|
| Works great. Thanks Keme! I knew there had to be a way. Thanks for your help! |
|
| Back to top |
|
|
|
