| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change procedure
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet_Change procedure
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) " wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Stop a Procedure from another procedure | Excel Discussion (Misc queries) | |||
| Calling a procedure in a procedure | Excel Programming | |||
| worksheet_change vs. calculate, and worksheet_change not running | Excel Programming | |||
| worksheet_change vs. calculate, and worksheet_change not running | Excel Programming | |||
Linear Mode
