Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatic Update of Dropdown List Box data
In cell A1 i have created a "Drop Down list " using validation which calls
data from B1:B4. and in A2 i have created a "drop Down List" using validation which Calls data from D1:D4. And B1 , B2, B3, B4 contains data Like 1, 2, 3, 4 respectively. C1 = A1 cell data then in D1 : D4 cell i have used vlookup function to create a dynamic list i.e. if in C1 data is = 1 the D1:D4 shows 1.1, 1.2, 1.4, 1.4 respectively and if in C1 data is = 2 the D1:D4 shows 2.1, 2.2, 2.4, 2.4 respectively Which then shows up in the list box of A2. The problem i face is say first i selected 1 in cell A1 and the list Box of A2 shows (1.1, 1.2, 1.4, 1.4) and i selected 1.2 but later on i chaged A1 cell value to 2 but but subsequently D1:D4 value chaged to 2.1, 2.2, 2.4, 2.4 respectively but as i entered data in A2 didn't chaged. what i want is if i chaged data in A1 then the the cell A2 value to be made "" (Blank) and a alert message to provide to select data in A2. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatic Update of Dropdown List Box data
To do what you're asking, you'll need a bit of VBA code.
Try this: Select the sheet with the Data Validation Right-Click on the sheet tab Select: View Code.....(that will open the VBA editor) Paste this code into the VBA editor: '--------start of code---------- Private Sub Worksheet_Change(ByVal Target As Range) Dim rngParentCell As Range Dim rngDepCell As Range Dim rngCell As Range Set rngParentCell = Range("A1") Set rngDepCell = Intersect(Target, rngParentCell) If Not rngDepCell Is Nothing Then Set rngCell = Range("A2") rngCell.ClearContents rngCell.Select MsgBox _ Title:="Notice", _ Prompt:="You must now select an item from cell A2", _ Buttons:=vbInformation + vbOKOnly Application.SendKeys ("%{DOWN}") End If Set rngParentCell = Nothing Set rngDepCell = Nothing Set rngCell = Nothing End Sub '--------end of code---------- Now try changing the A1 value. The message should pop up. After the user clicks [OK]...A2 will be selected and the list displayed. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Rajat" wrote: In cell A1 i have created a "Drop Down list " using validation which calls data from B1:B4. and in A2 i have created a "drop Down List" using validation which Calls data from D1:D4. And B1 , B2, B3, B4 contains data Like 1, 2, 3, 4 respectively. C1 = A1 cell data then in D1 : D4 cell i have used vlookup function to create a dynamic list i.e. if in C1 data is = 1 the D1:D4 shows 1.1, 1.2, 1.4, 1.4 respectively and if in C1 data is = 2 the D1:D4 shows 2.1, 2.2, 2.4, 2.4 respectively Which then shows up in the list box of A2. The problem i face is say first i selected 1 in cell A1 and the list Box of A2 shows (1.1, 1.2, 1.4, 1.4) and i selected 1.2 but later on i chaged A1 cell value to 2 but but subsequently D1:D4 value chaged to 2.1, 2.2, 2.4, 2.4 respectively but as i entered data in A2 didn't chaged. what i want is if i chaged data in A1 then the the cell A2 value to be made "" (Blank) and a alert message to provide to select data in A2. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatic Update of Dropdown List Box data
Ron
thanx a lot, the code worked and it was just what i needed. But i have another question for you if i need to extend this code to all the sheets of the workbook what modification do i require to made. if you can tell me this it will be a great help. regards "Ron Coderre" wrote: To do what you're asking, you'll need a bit of VBA code. Try this: Select the sheet with the Data Validation Right-Click on the sheet tab Select: View Code.....(that will open the VBA editor) Paste this code into the VBA editor: '--------start of code---------- Private Sub Worksheet_Change(ByVal Target As Range) Dim rngParentCell As Range Dim rngDepCell As Range Dim rngCell As Range Set rngParentCell = Range("A1") Set rngDepCell = Intersect(Target, rngParentCell) If Not rngDepCell Is Nothing Then Set rngCell = Range("A2") rngCell.ClearContents rngCell.Select MsgBox _ Title:="Notice", _ Prompt:="You must now select an item from cell A2", _ Buttons:=vbInformation + vbOKOnly Application.SendKeys ("%{DOWN}") End If Set rngParentCell = Nothing Set rngDepCell = Nothing Set rngCell = Nothing End Sub '--------end of code---------- Now try changing the A1 value. The message should pop up. After the user clicks [OK]...A2 will be selected and the list displayed. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Rajat" wrote: In cell A1 i have created a "Drop Down list " using validation which calls data from B1:B4. and in A2 i have created a "drop Down List" using validation which Calls data from D1:D4. And B1 , B2, B3, B4 contains data Like 1, 2, 3, 4 respectively. C1 = A1 cell data then in D1 : D4 cell i have used vlookup function to create a dynamic list i.e. if in C1 data is = 1 the D1:D4 shows 1.1, 1.2, 1.4, 1.4 respectively and if in C1 data is = 2 the D1:D4 shows 2.1, 2.2, 2.4, 2.4 respectively Which then shows up in the list box of A2. The problem i face is say first i selected 1 in cell A1 and the list Box of A2 shows (1.1, 1.2, 1.4, 1.4) and i selected 1.2 but later on i chaged A1 cell value to 2 but but subsequently D1:D4 value chaged to 2.1, 2.2, 2.4, 2.4 respectively but as i entered data in A2 didn't chaged. what i want is if i chaged data in A1 then the the cell A2 value to be made "" (Blank) and a alert message to provide to select data in A2. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatic Update of Dropdown List Box data
OK....This is more of a general purpose approach
There are only 2 rules: Rule_1: Each ParentList cell must have a Sheet-Level range name that contains the phrase: ListParent. Sheet-Level range names are created by prepending the sheet name to the range name. Example (if cell B5 is the parent Data Validation list for the cell below it: Names in workbook: Sheet1!ListParent01 Refers to: =Sheet1!B5 Rule_2: The Dependent List cell must be immediately below the Parent List cell When a designated Parent List cell's value changes, the below code attempts to clear the contents of the cell below the Parent List cell and display it's dropdown list. If that cell does NOT have data validation or the DV is not a list, the code processing stops. In a General Module...paste this code: '--------start of code---------- Sub EngageDependentList(ByVal rngDepCell As Range) 'This sub receives a cell as its only argument 'and attempts to display the Data Validation list for that cell Dim vTest As Variant On Error GoTo errTrap With rngDepCell If (.Validation.Type = 3) Then 'the cell has a dropdown list .ClearContents .Select MsgBox _ Title:="Notice", _ Prompt:="You must now select an item from the list", _ Buttons:=vbInformation + vbOKOnly 'Send [alt]+[down arrow] to the cell Application.SendKeys ("%{DOWN}") End If End With errTrap: End Sub '--------end of code---------- Paste this code in the code module for each sheet with Parent/Dependent lists: '--------start of code---------- Private Sub Worksheet_Change(ByVal Target As Range) Dim rngCell As Range Dim nmName As Name For Each nmName In ActiveSheet.Names 'Is the name designated as a ListParent? If InStr(1, nmName.Name, "ListParent") < 0 Then 'Test if the changed cell is THAT named cell Set rngCell = Range(nmName.RefersTo) If Not Intersect(rngCell, Target) Is Nothing Then 'Yes...So initiate the dependent list EngageDependentList _ rngDepCell:=rngCell _ .Offset(RowOffset:=1, ColumnOffset:=0) Exit Sub End If End If Next nmName End Sub '--------end of code---------- Does that help? *********** Regards, Ron XL2002, WinXP "Rajat" wrote: Ron thanx a lot, the code worked and it was just what i needed. But i have another question for you if i need to extend this code to all the sheets of the workbook what modification do i require to made. if you can tell me this it will be a great help. regards "Ron Coderre" wrote: To do what you're asking, you'll need a bit of VBA code. Try this: Select the sheet with the Data Validation Right-Click on the sheet tab Select: View Code.....(that will open the VBA editor) Paste this code into the VBA editor: '--------start of code---------- Private Sub Worksheet_Change(ByVal Target As Range) Dim rngParentCell As Range Dim rngDepCell As Range Dim rngCell As Range Set rngParentCell = Range("A1") Set rngDepCell = Intersect(Target, rngParentCell) If Not rngDepCell Is Nothing Then Set rngCell = Range("A2") rngCell.ClearContents rngCell.Select MsgBox _ Title:="Notice", _ Prompt:="You must now select an item from cell A2", _ Buttons:=vbInformation + vbOKOnly Application.SendKeys ("%{DOWN}") End If Set rngParentCell = Nothing Set rngDepCell = Nothing Set rngCell = Nothing End Sub '--------end of code---------- Now try changing the A1 value. The message should pop up. After the user clicks [OK]...A2 will be selected and the list displayed. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Rajat" wrote: In cell A1 i have created a "Drop Down list " using validation which calls data from B1:B4. and in A2 i have created a "drop Down List" using validation which Calls data from D1:D4. And B1 , B2, B3, B4 contains data Like 1, 2, 3, 4 respectively. C1 = A1 cell data then in D1 : D4 cell i have used vlookup function to create a dynamic list i.e. if in C1 data is = 1 the D1:D4 shows 1.1, 1.2, 1.4, 1.4 respectively and if in C1 data is = 2 the D1:D4 shows 2.1, 2.2, 2.4, 2.4 respectively Which then shows up in the list box of A2. The problem i face is say first i selected 1 in cell A1 and the list Box of A2 shows (1.1, 1.2, 1.4, 1.4) and i selected 1.2 but later on i chaged A1 cell value to 2 but but subsequently D1:D4 value chaged to 2.1, 2.2, 2.4, 2.4 respectively but as i entered data in A2 didn't chaged. what i want is if i chaged data in A1 then the the cell A2 value to be made "" (Blank) and a alert message to provide to select data in A2. |
#5
|
|||
|
|||
Thank you for posting very useful code for exactly the problem I was having.
I made one modification because I wanted to cause the A2 cell to simply go blank when a new item from A1 was selected, and show the correct list in A2. Easy. Private Sub Worksheet_Change(ByVal Target As Range) Dim rngParentCell As Range Dim rngDepCell As Range Dim rngCell As Range Set rngParentCell = Range("A1") Set rngDepCell = Intersect(Target, rngParentCell) If Not rngDepCell Is Nothing Then Set rngCell = Range("A2") rngCell.ClearContents End If Set rngParentCell = Nothing Set rngDepCell = Nothing Set rngCell = Nothing End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting 2 colums of numbers and incremening them down | Excel Discussion (Misc queries) | |||
Pull list / dropdown data from another worksheet? | Excel Discussion (Misc queries) | |||
Entering data from dropdown list | Excel Discussion (Misc queries) | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
adding data from one sheet to another sheet as a dropdown list bo. | Excel Discussion (Misc queries) |