Worksheet_Change Still Not Executing....

M

marston.gould

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
 
B

Bob Kilmer

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?

[email protected] said:
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
Click to expand...
 
B

Bob Kilmer

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

[email protected] said:
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
Click to expand...
 
B

Bob Kilmer

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 said:
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?

[email protected] said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Worksheet_Change procedure 1
Select Case code error 8
For Eachcell in Range tranpose comma seperated values to a list in D 4
Clean this select case code up a bit. 2
Remove Identical words 0
Copy row info but not in normal column sequence 6
Copy Application.Union(c.Offset(0, 1), c.Offset(0, 2)... to a list onsame & another sheet 27
Select Case Code does not run... 10

Top