Forced-In negative value

[joelle]

Hello Experts,

Please I need your expert help with the following:

Cell A1 is an input field for credit, meaning it has to be a negative value
all the times for the total to calculate correctly. How do I do so that even if
people enter a positive or a negative value, it will still turn to a negative value
and a negative number display. I run into a circular problem here because I
cannot put formula into A1 since I make it an input field.

Your prompt help is very much appreciated.
Regards,
Nee

[johnske]

Something like this maybe? (code goes into the relevant sheets code module)


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If IsNumeric([a1]) Then [a1] = -Abs([a1])
End Sub

HTH,

John

[joelle]

Hello John,

Wow that's very quick - thanks so much. But is there a way to avoid using
vba? Or if I need to use the code you provided, how do I change them to
work with a range of A1:A2 and not just A1.

Many thanks,
Nee

[Killian]

Well if you can't use a formula, then in the VBE, select the code pane for the worksheet concerned and add some code to do it in the WorkSheet_Change event


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" And Target.Value > 0 Then
Target.Value = 0 - Target.Value
End If
End Sub

Of course if someone enters a non-numeric value, 0 - Target.Value will cause an error, so you need to validate the input

[Killian]

Hello John,

Wow that's very quick - thanks so much. But is there a way to avoid using
vba? Or if I need to use the code you provided, how do I change them to
work with a range of A1:A2 and not just A1.

Many thanks,
Nee
Too quick, and a better answer!!!

But since we mentioned validation, you can go to Data>Validation, select Custom and enter =A1<1 for the formula, and add a custom message for the user

[johnske]

Hi Nee,

To add another range for it to work with, do it this way


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If IsNumeric([a1]) Then [a1] = -Abs([a1])
If IsNumeric([a2]) Then [a2] = -Abs([a2])
Application.EnableEvents = True
End Sub

From what you said before, I can't see that you can do it as a worksheet solution, but if you did you could use something like =-ABS(G11) ... assuming here that G11 is where you're getting your value for A1 from.

@ Killian :devil: we musta posted at the same time :rofl:

[Jacob Hilderbrand]

To make it display negative no matter what use this.


Format | Cells | Number | Custom

-#,##0_);-#,##0

This will make the number "look" negative even if it is positive, but you will need to adjust your formulas using this cell as well.

Option 1:

Have a hidden column with the formula:


=-Abs(A1)

Then refer to the cells in that column instead of column A.

Option 2:

In all formulas that refer to A1 (for example), change them from A1 to -Abs(A1).

[Killian]

@ Killian :devil: we musta posted at the same time :rofl:
and now you've posted while I was editing my response :rotlaugh:

That's it! I'm going to bed... :sleep:

[joelle]

Hello Gentlemen!

No fighting there -- you all deserve a big hug - wow, thats too much
to sacrify, so how about a pat on the shoulder! Seriously, you all have done great!

My thanks to you too, Killian and Jake.
John, your codes work great - and so do Killian's.
Jake - I like your alternative too because my coworkers are afraid of seeing vba (am I too?)

Good nite everyone,

Nee

[MWE]

Too quick, and a better answer!!!

But since we mentioned validation, you can go to Data>Validation, select Custom and enter =A1<1 for the formula, and add a custom message for the user

be very careful about using Validation. Validation is a "format" and is easily wiped out if a copy/paste is executed into the cell with Validation.