Defaulting Input to Negative Values

@Hazel Kohler,


If you use Ron S's suggestion of the helper column, I would put the input cells on a separate sheet, rather that having a helper column to hide and unhide. That way you can switch to the other sheet when entering the negative data and do everything else on the main sheet, which may be a visual hint to indicate when you are entering numbers to be negative. You can even protect the range in question on the main sheet from entering data there, with a pop up message directing you to input cells. Going overboard, you can also allow entry of only positive numbers on the helper sheet, and only in specified rows (10 to 120).
This may seem as too much work,if you are the only one who uses that workbook, but may be worthwhile if there may be other users that need to enter such data.

The formula to use on main sheet would be something like for cell B10:
=-InputSheet!B10

@Hazel Kohler,
It is nice to see that the person requesting help actually reads and appreciates the answer.
And, no, I did not contribute to this tip (yet), so I am saying this without personal interest.

You will need to use a "helper column".
Column A, enter values
Column B in rows 10-120 enter formula =Abs(B2)*-1
Column B rows 120+ enter formula =b2
This page lists several other methods including a chart listing pro's and cons for each method:
https://www.upwork.com/resources/how-to-make-numbers-negative-in-excel


Still with helper column A, end value col B, single formula:
=IF(ROW()<121, A10*-1, A10)

Copy the formula down to row 120

If you want to hide the input row, AND you do data entry at different times you could use PowerQuery.
Define your input area as an Excel Table, no helper column
Use PQ to read that input table
In PQ create a step to apply the formula (from above) in helper column
Next step delete the input column
Do any other data cleanup, or even formulas you need
Output to a new table on a new tab
Now you have 2 tabs:

one for input only
the other for user viewing of the output, without formulas.

After that the process is simple, do your data entry in the entry tab
then swap to the output tab, select the output table and use the "Refresh" function to import and process new data to update the output table.
https://support.microsoft.com/en-gb/office/refresh-an-external-data-connection-in-excel-1524175f-777a-48fc-8fc7-c8514b984440

I'm a strong fan of PowerQuery when data entry is required more than once. PowerQuery can be used to hide formulas from the users.

PowerQuery output is also a great input for Pivot Tables to generate summary reports!

PowerQuery and PivotTables take some time to learn, but once you get past the fear and invest some time for DYI learning (or pay for some courses) the combination can be used to save you days of repetitive work each week/month by automating collection of data from one or more sources and creating automated reports that just need a refresh to import new data.

PivotTables will impress your boss. Especially at how quickly you can tweak them to create new reports. If your boss is not willing to pay for the courses, any time you save by learning the skill you can use to surf the web or play solitaire on your phone (not your work computer! bosses can spy on them!)

In the Tip's first Worksheet_Change event procedure, Target.Value generates an error when negated if it is text or is formatted as Date (but not Time); in this case Target.Value is not changed. If Target.Value is TRUE (-1 in VBA) or FALSE (0 in VBA) it is not changed because it is not > 0. But if Target.Value is a positive numeric value the event procedure is triggered twice because EnableEvents was not disabled; in this case, if Target contains a formula it is replaced by a negative constant. The same applies to Micky's version, since Value is the default property of Target (a Range object).
If a multi-cell range is selected (contiguous or not) and a value is manually entered, the event procedure applies only to the active cell (Target is that single cell). But if a value is pasted into a multi-cell selection, then Target is that selection and the results depend on whether or not it is contiguous; when it is not contiguous (more than one area), the results depend on its configuration.
To resolve these issues, here is my version of the Tip's first event procedure:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row < 10 Or Target.Row > 120 Then
        Exit Sub
    ElseIf Not IsNumeric(Target.Value) Then
        Exit Sub
    ElseIf Target.HasFormula Then
        Exit Sub
    ElseIf Target.Value > 0 Then
        Application.EnableEvents = False
        Target.Value = -Target.Value
        Application.EnableEvents = True
    End If
End Sub

Notice IsNumeric(Target.Value) returns False if Target.Value is text or formatted as Date (but not Time); in this case Target.Value is not changed. And if Target contains a formula it is not disturbed. But when Target is a single cell with a positive numeric value the event procedure is triggered only once because EnableEvents is disabled before Target.Value is negated.
As before, if a multi-cell range is selected (contiguous or not) and a value is manually entered, the event procedure applies only to the active cell. When pasting into a contiguous multi-cell selection, IsNumeric(Target.Value) returns False and Target.Value is not negated. But if a value is pasted into a non-contiguous selection (more than one area), the results depend on its configuration.
To resolve issues with pasting into multiple cells, here is my version of the Tip's second event procedure:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim targ As Range, cell As Range
    Set targ = Application.Intersect(Target, Me.Range("10:120"))
    If targ Is Nothing Then Exit Sub
    For Each cell In targ.Cells
        If IsNumeric(cell) And Not cell.HasFormula Then
            If cell > 0 Then
                Application.EnableEvents = False
                cell = -cell '.Value is default property
                Application.EnableEvents = True
            End If
        End If
    Next cell
End Sub

This version is recommended for Hazel. It must be located in the applicable Sheet module as noted in the Tip: "Right-click the worksheet's tab and choose View Code from the resulting Context menu."

Thank you to everyone who helped on this question. It will save me a lot of time and key strokes

To my opinion, due to limiting the bench of rows - there in no need to use any ENABLEEVENT command.
The following code should do the job.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrHandler
If Not Intersect(Target, Rows("10:120")) Is Nothing And Target > 0 Then Target = -Target
ErrHandler:
End Sub

The last macro did not work for me, I have made two modifications : changed the location of the Not on line 4 and included and End if after Next cell as indicated in the revised code below
Private Sub Worksheet_Change(ByVal Target As Range)
Dim targ As Range, cell As Range
Set targ = Application.Intersect(Target, Me.Range("10:120"))
If Not targ Is Nothing Then
Application.EnableEvents = False
On Error GoTo ErrExit
For Each cell In targ.Cells
On Error GoTo ErrExit
If IsNumeric(cell) And _
Not (cell <= 0 Or IsDate(cell) Or cell.HasFormula) _
Then cell = -cell '.Value is default property
ErrExit:
Next cell
End If
Application.EnableEvents = True
End Sub