Dropdown in cell base on a selection of a different drop down

[paapa21]

Hi all, I'm wondering if someone can help me out here. I'm trying to automate
an excel sheet whereby a selection of a value form a drop down list will
present a differnt dropdown in adifferent cell. However my code is throwing
Run-time error 1004. Below is the code and the debug point to .Ignoreblank =
true

Dim CommentRed As String
Dim CommentAmber As String
Dim CommentGreen As String

CommentRed = "OTC, WIP in course, Credit in course, Credit Submitted"
CommentAmber = "Credit Approved, Awaiting Confirmation, In documentation"
CommentGreen = "In documentation, Deal Completed"


If Target.Column = 4 Then
ThisRow = Target.Row
With Range("E" & ThisRow).Validation
.Delete
Select Case Range("M" & ThisRow).Value
Case "WIP Red"
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:=xlEqual, Formula1:=CommentRed
Case "WIP Amber"
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:=xlEqual, Formula1:=CommentAmber
Case "WIP Green"
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:=xlEqual, Formula1:=CommentGreen
End Select
.IgnoreBlank = True
.InCellDropdown = True
End With
End If

Can some one please help me out. I'm behind deadline

 

[Billy Liddel]

Hi
Do you need to do this in code? Debra Dalgliesh shows how to do this without
code at:
http://www.contextures.com/xlDataVal13.html

Hope this helps, I have never tried to code this myself.
Peter

 

[Dave Peterson]

You're only adding the validation rules for 3 cases. So if the cell in column M
of that row doesn't contain one of those 3 cases, then you're trying to add the
..ignoreblank to a validation that doesn't exist.

And just a guess...

If that cell has "wip Red", then it won't match "WIP Red". The case of the text
is important.

I'd use:

Select Case lcase(Range("M" & ThisRow).Value)
Case lcase("WIP Red")

(for all 3 rules, too)

In fact, I'd use:

Case "WIP Red"
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, _
Formula1:=CommentRed
.IgnoreBlank = True
.InCellDropdown = True
Case "WIP Amber"
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, _
Formula1:=CommentAmber
.IgnoreBlank = True
.InCellDropdown = True
'same with the third case, too.

Or keep track if the code applied data|validation so I could check that "flag"
before I try to change the rule that may not be there.

 

[Dave Peterson]

I didn't put the lcase() stuff in here:

Case lcase("WIP Red")
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, _
Formula1:=CommentRed
.IgnoreBlank = True
.InCellDropdown = True

Case lcase("WIP Amber")
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, _
Formula1:=CommentAmber
.IgnoreBlank = True
.InCellDropdown = True

....