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



On Wed, 16 Jul 2008 13:46:03 -0700, Courtney
wrote:

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" wrote:

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

On Wed, 16 Jul 2008 09:43:01 -0700, Courtney
wrote:

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....

"Peo Sjoblom" wrote:

If you use datavalidationallowcustom 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" wrote in message
...
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!