I have the following VBA code and want to repeat it across multiple worksheets on a workbook.
---------------------------------------------------------------------
Option Explicit
Private Enum Nwc ' Worksheet columns
' 17 January 2018 ' assign any integer value to any enumeration.
' Enumerations without assigned value
' are incremented by 1 from the one above
NwcDate = 1 ' used to find the last used row
' DR and CR must be adjacent!
NwcDR = 5 ' 3 = column C
NwcCR = 6 ' [auto] = 7 =
NwcBal = 8 ' [auto] = 8 =
NwcFirstRow = 3 ' rows above NwcFirstRow are ignored
' change as required by your worksheet design
End Enum
Private Sub Worksheet_Change(ByVal Target As Range)
' 17 January 2018
Dim Rl As Long
Dim Rng As Range
Dim Bal As Double
Rl = Cells(Rows.Count, NwcDate).End(xlUp).Row
Set Rng = Range(Cells(NwcFirstRow, NwcDR), Cells(Rl, NwcCR))
With Application
.EnableEvents = False
If Not .Intersect(Target, Rng) Is Nothing Then
Rl = Cells(Rows.Count, NwcBal).End(xlUp).Row + 1
If Target.Row <> Rl Then
' the only allowed row is the one immediately under
' the last shown balance:
MsgBox "Past Entries Cannot be Modified. No Vex..." & vbCr & _
"" & vbCr & _
"", _
vbCritical, "KADFAMA"
.Undo
.Cells(Rl, Target.Column).Select
Else
Bal = .Sum(Rng.Columns(2)) - .Sum(Rng.Columns(1))
If Bal < 0 Then
MsgBox "This Debit Would Create a Negative " & vbCr & _
"Balance of " & Bal & " Which is Not Permitted." & vbCr & _
"...and Bash Insists the Entry MUST Be Removed.", _
vbExclamation, "KADFAMA"
.Undo
Else
Cells(Rl, NwcBal).Value = Bal
End If
End If
End If
.EnableEvents = True
End With
End Sub
---------------------------------------
How can I repeat this across multiple worksheets?
---------------------------------------------------------------------
Option Explicit
Private Enum Nwc ' Worksheet columns
' 17 January 2018 ' assign any integer value to any enumeration.
' Enumerations without assigned value
' are incremented by 1 from the one above
NwcDate = 1 ' used to find the last used row
' DR and CR must be adjacent!
NwcDR = 5 ' 3 = column C
NwcCR = 6 ' [auto] = 7 =
NwcBal = 8 ' [auto] = 8 =
NwcFirstRow = 3 ' rows above NwcFirstRow are ignored
' change as required by your worksheet design
End Enum
Private Sub Worksheet_Change(ByVal Target As Range)
' 17 January 2018
Dim Rl As Long
Dim Rng As Range
Dim Bal As Double
Rl = Cells(Rows.Count, NwcDate).End(xlUp).Row
Set Rng = Range(Cells(NwcFirstRow, NwcDR), Cells(Rl, NwcCR))
With Application
.EnableEvents = False
If Not .Intersect(Target, Rng) Is Nothing Then
Rl = Cells(Rows.Count, NwcBal).End(xlUp).Row + 1
If Target.Row <> Rl Then
' the only allowed row is the one immediately under
' the last shown balance:
MsgBox "Past Entries Cannot be Modified. No Vex..." & vbCr & _
"" & vbCr & _
"", _
vbCritical, "KADFAMA"
.Undo
.Cells(Rl, Target.Column).Select
Else
Bal = .Sum(Rng.Columns(2)) - .Sum(Rng.Columns(1))
If Bal < 0 Then
MsgBox "This Debit Would Create a Negative " & vbCr & _
"Balance of " & Bal & " Which is Not Permitted." & vbCr & _
"...and Bash Insists the Entry MUST Be Removed.", _
vbExclamation, "KADFAMA"
.Undo
Else
Cells(Rl, NwcBal).Value = Bal
End If
End If
End If
.EnableEvents = True
End With
End Sub
---------------------------------------
How can I repeat this across multiple worksheets?