Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ATTN : Ron (Multiple Dependent DV List Auto Update)
Dear Ron,
thanx a lot, it worked well, But aother question, i am curious (i might use that in future) to know what modification is to be made in the Code if the DV list are non contagious i.e. if i have DV in Cell A1 then B4 then C2 and A7. expecting your reply, Rajat "Ron Coderre" wrote: Hi, Rajat...sorry for the delay (When your referenced "Ron", I didn't realize that you meant me) The rules (as I understand them) -You have 4 Data Validations (A1:D1) -When one value is changed ---you want the DV cells to the right of it cleared AND ---you want the user prompted to select items from the subsequent DV lists If that's true....Try this vba code: '--------start of code---------- Private Sub Worksheet_Change(ByVal Target As Range) Dim intCellCount As Integer On Error GoTo ErrTrap 'Check if the active cell is one of the Data Validation cells 'But not the last one If Not Intersect(Target, Range("A1:C1")) Is Nothing Then 'Count the DV cells to the right of the Active Cell intCellCount = Range(ActiveCell, "C1").Cells.Count 'Select the cell to the right of the active cell ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Select 'Turn off events so you don't get into and endless loop Application.EnableEvents = False With ActiveCell 'Clear the contents of the subsequent DV cells .Resize(RowSize:=1, ColumnSize:=intCellCount) _ .ClearContents End With 'Alert the user that an item must be selected MsgBox _ Title:="Notice", _ Prompt:="You must now select an item from the next list", _ Buttons:=vbInformation + vbOKOnly Application.SendKeys ("%{DOWN}") End If ErrTrap: 'Turn events back on...so Excel can behave normally Application.EnableEvents = True End Sub '--------end of code---------- Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Rajat" wrote: Hi Ron you gave me this code to Update Dependent DV List automatically i.e. Cell A1 and B1 having DV through DataValidationList When Cell Value of A1 Change following code clear cell B1 content and popup a Msg Box to select value from the list '--------that code is ---------- 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 my problem is 1 have 3 Dependent DV on the sheet i.e. on cell A1 value dependent is DV list of B1, DV list of C1 and DV list of D1 from the above code When A1 value change Msg Box popup and cell Ba selected, but i need when value A1 change msg box appear and cell B1 selected and after selecting value in B1 again msg box pop up and C1 selected and after selecting value in C1 again msg box appear and cell D1 selected. Thans in advance, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro | Excel Discussion (Misc queries) | |||
data validation--multiple dependent list | Excel Discussion (Misc queries) | |||
Dependent List (via Data Validation) Error | Excel Worksheet Functions | |||
Update master list with other lists | Excel Worksheet Functions | |||
Dependent List Query | Excel Worksheet Functions |