PDA

View Full Version : Select Case & Data Validation Issues



Simon Lloyd
04-16-2009, 02:30 PM
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?

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

DannyUk
04-16-2009, 02:51 PM
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
04-16-2009, 03:28 PM
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
04-16-2009, 03:32 PM
How about
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

Simon Lloyd
04-16-2009, 04:11 PM
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
04-16-2009, 04:42 PM
if aCell is a single cell, aCell.SpecialCells(x) returns the same range as Cells.SpecialCells(x)

Simon Lloyd
04-16-2009, 05:13 PM
Sorry Mike thats unclear!, i meant with my first select case
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
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
04-16-2009, 06:20 PM
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.)