Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change procedure
On other quick question -
I'm trying to pass the "Cell" to the Function, not just its value, so that I can figure out its address and column - but when I pass EntryIsValid(cell) it appears its passing the value I put into the cell When I pass cell.address - then I can't figure out how to use that to use Range().Offset.... Dave Peterson wrote: 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change procedure
Without looking too much, you could be more explicit:
Private Function EntryIsValid(cell) As Variant becomes Private Function EntryIsValid(cell as range) As Variant And I've found that it's better for me to be explicit when I refer to an object--instead of relying on its default property. If CInt(cell) < cell Then becomes: If CInt(cell.value) < cell.value Then instead of just plain cell. (and other places, too) Dept = Range("A1").Offset(0, -5).value instead of Dept = Range("A1").Offset(0, -5) I find I make fewer mistakes while writing the code initially and it makes it easier when reviewing it later. " wrote: On other quick question - I'm trying to pass the "Cell" to the Function, not just its value, so that I can figure out its address and column - but when I pass EntryIsValid(cell) it appears its passing the value I put into the cell When I pass cell.address - then I can't figure out how to use that to use Range().Offset.... Dave Peterson wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet_Change procedure | Excel Programming | |||
Worksheet_Change 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 |