Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

VBA to Return Error for a Negative Value

0

I have a simple excel with multiple worksheets that calculates a running balance on each of the worksheet  using the formula =Max(H7+E8-F8,0). This formula evaluates to zero for every negative result. This is OK but still far from what I need to achieve. 

Is there a VBA or any formula that can help me achieve the same result but this time, compel the user to enter only integers that will result in only positive running balance. If the result (running balance) evaluates to a NEGATIVE value, an ERROR message is thrown back and the operation is reversed. This should be persistent across all the worksheets. 

Thank you. 

excel
MS Office Version: Excel 2007 and 2013
posted by: Antong (rep: 2) on Fri Feb 23, 2018 at 7:19 am
Answer
Discuss

Answers

1

You don't need VBA for this, you can use data validation.

Select the 3 cells where the user can input the numbers, I'm assuming H7, E8, and F8 are those cells, and then go to Data Validation (Alt + D + L) > on the Setting tab, select Custom from the Allow drop down menu > Paste the below formula into the text input that appears > uncheck Ignore Blank and hit OK.

=($H$7+$E$8-$F$8)>0

If you want to tell the user why there was an error, go to the Error Alert tab in the data validation window and enter a custom message.

This should solve the problem without having to worry about any coding.

posted by: don (rep: 1989) on Fri Feb 23, 2018 at 9:34 am
Discuss

Discussion

Thank you for your kind response. I still get negative running balance when I applied this formula:
=($H$7+$E$8-$F$8)>0

However, what I intend to achieve is that once the value of DEBIT entered by the user is greater than the running balance instead of displaying NEGATIVE running balance, I would like for an ERROR to be displayed and if possible, UNDO the operation. Put differently, only POSITIVE running balance is allowed. NEGATIVE balance should be disallowed.
Any help with that?  
Thank you... 
Antong (rep: 2) Feb 25, '18 at 5:54 am
Edit your question and upload a sample file so I can see what you're doing. Then, I'll apply my solution above, if still aplicable, to the workbook.
don (rep: 1989) Feb 25, '18 at 11:45 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login