Drop down list reliant on condition
Hi,
Is there code I can use instead of an entry in a cell to archive the following. I have a drop down list in a cell that is created from the menu item Data, Validation.... and refers to a named range, this range name is defined with a formula that varies on what text is in a particular cell, the formula is: =IF(Menu!$A$1="Adm",Codes!$C$1:$C$30,Codes!$B$1:$B $20). When Adm is in cell A1 my named range refers to Codes!$C$1:$C$30 whereas is A1 is anything else, the name range refers to Codes!$B$1:$B$20. Thanks, Rob |
Drop down list reliant on condition
I hope I've read your post correctly.
This code will set your data validation list dependant on the value in A1. Put the code in the module of the worksheet that will contain the list: 'Assume data validation list goes in B1 Private Sub Worksheet_Change(ByVal Target As Range) Dim ListRange As String If Target = ActiveSheet.Range("A1") Then If ActiveSheet.Range("A1") = "Adm" Then ListRange = "=$C$1:$C$30" Else ListRange = "=$B$1:$B$21" End If With Selection.Validation .Delete .Add Type:=xlValidateList, _ AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=ListRange .IgnoreBlank = True .InCellDropdown = True .ShowInput = True .ShowError = True End With End If End Sub -----Original Message----- Hi, Is there code I can use instead of an entry in a cell to archive the following. I have a drop down list in a cell that is created from the menu item Data, Validation.... and refers to a named range, this range name is defined with a formula that varies on what text is in a particular cell, the formula is: =IF(Menu!$A$1="Adm",Codes!$C$1:$C$30,Codes!$B$1:$ B$20). When Adm is in cell A1 my named range refers to Codes!$C$1:$C$30 whereas is A1 is anything else, the name range refers to Codes!$B$1:$B$20. Thanks, Rob . |
Drop down list reliant on condition
Assume the named range is List1
if SomeCondition then worksheets("Codes").Range("C1:C30").Name = "List1" else worksheets("Codes").Range("B1:B30").Name = "List1" End If I don't know what condition sets the cell to ADM, so I can't guess what condition to put in the code or to tell you where to put the code so it updates the dropdown at the appropriate time. You could put it in the SelectionChange event and test if the cell with the dropdown is what triggered the event. -- Regards, Tom Ogilvy "Rob" wrote in message ... Hi, Is there code I can use instead of an entry in a cell to archive the following. I have a drop down list in a cell that is created from the menu item Data, Validation.... and refers to a named range, this range name is defined with a formula that varies on what text is in a particular cell, the formula is: =IF(Menu!$A$1="Adm",Codes!$C$1:$C$30,Codes!$B$1:$B $20). When Adm is in cell A1 my named range refers to Codes!$C$1:$C$30 whereas is A1 is anything else, the name range refers to Codes!$B$1:$B$20. Thanks, Rob |
Drop down list reliant on condition
Thanks Tod and Tom, I'll experiment over the weekend with the example.
Regards, Rob "Tom Ogilvy" wrote in message ... Assume the named range is List1 if SomeCondition then worksheets("Codes").Range("C1:C30").Name = "List1" else worksheets("Codes").Range("B1:B30").Name = "List1" End If I don't know what condition sets the cell to ADM, so I can't guess what condition to put in the code or to tell you where to put the code so it updates the dropdown at the appropriate time. You could put it in the SelectionChange event and test if the cell with the dropdown is what triggered the event. -- Regards, Tom Ogilvy "Rob" wrote in message ... Hi, Is there code I can use instead of an entry in a cell to archive the following. I have a drop down list in a cell that is created from the menu item Data, Validation.... and refers to a named range, this range name is defined with a formula that varies on what text is in a particular cell, the formula is: =IF(Menu!$A$1="Adm",Codes!$C$1:$C$30,Codes!$B$1:$B $20). When Adm is in cell A1 my named range refers to Codes!$C$1:$C$30 whereas is A1 is anything else, the name range refers to Codes!$B$1:$B$20. Thanks, Rob |
All times are GMT +1. The time now is 12:30 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com