Data Validation - allowing only negative numbers or converting

[Courtney]

I'm building a model where end-users must input expenses and revenues. In
the expense section I'd like to ensure that numbers are entered as negatives,
or if they are entered as positives build a formula in the data validation
section that would automatically convert them to negative numbers. Any idea
on how to do this?

Any insight would be appreciated.

Thanks!

 

[Bob Phillips]

Just use the decimal or whole number options with a very small negative
number and the biggest negative number you will allow.

Or custom with =A1<0

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[John C]

In the cells they will be entering this negative number:
Data-->Validation
Allow: Decimal
Data: Less than or equal to
Maximum: 0

NOTE: You can change DATA to Less than as needed.

 

[Peo Sjoblom]

If you use data>validation>allow>custom and

=A1<0

then they can't type in a positive number, otherwise you would need
an event macro that executes when pressing enter

Replace A1 with the cell you want to validate

--


Regards,


Peo Sjoblom

 

[Courtney]

Thanks for the reply - that works if they enter a negative number, but can
you also make it to where if they enter it as a positive, instead of getting
an error message it automatically converts it to a negative number on what
they entered. Ex. If they entered an expense as $600 it would convert to
($600).

Thanks

 

[John C]

May I suggest a different approach.
Assumptions, columns B, D, and F have revenue, columns C, E, and G have
expenses.
Select all cells (that will have a revenue or expense entered).
Data-->Validation, Decimal, Greater than or Equal to, 0
Select the expense cells, go to Menu: Format-->Cells-->Number-->Custom
I used the following:
[Red](#,##0.00)
A format for negative numbers is not needed, as we don't allow entry of
negative numbers.

Assuming Row 2 is the first set of data of expenses and revenues, my total
formula entered in cell H2 is as follows:
=SUM(B2,D2,F2)+SUM(C2,E2,G2)*-1

Then Edit-->Fill-->Down as needed.

 

[Courtney]

Thanks - I think this would be the same effect of changing all my
totals/formulas to automatically subtract expenses entered as positive
numbers - that might be the answer - was trying to look for an automatic way
to convert to a negative number to prevent some re-work, but might be
unavoidable. Appreciate the help though.

May I suggest a different approach.
Assumptions, columns B, D, and F have revenue, columns C, E, and G have
expenses.
Select all cells (that will have a revenue or expense entered).
Data-->Validation, Decimal, Greater than or Equal to, 0
Select the expense cells, go to Menu: Format-->Cells-->Number-->Custom
I used the following:
[Red](#,##0.00)
A format for negative numbers is not needed, as we don't allow entry of
negative numbers.

Assuming Row 2 is the first set of data of expenses and revenues, my total
formula entered in cell H2 is as follows:
=SUM(B2,D2,F2)+SUM(C2,E2,G2)*-1

Then Edit-->Fill-->Down as needed.
--
John C

Thanks for the reply - that works if they enter a negative number, but can
you also make it to where if they enter it as a positive, instead of getting
an error message it automatically converts it to a negative number on what
they entered. Ex. If they entered an expense as $600 it would convert to
($600).

Thanks

 

[Courtney]

How would you build the event macro? I think that is what I'm looking for -
I would like them to be able to enter either a positive or negative number
but in the end, convert all expenses to negatives to make it foolproof....

 

[Gord Dibben]

This code will change positves to negatives upon entry.

If negative is entered it remains negative.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A100"
Dim cell As Range

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each cell In Target
If Left(cell.Value, 1) <> "-" Then
cell.Value = cell.Value * -1
End If
Next cell
End If

ws_exit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste into that sheet module. Edit the range to suit.

Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP

 

[Courtney]

Thank you so much Gord - a couple of questions - sorry if they are
remedial...haven't done Macros in a while....

If I only want certain cells on certain worksheets to change to negative
numbers (or remain negative if entered that way) would I just change the
worksheet name and the range? Should I set up these prior to entering the
formula you have below?

Thanks for your help on this - just want to make sure I understand the code
below...

 

[Gord Dibben]

First of all............what I posted is event code........not a formula

Second of all............what you do depends upon a few things.

Which cells? Which worksheets.......some or all? Different cells on each
worksheet?

Assuming the certain cells on certain worksheets are different you would copy
the code to each worksheet module and edit the WS_RANGE to suit like

Const WS_RANGE As String = "A1,B2,C3,D4,E5,F6"

Or............"A1:J10", "K11:T20", "U21:AD30"

If something else, please post back with a few more details on "certain"


Gord

 

[Courtney]

Hi Gord! Thanks for the response and clarification - is an event code a
macro in VB? Sorry if that is a dumb question.....

To clarify on "certain" there will be one worksheet for inputs, and on that
worksheet only half of the cells will be expenses (so need to be negative).
They are not in a nice clean group so I think it will be more like your first
example - B61, B63, B65, etc. but what complicates it more, is that there is
a time phasing element depending on a drop down list the user selects to tell
the model how often these expenses occur - in this example an input would be
entered in B61, in C61 if the end user selects "Monthly, Quarterly, Annually"
the expense entered in B61 is spread accordingly - if the users selects
"Other" in C61, then the model prompts the user to enter the expense manually
in row C62:GA62 - I would need to set it up so that these cells would also
automatically convert to negative if data is entered in them. So most if I'm
understanding your advice correctly, I would need to set a range that looked
something like this: B61, C62:GA62, B63, C64:GA64, B65, etc. etc.

Is that correct?

thanks for your patience!

 

[Courtney]

OK I found out how to view the code associated with the sheet, so I
understand the difference now. If I named my range of cells "Expenses" for
any cell I'd want to change to a negative value, and my worksheet name was
"Input - Detailed" - would this be the appropriate code?
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("Expenses")) Is Nothing Then
For Each cell In Target
If Left(cell.Value, 1) <> "-" Then
cell.Value = cell.Value * -1
End If
Next cell
End If

ws_exit:
Application.EnableEvents = True
End Sub


Thanks for your help!

 

[Gord Dibben]

Doesn't matter what the sheet name is as long as you have right-clicked on that
worksheet tab and selected "View Code" then placed the code into that sheet
module.

Yes, your code will work as written as long as you have a defined name of
Expenses in that sheet.

You could have defined your cells in the WS_RANGE String as I pointed out.

Const WS_RANGE As String = "A1,B2,C3,D4,E5,F6"

But when I tested your code it worked so no need to change now.


Gord Dibben MS Excel MVP