Excel '03 Data Validation doesn't trigger Worksheet Change

[vbaHack]

I have a bit of code that calls a formatting sub depending on which cell is modified. It is triggered by the Worksheet_Change event, determines which cell is modified, and either calls the formatting sub or doesn't based on the location of the modified cell.

Some of the columns in the sheet have data validation with drop downs. If I select a value from the drop down, it doesn't trigger the Worksheet_Change. If I type a value into the same cell, it does.

This was apparently an issue in Excel '97, but supposedly fixed in '03? Any ideas?

Thanks

 

[vbaHack]

I can't find anywhere in the code that 'EnableEvents' is being disabled (did a search for 'EnableEvents' through the whole project). The code is in a worksheet module. Tried Debug->Compile. Created a new workbook and DV triggers Worksheet_Change the way it should...
---
Here is the supporting code - remember, I don't claim to be good at this!...

Public Pipeline As Range
Public CREProjNum As Range
Public PropID As Range
Public CKPProjNum As Range
Public Bldg As Range
Public ProjName As Range
Public ApprovalYear As Range
Public CapCats As Range
Public CKPMgmtFee As Range
Public ProfFeesLessCKP As Range
Public ProjExp As Range
Public OriginalBudget As Range
Public Approvals As Range
Public ProjectedApproveDate As Range
Public DateWkbkSubmitted As Range
Public TargetDate As Range
Public PSUBudget As Range
Public ActProjDetail As Range
Public WorkStatus As Range
Public ProjStatus As Range
Public FundingSource As Range
Public Accountabilities As Range
Public Scope As Range
Public Justification As Range
Public Comments As Range
Public Priority As Range
Public ListLength As Integer

Sub InitializeRanges()
    ' **********************************************************************************
    '       FINDS THE LAST ROW OF DATA AND INITIALIZES THE LIST LENGTH
    Dim OrigBudCol As Integer
    Dim OrigBudColRange As Range
'    UnhideColumns
    Set OrigBudColRange = ThisWorkbook.ActiveSheet.Range("A:Z").Find(What:="Original Budget", After:=ThisWorkbook.ActiveSheet.Range("A:A").Cells(1), LookIn:=xlValues, _
                                  LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                  MatchCase:=False)
    If OrigBudColRange Is Nothing Then
        Dim intAns As Integer
        MsgBox "An error message will pop up when this window closes" _
                                & Chr(13) & "because the code can't find the 'Original Budget' column." _
                                & Chr(13) & "Please unhide this column and try again.", vbOKOnly, "Error Coming"
    End If
    OrigBudCol = OrigBudColRange.Column
    ListLength = (Cells(1000, OrigBudCol).End(xlUp).Row) + 10
    ' **********************************************************************************
    Set Pipeline = ActiveSheet.Range(Cells(5, "A"), Cells(ListLength, "A"))
    Set CREProjNum = ActiveSheet.Range(Cells(5, "B"), Cells(ListLength, "B"))
    Set PropID = ActiveSheet.Range(Cells(5, "C"), Cells(ListLength, "C"))
    Set CKPProjNum = ActiveSheet.Range(Cells(5, "D"), Cells(ListLength, "D"))
    Set Bldg = ActiveSheet.Range(Cells(5, "E"), Cells(ListLength, "E"))
    Set ProjName = ActiveSheet.Range(Cells(5, "F"), Cells(ListLength, "F"))
    Set ApprovalYear = ActiveSheet.Range(Cells(5, "G"), Cells(ListLength, "G"))
    Set CapCats = ActiveSheet.Range(Cells(5, "H"), Cells(ListLength, "P"))
    Set ProfFeesLessCKP = ActiveSheet.Range(Cells(5, "J"), Cells(ListLength, "J"))
    Set CKPMgmtFee = ActiveSheet.Range(Cells(5, "K"), Cells(ListLength, "K"))  '********************
    Set ProjExp = ActiveSheet.Range(Cells(5, "R"), Cells(ListLength, "R"))
    Set OriginalBudget = ActiveSheet.Range(Cells(5, "S"), Cells(ListLength, "S"))
    Set Approvals = ActiveSheet.Range(Cells(5, "T"), Cells(ListLength, "V"))
    Set ProjectedApproveDate = ActiveSheet.Range(Cells(5, "T"), Cells(ListLength, "T"))
    Set DateWkbkSubmitted = ActiveSheet.Range(Cells(5, "U"), Cells(ListLength, "U"))
    Set TargetDate = ActiveSheet.Range(Cells(5, "W"), Cells(ListLength, "W"))
    Set PSUBudget = ActiveSheet.Range(Cells(5, "X"), Cells(ListLength, "X"))
    Set ActProjDetail = ActiveSheet.Range(Cells(5, "X"), Cells(ListLength, "AD"))
    Set WorkStatus = ActiveSheet.Range(Cells(5, "AC"), Cells(ListLength, "AC"))
    Set ProjStatus = ActiveSheet.Range(Cells(5, "AD"), Cells(ListLength, "AD"))
    Set FundingSource = ActiveSheet.Range(Cells(5, "AE"), Cells(ListLength, "AE"))
    Set Accountabilities = ActiveSheet.Range(Cells(5, "AF"), Cells(ListLength, "AG"))
    Set Scope = ActiveSheet.Range(Cells(5, "AH"), Cells(ListLength, "AH"))
    Set Justification = ActiveSheet.Range(Cells(5, "AI"), Cells(ListLength, "AI"))
    Set Comments = ActiveSheet.Range(Cells(5, "AJ"), Cells(ListLength, "AJ"))
    Set Priority = ActiveSheet.Range(Cells(5, "AK"), Cells(ListLength, "AK"))
 
End Sub

Function DoFormatting(Target)
    Dim rngCell As Range
    Dim rngModCell As Range
    Dim rngWholeRow As Range
    Dim intRow As Integer
    Dim intCol As Integer
    intRow = Range(Target).Row
    intCol = Range(Target).Column
 
    If (Cells(intRow, 1).Interior.ColorIndex <> 36) And (Cells(intRow, 1).Interior.ColorIndex <> 15) Then
 
        If ((Cells(intRow, WorkStatus.Columns(1).Column) = "On Hold")) Then  'If On Hold
            FormatOnHold (intRow)
        Else
        If ((Cells(intRow, WorkStatus.Columns(1).Column) = "Complete") _
            And (Cells(intRow, ProjStatus.Columns(1).Column) = "Closed")) _
            Or (Cells(intRow, WorkStatus.Columns(1).Column) = "Canceled") Then  'If Complete and Closed, or Canceled
                FormatCompleteClosedCanceled (intRow)
        Else
            FormatFundingSource (intRow)
        End If
 
        'Look at Pipeline status
        If ((Cells(intRow, Pipeline.Columns(1).Column) = "No")) Then   ' If not in Pipeline
            Cells(intRow, ProjName.Column).Font.ColorIndex = 3  ' Red text
        Else   'If No and text is already red, look to see if Cap or DDA
            If ((Cells(intRow, Pipeline.Columns(1).Column) = "Yes")) _
            And (Cells(intRow, ProjName.Column).Font.ColorIndex = 3) Then   ' If in Pipeline, but red
                If ((Cells(intRow, FundingSource.Columns(1).Column) = "DDA")) Then ' If DDA, make project name text green
                    Cells(intRow, ProjName.Column).Font.ColorIndex = 10
                Else
                    Cells(intRow, ProjName.Column).Font.ColorIndex = 1  'If Cap, make project name text black
                End If
            End If
        End If
 
        FormatHyperlink (intRow)  'Look for hyperlink in project number
 
    End If
    End If
End Function

 

[vbaHack]

Ok - I tried the spreadsheet on a box running Office '07 and it works, so I would imagine there's nothing going on with the code. In '03 I can paste the data in the spreadsheet into a blank template, a little at a time, and it will work - up to a point. Once I get a few hundred rows pasted in, Excel starts behaving oddly - does some kind of a weird refresh after I select from the drop downs. It seems to be related to the scope of the data. Unless someone has any other ideas I'll assume that my Excel app is flaky and talk to my IT guys about reinstalling...

Thanks for the input, everyone.

 

[bf024]

I've got this exact same build of Excel, and have the same problem. My macros are working, if I type a value into the cells. However if I use the mouse to select an item in the Data Validation drop-down, the macro is not working. I'm new to this, but it appears that the Change event is not sensed when you choose an item, only when you type. I know this runs counter to docs I've read, but that's what I'm seeing.