Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data validation question
Hi,
Can anyone please tell me if it is possible to have a cell, say B1, to have two data validation lists dependant on a the contents of another cell? What I mean is: If Cell A1 = 1 then cell B1 will use the data validation list C1:C10, but if A1 = 2 then cell B1 will use data validation list D1:D10 Any suggestions of howto obtain this result (if it is possible) would be much appreciated. -- Cheers Peter Remove the INVALID to reply |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data validation question
Put the following in the Worksheet's code module:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$B$1" Then If Range("A1") = 1 Then With Range("B1").Validation .Delete .Add xlValidateList, xlValidAlertStop, xlBetween, "=$C$1:$C$10" .InCellDropdown = True End With End If If Range("A1") = 2 Then With Range("B1").Validation .Delete .Add xlValidateList, xlValidAlertStop, xlBetween, "=$D$1:$D$10" .InCellDropdown = True End With End If End If End Sub HTH, Merjet |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data validation question
Hello Peter,
This suggestion use no VBA code but you will also lose the functionality of Drop-down. In your data validation use custom and put this formula. Take note that if A1 is neither 1 or 2 B1 will accept any entry. Also if there is already value in B1 and you change value in A1 value in B1 will not be re-validated. Only when you change again B1. =IF(A1=1,OR(B1=(C1:C10)),IF(A1=2,OR(B1=(D1:D10))," ")) Regards, Jon-jon "Peter" wrote in message ... Hi, Can anyone please tell me if it is possible to have a cell, say B1, to have two data validation lists dependant on a the contents of another cell? What I mean is: If Cell A1 = 1 then cell B1 will use the data validation list C1:C10, but if A1 = 2 then cell B1 will use data validation list D1:D10 Any suggestions of howto obtain this result (if it is possible) would be much appreciated. -- Cheers Peter Remove the INVALID to reply |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data validation question
On Thu, 09 Oct 2003 22:06:35 GMT, "merjet" wrote:
Put the following in the Worksheet's code module: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$B$1" Then If Range("A1") = 1 Then With Range("B1").Validation .Delete .Add xlValidateList, xlValidAlertStop, xlBetween, "=$C$1:$C$10" .InCellDropdown = True End With End If If Range("A1") = 2 Then With Range("B1").Validation .Delete .Add xlValidateList, xlValidAlertStop, xlBetween, "=$D$1:$D$10" .InCellDropdown = True End With End If End If End Sub HTH, Merjet Hi Merjet, Thanks for your reply - I think this is exactly what I need. I have entered the above in what i think is the code module of a spreadsheet, with two lists in columns C & D, but it doesn't work - I guess I must be doing something wrong - I'm afraid it isn't obvious. Any suggestions as to what I need to do would be much appreciated. -- Cheers Peter Remove the INVALID to reply |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data validation question
Thanks for your reply - I think this is exactly what I need.
I have entered the above in what i think is the code module of a spreadsheet, with two lists in columns C & D, but it doesn't work - I guess I must be doing something wrong - I'm afraid it isn't obvious. Any suggestions as to what I need to do would be much appreciated. If you double-click on the sheet's icon in the VB Editor, do you see the code? Does cell A1 have a 1 or 2? Put a breakpoint in the code and select B1 to check if the code is being executed. Merjet |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation question | Excel Discussion (Misc queries) | |||
Data Validation Question | Excel Discussion (Misc queries) | |||
Data Validation Question | Excel Discussion (Misc queries) | |||
Data Validation Question | Excel Worksheet Functions | |||
Data Validation Question | Excel Discussion (Misc queries) |