Negative entry

[GeeTee]

Can I format cells so that any number entered into it is shown as negative
automatically ie not having to make the entry with a minus sign


Thanks

 

[Gary''s Student]

The simplest way is to use two cells:

Enter your data in A1 and in B1:

=-A1

Thus saving a keystroke.

Another way involves event macros.

 

[Bob Phillips]

(General);(General)

--
---
HTH

Bob

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

 

[Mike H]

Hi,

Not with a format because that will make the number look negative when in
fact it would not change the underlying value. This may help. Right click the
sheet tab, view code and paste this in:-

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A1:B10")) Is Nothing Then ' Change to suit
If IsNumeric(Target) Then
Application.EnableEvents = False
Target.Value = Target.Value * -1
Application.EnableEvents = True
End If
End If
End Sub

Mike

 

[Stephen]

But be aware that this changes only the way the number is shown, not the
underlying value. So if the value is then used elsewhere in a formula, the
positive value will be used, which I would find very confusing. Whilst this
is exactly what the OP asked, it may well not be what was wanted! Certainly
the OP should be made aware of this.

 

[Gary''s Student]

To you point:

Format > Cells... > Number > Custom >
"-"General" (only joking)"

 

[GeeTee]

Thank guys for the prompt responses, and yes I did want to change the value
to negatve, I had tried custom format. I continually enter amounts into a
column that are neg and wanted to save a keystroke

 

[Bob Phillips]

I tend to respond to the question asked unless it is ambiguous or clearly
mis-stated.

--
---
HTH

Bob

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

 

[Gord Dibben]

You can use event code behind the sheet.

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 the sheet tab and "View Code".

Copy/paste into that sheet module. Adjust range to suit.


Gord Dibben MS Excel MVP