Looking for way to ensure that cell B15 is always negative on all sheets.

mikenelena

Board Regular
Joined
Mar 5, 2018
Messages
139
Office Version
  1. 365
Platform
  1. Windows
I have a payroll spreadsheet that features a cell for deductions. I want to make sure that regardless of whether we enter a positive or a negative number in that cell, the number always reverts to negative. I found a code on extendoffice.com that works, but I have to add it to every sheet. I have about 30. The larger issue though, is that when we add a new employee, we'd have to remember to add the code to their their sheet also. I'd like to find a solution that works once and forever. I appreciate any ideas!

Here are 2 sections of code. The first was my attempt to solve the problem at the user form level. If I step through this code line by line with F8, it works. When the code runs inside the user form however, it skips the If Statement (red text), and leaves the number positive. I don't know why.

The 2nd code section is the one from extendoffice.com. It works, but is not especially practical for our circumstances. Thanks again to anyone willing to help out!

Code:
Private Sub Confirm_Btn_Click()


Dim Appraiser As String
Dim i As Long
Dim r As Long
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim mySheet As String


With Me.EmployeeList_Lstbox
    For i = 0 To .ListCount - 1
        If .Selected(i) Then         '<-----'This with block gets the appraiser selected in the listbox to try to match against the list on the "Generals" worksheet.
            Appraiser = .List(i)
            Exit For
        End If
    Next i
End With


If Appraiser = "" Then
    MsgBox "Please select an appraiser from the ListBox.", vbExclamation, "Appraiser Not Selected!"
    Exit Sub
End If


Set ws = Worksheets("Generals")


r = Application.Match(Appraiser, ws.Columns(1), 0) 'Matching the selected appraiser to one in the worksheet column.
mySheet = ws.Range("H" & r).Value                   ' This sets the appraiser's worksheet name in column H to a variable.  r is the row number.


Set ws2 = Sheets(mySheet)                       'Changing the string variable into a worksheet variable.


With ws2
.Range("B15").Value = Me.DeductionAmount_Txtbox.Text     'Setting the amount of the deduction into the appropriate cell.
.Range("D15").Value = Me.Description_txtbox.Text
End With


MsgBox "Deduction sucessfully applied."


[B][COLOR=#FF0000]If Range("B15").Value > 0 Then[/COLOR][/B]
[B][COLOR=#FF0000]
[/COLOR][/B]
[B][COLOR=#FF0000]    Range("B15").Value = Range("B15").Value * -1[/COLOR][/B]
[B][COLOR=#FF0000]End If[/COLOR][/B]


AddDeduction_Frm.Hide
CurrentPayroll_Frm.Show


End Sub


2nd Code...

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'This code ensures that deductions will be entered as negative numbers.
    Const sRg As String = "B15"
    Dim xRg As Range
    On Error GoTo err_exit:
    Application.EnableEvents = False
    If Not Intersect(Target, Range(sRg)) Is Nothing Then
        For Each xRg In Target
            If Left(xRg.Value, 1) <> "-" Then
                xRg.Value = xRg.Value * -1
            End If
        Next xRg
    End If
err_exit:
    Application.EnableEvents = True
End Sub
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Based on what you've told us, I would think some Workbook_SheetChange event code is what you need. It's automatic and comprehends adding and/or removing employee sheets. Basically does for every sheet, what your 2nd code does for a specific sheet. Would you still consider that impractical? If so, why?
 
Upvote 0
Joe,

Thanks for the suggestion. No, that sounds like something that could work. Although what I meant by "all" sheets, was all 'employee' sheets. There are a few sheets to which this should not apply. Is that possible?

Thanks!
 
Upvote 0
If you are only interested in cell B15, try
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'This code ensures that deductions will be entered as negative numbers.
   If Sh.Name = "[COLOR=#ff0000]master[/COLOR]" Or Sh.Name = "[COLOR=#ff0000]data[/COLOR]" Then Exit Sub
   On Error GoTo err_exit:
   Application.EnableEvents = False
   If Target.Address(0, 0) = "B15" Then
      If Left(Target.Value, 1) <> "-" Then
          Target.Value = Target.Value * -1
      End If
    End If
err_exit:
    Application.EnableEvents = True
End Sub
Change sheet names in red to suit & add mores as needed. These are the sheets the code should NOT run on.
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top