Dropdown in cell base on a selection of a different drop down
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.
paapa21 wrote:
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
--
Dave Peterson
|