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

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

"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

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

I didn't put the lcase() stuff in he

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

....

Dave Peterson wrote:

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

--

Dave Peterson