Force a negative value

  • Thread starter Thread starter Ralph
  • Start date Start date
R

Ralph

Is it possible to force a number in a cell to always be a
negative value (whether you key it in as positive or
negative)? Thanks in (e-mail address removed)
 
Hi Ralph
for this you'll need VBA. Put the following code in your worksheet
module (see http://www.cpearson.com/excel/events.htm for more details):
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value <> "" Then
if .value <0 then .value = - .value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub
 
Ralph said:
Is it possible to force a number in a cell to always be a
negative value (whether you key it in as positive or
negative)?...
Click to expand...

You could always refer to the cell as -ABS(CellRef).
 
Many thanks Frank...:-)
-----Original Message-----
Hi Ralph
for this you'll need VBA. Put the following code in your worksheet
module (see http://www.cpearson.com/excel/events.htm for more details):
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value <> "" Then
if .value <0 then .value = - .value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany



.
Click to expand...
 
Frank Kabel said:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value <> "" Then
if .value <0 then .value = - .value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub
Click to expand...
....

First things first. While the colon ending the 'On Error' line is innocuous,
it's bad form.

The error trap is nice, but you're missing the critical

Application.EnableEvents = False

call. Also, the OP wants to force *negative* values. Your If block changes
the sign of negative values, thus forcing positive values. Finally, why bail
when Target.Cells.Count > 1? All that means is that the user entered more
than one cell at once, either entering a multicell array formula or entering
into several cells with [Ctrl]+[Enter]. If the former, probably unwise to
try changing the cell. If the latter, there's no reason not to change the
cell.

Perhaps you meant something like


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub

With Me.Range("A1")

If .HasArray Then If .CurrentArray.Cells.Count > 1 Then _
Exit Sub

Application.EnableEvents = False

If VarType(.Value) = vbDouble And CDbl(.Value) > 0 Then _
.Value = -.Value

Application.EnableEvents = True

End With

End Sub
 
Hi Harlan
you're of course right (a little bit early for me this mornig, so %&/%
copy and paste errors).
BTW nice way to deal with entries in multiple cells :-)

--
Regards
Frank Kabel
Frankfurt, Germany


Harlan said:
Frank Kabel said:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value <> "" Then
if .value <0 then .value = - .value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub
Click to expand...
...

First things first. While the colon ending the 'On Error' line is
innocuous, it's bad form.

The error trap is nice, but you're missing the critical

Application.EnableEvents = False

call. Also, the OP wants to force *negative* values. Your If block
changes the sign of negative values, thus forcing positive values.
Finally, why bail when Target.Cells.Count > 1? All that means is that
the user entered more than one cell at once, either entering a
multicell array formula or entering into several cells with
[Ctrl]+[Enter]. If the former, probably unwise to try changing the
cell. If the latter, there's no reason not to change the cell.

Perhaps you meant something like


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub

With Me.Range("A1")

If .HasArray Then If .CurrentArray.Cells.Count > 1 Then _
Exit Sub

Application.EnableEvents = False

If VarType(.Value) = vbDouble And CDbl(.Value) > 0 Then _
.Value = -.Value

Application.EnableEvents = True

End With

End Sub
Click to expand...
 
Back
Top