Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Worksheet_Change Still Not Executing....
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 102
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 280
Default 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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 280
Default 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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 280
Default 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





Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Executing Macros bmorganh Excel Discussion (Misc queries) 1 January 27th 06 12:03 AM
Forumlas Not Executing Trey Excel Discussion (Misc queries) 2 January 25th 06 04:02 PM
VBA - Writing in XL XP, executing in XL 97 Paulo de Arruda Borelli[_2_] Excel Programming 2 August 17th 04 05:14 AM
worksheet_change vs. calculate, and worksheet_change not running Tom Ogilvy Excel Programming 1 July 14th 03 02:51 AM
worksheet_change vs. calculate, and worksheet_change not running Ross[_5_] Excel Programming 0 July 13th 03 04:27 PM


All times are GMT +1. The time now is 10:40 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement