Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change Still Not Executing....
Thanks Bob - I'll try that first thing in the AM Bob Kilmer wrote: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why are my functions not executing? | Excel Worksheet Functions | |||
Worksheet_Change Still Not Executing.... | Excel Programming | |||
Worksheet_Change Still Not Executing.... | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming |