ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Worksheet_Change Still Not Executing.... (https://www.excelbanter.com/excel-programming/308856-worksheet_change-still-not-executing.html)

[email protected]
Worksheet_Change Still Not Executing....
 

Any suggestions....?? Most appreciated.


Code in Sheet1

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.Address([False], [False]))
MsgBox "Dept: " & ValidateCode(1) & vbCrLf & _
"Loc: " & ValidateCode(2) & vbCrLf & _
"Fn: " & ValidateCode(3) & vbCrLf & _
"Acct: " & ValidateCode(4) & vbCrLf & _
"Fleet: " & ValidateCode(5) & vbCrLf & _
"Bldg: " & ValidateCode(6)
End If
Next cell
End Sub

Code in Module

Private Function EntryIsValid(celladdress) As Variant
Dim Dept As Variant
Dim Loc As Variant
Dim Fn As Variant
Dim Acct As Variant
Dim Fleet As Variant
Dim Bldg As Variant
Dim CellCase As String

CellCase = Left(CStr(celladdress), 1)
Select Case CellCase
Case "A"
Dept = Range(celladdress).Offset(0, 0)
Loc = Range(celladdress).Offset(0, 1)
Fn = Range(celladdress).Offset(0, 2)
Acct = Range(celladdress).Offset(0, 3)
Fleet = Range(celladdress).Offset(0, 4)
Bldg = Range(celladdress).Offset(0, 5)
Case "B"
Dept = Range(celladdress).Offset(0, -1)
Loc = Range(celladdress).Offset(0, 0)
Fn = Range(celladdress).Offset(0, 1)
Acct = Range(celladdress).Offset(0, 2)
Fleet = Range(celladdress).Offset(0, 3)
Bldg = Range(celladdress).Offset(0, 4)
Case "C"
Dept = Range(celladdress).Offset(0, -2)
Loc = Range(celladdress).Offset(0, -1)
Fn = Range(celladdress).Offset(0, 0)
Acct = Range(celladdress).Offset(0, 1)
Fleet = Range(celladdress).Offset(0, 2)
Bldg = Range(celladdress).Offset(0, 3)
Case "D"
Dept = Range(celladdress).Offset(0, -3)
Loc = Range(celladdress).Offset(0, -2)
Fn = Range(celladdress).Offset(0, -1)
Acct = Range(celladdress).Offset(0, 0)
Fleet = Range(celladdress).Offset(0, 1)
Bldg = Range(celladdress).Offset(0, 2)
Case "E"
Dept = Range(celladdress).Offset(0, -4)
Loc = Range(celladdress).Offset(0, -3)
Fn = Range(celladdress).Offset(0, -2)
Acct = Range(celladdress).Offset(0, -1)
Fleet = Range(celladdress).Offset(0, 0)
Bldg = Range(celladdress).Offset(0, 1)
Case "F"
Dept = Range(celladdress).Offset(0, -5)
Loc = Range(celladdress).Offset(0, -4)
Fn = Range(celladdress).Offset(0, -3)
Acct = Range(celladdress).Offset(0, -2)
Fleet = Range(celladdress).Offset(0, -1)
Bldg = Range(celladdress).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

Bob Kilmer
Worksheet_Change Still Not Executing....
 
Yeah. How about something of a complete description of the current problem
for those of us who haven't been following every last one of your messages?
Symptoms, for instance.

What do you mean by the following?

EntryIsValid(1) = Dept
EntryIsValid(2) = Loc
EntryIsValid(3) = Fn
EntryIsValid(4) = Acct
EntryIsValid(5) = Fleet
EntryIsValid(6) = Bldg

What do you expect it to do?

" wrote in message
...

Any suggestions....?? Most appreciated.


Code in Sheet1

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.Address([False], [False]))
MsgBox "Dept: " & ValidateCode(1) & vbCrLf & _
"Loc: " & ValidateCode(2) & vbCrLf & _
"Fn: " & ValidateCode(3) & vbCrLf & _
"Acct: " & ValidateCode(4) & vbCrLf & _
"Fleet: " & ValidateCode(5) & vbCrLf & _
"Bldg: " & ValidateCode(6)
End If
Next cell
End Sub

Code in Module

Private Function EntryIsValid(celladdress) As Variant
Dim Dept As Variant
Dim Loc As Variant
Dim Fn As Variant
Dim Acct As Variant
Dim Fleet As Variant
Dim Bldg As Variant
Dim CellCase As String

CellCase = Left(CStr(celladdress), 1)
Select Case CellCase
Case "A"
Dept = Range(celladdress).Offset(0, 0)
Loc = Range(celladdress).Offset(0, 1)
Fn = Range(celladdress).Offset(0, 2)
Acct = Range(celladdress).Offset(0, 3)
Fleet = Range(celladdress).Offset(0, 4)
Bldg = Range(celladdress).Offset(0, 5)
Case "B"
Dept = Range(celladdress).Offset(0, -1)
Loc = Range(celladdress).Offset(0, 0)
Fn = Range(celladdress).Offset(0, 1)
Acct = Range(celladdress).Offset(0, 2)
Fleet = Range(celladdress).Offset(0, 3)
Bldg = Range(celladdress).Offset(0, 4)
Case "C"
Dept = Range(celladdress).Offset(0, -2)
Loc = Range(celladdress).Offset(0, -1)
Fn = Range(celladdress).Offset(0, 0)
Acct = Range(celladdress).Offset(0, 1)
Fleet = Range(celladdress).Offset(0, 2)
Bldg = Range(celladdress).Offset(0, 3)
Case "D"
Dept = Range(celladdress).Offset(0, -3)
Loc = Range(celladdress).Offset(0, -2)
Fn = Range(celladdress).Offset(0, -1)
Acct = Range(celladdress).Offset(0, 0)
Fleet = Range(celladdress).Offset(0, 1)
Bldg = Range(celladdress).Offset(0, 2)
Case "E"
Dept = Range(celladdress).Offset(0, -4)
Loc = Range(celladdress).Offset(0, -3)
Fn = Range(celladdress).Offset(0, -2)
Acct = Range(celladdress).Offset(0, -1)
Fleet = Range(celladdress).Offset(0, 0)
Bldg = Range(celladdress).Offset(0, 1)
Case "F"
Dept = Range(celladdress).Offset(0, -5)
Loc = Range(celladdress).Offset(0, -4)
Fn = Range(celladdress).Offset(0, -3)
Acct = Range(celladdress).Offset(0, -2)
Fleet = Range(celladdress).Offset(0, -1)
Bldg = Range(celladdress).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

Bob Kilmer
Worksheet_Change Still Not Executing....
 
I think this version of (Public, not Private) EntryIsValid does what the
long form you gave is trying to do. Correct me if I am wrong.

'--------------------------------
Public Function EntryIsValid(celladdress As String) As Range
Set EntryIsValid = Intersect(Range(celladdress).EntireRow, Range("A:F"))
End Function

'--------------------------------
Sub DemoEntryIsValid() 'to try it

Dim cell As Range, rng As Range
Set rng = EntryIsValid("A1")
Debug.Print "-----------"
For Each cell In rng
Debug.Print cell.Text
Next cell

'or
Debug.Print "-----------"
Debug.Print rng(1).Text
Debug.Print rng(2).Text
Debug.Print rng(3).Text
Debug.Print rng(4).Text
Debug.Print rng(5).Text
Debug.Print rng(6).Text

End Sub

'--------------------------------

You'd use something like
Dim ValidateCode As Range
....
Set ValidateCode = EntryIsValid(cell.Address([False], [False]))
If Not ValidateCode is Nothing Then
....
End If


Or just

Set ValidateCode = Intersect(Range(cell.Address([False],
[False])).EntireRow, Range("A:F"))
etc.

BTW, Range("A1:F65536") is Range("A:F") is Columns("A:F")

Bob

" wrote in message
...

Any suggestions....?? Most appreciated.


Code in Sheet1

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.Address([False], [False]))
MsgBox "Dept: " & ValidateCode(1) & vbCrLf & _
"Loc: " & ValidateCode(2) & vbCrLf & _
"Fn: " & ValidateCode(3) & vbCrLf & _
"Acct: " & ValidateCode(4) & vbCrLf & _
"Fleet: " & ValidateCode(5) & vbCrLf & _
"Bldg: " & ValidateCode(6)
End If
Next cell
End Sub

Code in Module

Private Function EntryIsValid(celladdress) As Variant
Dim Dept As Variant
Dim Loc As Variant
Dim Fn As Variant
Dim Acct As Variant
Dim Fleet As Variant
Dim Bldg As Variant
Dim CellCase As String

CellCase = Left(CStr(celladdress), 1)
Select Case CellCase
Case "A"
Dept = Range(celladdress).Offset(0, 0)
Loc = Range(celladdress).Offset(0, 1)
Fn = Range(celladdress).Offset(0, 2)
Acct = Range(celladdress).Offset(0, 3)
Fleet = Range(celladdress).Offset(0, 4)
Bldg = Range(celladdress).Offset(0, 5)
Case "B"
Dept = Range(celladdress).Offset(0, -1)
Loc = Range(celladdress).Offset(0, 0)
Fn = Range(celladdress).Offset(0, 1)
Acct = Range(celladdress).Offset(0, 2)
Fleet = Range(celladdress).Offset(0, 3)
Bldg = Range(celladdress).Offset(0, 4)
Case "C"
Dept = Range(celladdress).Offset(0, -2)
Loc = Range(celladdress).Offset(0, -1)
Fn = Range(celladdress).Offset(0, 0)
Acct = Range(celladdress).Offset(0, 1)
Fleet = Range(celladdress).Offset(0, 2)
Bldg = Range(celladdress).Offset(0, 3)
Case "D"
Dept = Range(celladdress).Offset(0, -3)
Loc = Range(celladdress).Offset(0, -2)
Fn = Range(celladdress).Offset(0, -1)
Acct = Range(celladdress).Offset(0, 0)
Fleet = Range(celladdress).Offset(0, 1)
Bldg = Range(celladdress).Offset(0, 2)
Case "E"
Dept = Range(celladdress).Offset(0, -4)
Loc = Range(celladdress).Offset(0, -3)
Fn = Range(celladdress).Offset(0, -2)
Acct = Range(celladdress).Offset(0, -1)
Fleet = Range(celladdress).Offset(0, 0)
Bldg = Range(celladdress).Offset(0, 1)
Case "F"
Dept = Range(celladdress).Offset(0, -5)
Loc = Range(celladdress).Offset(0, -4)
Fn = Range(celladdress).Offset(0, -3)
Acct = Range(celladdress).Offset(0, -2)
Fleet = Range(celladdress).Offset(0, -1)
Bldg = Range(celladdress).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

Bob Kilmer
Worksheet_Change Still Not Executing....
 
If you want to return an array from a function, use a temporary array and
assign the array to the function name, or assign something that returns an
array to the function name.

e.g.,
EntryIsValid = Array(Dept, Loc, Fn, Acct, Fleet, Bldg)

"Bob Kilmer" wrote in message
...
Yeah. How about something of a complete description of the current problem
for those of us who haven't been following every last one of your
messages?
Symptoms, for instance.

What do you mean by the following?

EntryIsValid(1) = Dept
EntryIsValid(2) = Loc
EntryIsValid(3) = Fn
EntryIsValid(4) = Acct
EntryIsValid(5) = Fleet
EntryIsValid(6) = Bldg

What do you expect it to do?

" wrote in message
...

Any suggestions....?? Most appreciated.


Code in Sheet1

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.Address([False], [False]))
MsgBox "Dept: " & ValidateCode(1) & vbCrLf & _
"Loc: " & ValidateCode(2) & vbCrLf & _
"Fn: " & ValidateCode(3) & vbCrLf & _
"Acct: " & ValidateCode(4) & vbCrLf & _
"Fleet: " & ValidateCode(5) & vbCrLf & _
"Bldg: " & ValidateCode(6)
End If
Next cell
End Sub

Code in Module

Private Function EntryIsValid(celladdress) As Variant
Dim Dept As Variant
Dim Loc As Variant
Dim Fn As Variant
Dim Acct As Variant
Dim Fleet As Variant
Dim Bldg As Variant
Dim CellCase As String

CellCase = Left(CStr(celladdress), 1)
Select Case CellCase
Case "A"
Dept = Range(celladdress).Offset(0, 0)
Loc = Range(celladdress).Offset(0, 1)
Fn = Range(celladdress).Offset(0, 2)
Acct = Range(celladdress).Offset(0, 3)
Fleet = Range(celladdress).Offset(0, 4)
Bldg = Range(celladdress).Offset(0, 5)
Case "B"
Dept = Range(celladdress).Offset(0, -1)
Loc = Range(celladdress).Offset(0, 0)
Fn = Range(celladdress).Offset(0, 1)
Acct = Range(celladdress).Offset(0, 2)
Fleet = Range(celladdress).Offset(0, 3)
Bldg = Range(celladdress).Offset(0, 4)
Case "C"
Dept = Range(celladdress).Offset(0, -2)
Loc = Range(celladdress).Offset(0, -1)
Fn = Range(celladdress).Offset(0, 0)
Acct = Range(celladdress).Offset(0, 1)
Fleet = Range(celladdress).Offset(0, 2)
Bldg = Range(celladdress).Offset(0, 3)
Case "D"
Dept = Range(celladdress).Offset(0, -3)
Loc = Range(celladdress).Offset(0, -2)
Fn = Range(celladdress).Offset(0, -1)
Acct = Range(celladdress).Offset(0, 0)
Fleet = Range(celladdress).Offset(0, 1)
Bldg = Range(celladdress).Offset(0, 2)
Case "E"
Dept = Range(celladdress).Offset(0, -4)
Loc = Range(celladdress).Offset(0, -3)
Fn = Range(celladdress).Offset(0, -2)
Acct = Range(celladdress).Offset(0, -1)
Fleet = Range(celladdress).Offset(0, 0)
Bldg = Range(celladdress).Offset(0, 1)
Case "F"
Dept = Range(celladdress).Offset(0, -5)
Loc = Range(celladdress).Offset(0, -4)
Fn = Range(celladdress).Offset(0, -3)
Acct = Range(celladdress).Offset(0, -2)
Fleet = Range(celladdress).Offset(0, -1)
Bldg = Range(celladdress).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


All times are GMT +1. The time now is 05:54 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com