Dropdown in cell base on a selection of a different drop down
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 |
Dropdown in cell base on a selection of a different drop down
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 |
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 |
Dropdown in cell base on a selection of a different drop down
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 |
All times are GMT +1. The time now is 12:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com