Select Case & Data Validation Issues

[Simon Lloyd]

Hi all i am using Select Case to check for a value and then colouring the cell dependant on the value, however now that i have made the cells data validation so we have a dropdown for ease of use the Select Case statement doesn't work, so i tried the following and it bugs out at the first case, adding On Error..... allowed the statement to work to a fashin in so much as, it colours each validated cell the first case' colour when a selection is made.

Can someone point me in the right direction in to getting data validation to work with the case statement?

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Select Case Target.SpecialCells(xlCellTypeAllValidation)
Case Is = "Sick"
Target.Interior.ColorIndex = 4 'Green
Case Is = "KL"
Target.Interior.ColorIndex = 3 'yellow
Case Is = "DIL"
Target.Interior.ColorIndex = 7 'Purple
Case Is = "OFF"
Target.Interior.ColorIndex = 5 'Blue
End Select
End Sub[/VBA]

[DannyUk]

Hi Simon,

Does this help

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range
    
    On Error GoTo ErrHandler:
    
   Set Rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
    
        If Not Intersect(Target, Rng) Is Nothing Then
                
            With Target
                Select Case LCase(.Value)
                    Case "sick":    .Interior.ColorIndex = 4 'Green
                    Case "kl":      .Interior.ColorIndex = 3 'yellow
                    Case "dil":     .Interior.ColorIndex = 7 'Purple
                    Case "off":     .Interior.ColorIndex = 5 'Blue
                 End Select
            End With
        End If
    
ErrHandler:
    If Err.Number = 1004 Then
            MsgBox "No Data Validation found!"
            Exit Sub
    End If
End Sub

Danny

[p45cal]

re: "i am using Select Case to check for a value"

a singular value I'd guess, in a single cell, the one you're changing?
Running your sub with a single cell as target then
Target.SpecialCells(xlCellTypeAllValidation)
will refer to a range of cells, being all the cells with validation on that sheet, if that range is not contiguous and the first area in that range is a single cell you might be lucky and get a value from
Target.SpecialCells(xlCellTypeAllValidation).value
but if the first area (or the whole area for that matter) is a range containing 2 or more cells then you'll get a type mismatch for:
?Target.SpecialCells(xlCellTypeAllValidation).value.
Had you got
$D$4:$D$5,$D$9 (where the first area is two cells)
from typing
?Target.SpecialCells(xlCellTypeAllValidation).address
in the immediate pane, you'd get an error when you typed:
?Target.SpecialCells(xlCellTypeAllValidation).value
in the immedaite pane.

It's probably colouring the first case's colour because the range referred to in Target.SpecialCells(xlCellTypeAllValidation) is likely always to be the same regardless of which cell you're changing - try stepping through the code and as soon as it the sub begins executing, type this in the immediate pane:
?Target.SpecialCells(xlCellTypeAllValidation).address
and you'll probably get something like this:
$D$9,$D$4:$D$6
where the first area is a single cell, so typing this in the immediate pane:
?Target.SpecialCells(xlCellTypeAllValidation).value
will always return "Sick", regardless of which cell you're changing, because that's what happens to be (in this example) in D9.

Are you trying to colour multiple cells at once?

[mikerickson]

How about
[VBA]Dim keyCells as Range, oneCell as Range
On Error Resume Next
Set keyCells = Target.SpecialCells(xlCellTypeAllValidation)
On Error Goto 0

If Not keyCells Is Nothing Then
For Each oneCell in keyCells
Select Case oneCell.Value
Rem your code
End Select
Next oneCell
End If[/VBA]

[Simon Lloyd]

Danny, thanks!, i just couldn't get my head round it!

p45cal, thansk for the explanation.

Mike, your's worked too

Anyone know the reason that without using specialcells the simple Select Case doesn't work?

[mikerickson]

if aCell is a single cell, aCell.SpecialCells(x) returns the same range as Cells.SpecialCells(x)

[Simon Lloyd]

Sorry Mike thats unclear!, i meant with my first select case[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.value
Case Is = "Sick"
Target.Interior.ColorIndex = 4 'Green
Case Is = "KL"
Target.Interior.ColorIndex = 3 'yellow
Case Is = "DIL"
Target.Interior.ColorIndex = 7 'Purple
Case Is = "OFF"
Target.Interior.ColorIndex = 5 'Blue
End Select
End Sub

[/vba]which was before i added specialcells, it is that which wouldn't work with data validation but any version of xl later than 2000 should trigger a change event.

[mikerickson]

My Excel 2004 also has a problem like that.
Selection from a Validation dropdown does not trigger a Change event.
Well, most of the time. Exactly when is an ongoing question for me.

(historical note: Around 2000-2003 VBA Windows upgraded, while Mac continued using VBA version 5(?). The introduction of the VBA function Replace marks the divergence between Mac and Windows VBA.)

My work-around for Validation list selection not triggering a Change event, is to use a spreadsheet formula that has the Validated cell as a precedent to trigger the Calculate event. (e.g. =COUNTA(A:CV))

Why SpecialCells would cure this, I can't understand. Excel shouldn't "see" the SpecialCells command until after the Change event has triggered. (Perhaps the issue is not in list validation triggering the Change event, but in the way that it passes Target.)