Worksheet_Change procedure

[marston.gould]

Can someone explain to me why, when I type in the area "A1:F65536" this
doesn't seem to execute?

Code in ThisWorkbook

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VRange As Range, cell As Range
Dim Msg As String
Dim ValidateCode As Variant
Set VRange = Range("A1:F65536")
For Each cell In Target
If Union(cell, VRange).Address = VRange.Address Then
ValidateCode = EntryIsValid(cell)
If ValidateCode = False Then
MsgBox "Please make correct entry"
Else
MsgBox "Dept: " & ValidateCode(1) & vbCrLf & _
"Loc: " & ValidateCode(2) & vbCrLf & _
"Fn: " & ValidateCode(3) & vbCrLf & _
"Acct: " & ValidateCode(4) & vbCrLf & _
"Fleet: " & ValidateCode(5) & vbCrLf & _
"Bldg: " & ValidateCode(6)
Application.EnableEvents = False
cell.Activate
Application.EnableEvents = True
End If
End If
Next cell
End Sub

Code in module1

Private Function EntryIsValid(cell) As Variant
Option Base 1
Dim Dept, Loc, Fn, Acct, Fleet, Bldg As Variant
If Not WorksheeetFunction.IsNumber(cell) Then
EntryIsValid = False
Exit Function
End If
If CInt(cell) <> cell Then
EntryIsValid = False
Exit Function
End If
CELLCOLUMN = Left(CStr(cell.Address), 1)
Select Case CELLCOLUMN
Case "A"
Dept = Range("A1").Offset(0, 0)
Loc = Range("A1").Offset(0, 1)
Fn = Range("A1").Offset(0, 2)
Acct = Range("A1").Offset(0, 3)
Fleet = Range("A1").Offset(0, 4)
Bldg = Range("A1").Offset(0, 5)
Case "B"
Dept = Range("A1").Offset(0, -1)
Loc = Range("A1").Offset(0, 0)
Fn = Range("A1").Offset(0, 1)
Acct = Range("A1").Offset(0, 2)
Fleet = Range("A1").Offset(0, 3)
Bldg = Range("A1").Offset(0, 4)
Case "C"
Dept = Range("A1").Offset(0, -2)
Loc = Range("A1").Offset(0, -1)
Fn = Range("A1").Offset(0, 0)
Acct = Range("A1").Offset(0, 1)
Fleet = Range("A1").Offset(0, 2)
Bldg = Range("A1").Offset(0, 3)
Case "D"
Dept = Range("A1").Offset(0, -3)
Loc = Range("A1").Offset(0, -2)
Fn = Range("A1").Offset(0, -1)
Acct = Range("A1").Offset(0, 0)
Fleet = Range("A1").Offset(0, 1)
Bldg = Range("A1").Offset(0, 2)
Case "E"
Dept = Range("A1").Offset(0, -4)
Loc = Range("A1").Offset(0, -3)
Fn = Range("A1").Offset(0, -2)
Acct = Range("A1").Offset(0, -1)
Fleet = Range("A1").Offset(0, 0)
Bldg = Range("A1").Offset(0, 1)
Case "F"
Dept = Range("A1").Offset(0, -5)
Loc = Range("A1").Offset(0, -4)
Fn = Range("A1").Offset(0, -3)
Acct = Range("A1").Offset(0, -2)
Fleet = Range("A1").Offset(0, -1)
Bldg = Range("A1").Offset(0, 0)
Case Else
End Select
EntryIsValid(1) = Dept
EntryIsValid(2) = Loc
EntryIsValid(3) = Fn
EntryIsValid(4) = Acct
EntryIsValid(5) = Fleet
EntryIsValid(6) = Bldg
End Function

 

[Dave Peterson]

Try moving your worksheet_change event into the sheet module that should have
that behavior.

There is a workbook version of the worksheet_change event--it's called by:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)