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

 

[Datsmart]

Data Validation selection triggers the "Worksheet_Change" on my XL version 2000.
Can't think of a work around for Excel 97.

 

[Peter_SSs]

vbaHack

Welcome to the MrExcel board!

If using Excel 2003, the first thing I would check is to ensure 'Events' are enabled. In the VB window, ensure the Immediate Window is visible (View|Immediate Window) and then on a new line in the Immediate Window, type
Application.EnableEvents=True and press Enter

Now go back to your sheet and try changing the Validation cell by the drop-down.

 

[Smitty]

Welcome to the Board!

DV will trigger a change event in '03. John and Peter's comments notwithstanding, do you have the ranges in questions set to intersect with the target ranges? It can be overlooked at times.

Hope that helps,

 

[vbaHack]

Thanks for the repies, all... Happy New Year!

Peter: Tried the Application.EnableEvents=True with no success...

Smitty: Sorry, not sure exactly what you're asking for about the ranges intersecting. I'll explain a little further and maybe it'll help.

The DV in the cell is set to allow a list. In the DV 'Source' field I entered a comma-separated list of values from which the user can select. I'm not referencing any ranges on the worksheet in the criteria. Just for grins, I set up DV in another cell using a named range as the source. No luck.

 

[Richard Schollar]

Hi

Smitty actually meant whether the cells with DV were being taken into account in the Worksheet_Change code itself - coul you post it so we can have a look?

 

[RoryA]

Works fine for me with 2003 - what is the exact build number and SP of your copy of 2003?

 

[vbaHack]

rorya: I'm running Excel '03 (11.8237.8221) SP3

Richard: Here is the Worksheet_Change code I'm trying to run. When I use the DV dropdown, it never hits the breakpoint I put in the first available line. It doesn't throw any errors and doesn't seem to get far enough in the code to bail for seeing something it doesn't like...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngModCell As Range
Dim intCol As Integer

'Verify that public ranges have been initialized
If WorkStatus Is Nothing Then
InitializeRanges
End If
Set rngModCell = Range(Target.Address)
intCol = Range(Target.Address).Column

'If modified cell is in Work Status, Project Status, Funding Source, or Pipeline columns, run formatting sub
If (intCol = WorkStatus.Columns(1).Column) Or (intCol = ProjStatus.Columns(1).Column) _
Or (intCol = FundingSource.Columns(1).Column) Or (intCol = Pipeline.Columns(1).Column) Then

DoFormatting (rngModCell.Address)

End If
End Sub

Thanks for the help!

 

[Richard Schollar]

You've got quite a lot going on in there- can you also post the declarations you are using for the public range variables and the code behind InitializeRanges and DoFormatting please.

 

[RoryA]

If it doesn't even reach this line:

If WorkStatus Is Nothing Then

and you have checked that events are not disabled (or being disabled by other code and then reenabled), are you sure this code is in a worksheet code module? Have you compiled the code (Debug->Compile in the VBE)?