SOLVED
Home

Multiple drop down list

%3CLINGO-SUB%20id%3D%22lingo-sub-1260727%22%20slang%3D%22en-US%22%3EMultiple%20drop%20down%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1260727%22%20slang%3D%22en-US%22%3E%3CP%3EWhen%20I%20select%20AMR%20it%20shows%20me%20the%20list%20of%20names%20for%20AMR%2C%20after%20selecting%20one%20terminal%2C%20next%20if%20I%20choose%20ANZ%2C%20till%20I%20again%20go%20to%20the%20sub%20selection%20to%20select%20the%20list%20of%20names%20from%20the%20second%20selector%2C%20it%20still%20shows%20the%20name%20from%20AMR.%26nbsp%3B%20Is%20there%20a%20way%20that%20when%20I%20change%20AMR%20to%20ANZ%2C%20the%20secondary%20selector%20goes%20blank%20or%20chooses%20the%20first%20name%20from%20ANZ%3F%26nbsp%3B%20Thanks%20in%20advance%20for%20your%20help.%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222020-03-27_12-23-17.jpg%22%20style%3D%22width%3A%20815px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F180113i9455D4573D5B39EF%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%222020-03-27_12-23-17.jpg%22%20alt%3D%222020-03-27_12-23-17.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1260727%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1260860%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20drop%20down%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1260860%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F598822%22%20target%3D%22_blank%22%3E%40sure19%3C%2FA%3E%26nbsp%3BYou%20can%20put%20this%20little%20macro%20%3CU%3E%3CFONT%20color%3D%22%23FF0000%22%3Ein%20the%20code%20area%20for%20the%20worksheet%3C%2FFONT%3E%3C%2FU%3E%20(i.e.%20NOT%20in%20a%20separate%20module!!).%20It%20detects%20a%20change%20in%20cell%20D1%20and%20blanks%20cell%20E1.%3C%2FP%3E%3CP%3EYou%20workbook%20then%20has%20to%20be%20saved%20as%20a%20macro-enabled%20one%20(.xlsm).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%20%20%20%20If%20Target.Column%20%3D%204%20And%20Target.Row%20%3D%201%20Then%0A%20%20%20%20%0A%20%20%20%20%20%20%20%20Range(%22E1%22)%20%3D%20%22%22%0A%20%20%20%20%20%20%20%20%20%0A%20%20%20%20End%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EYou%20can%20read%20more%20about%20it%20here%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Fvba%2Fapi%2FExcel.Worksheet.Change%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Fvba%2Fapi%2FExcel.Worksheet.Change%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1277618%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20drop%20down%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1277618%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%20i%20did%20that%20and%20it%20worked.%26nbsp%3B%20Thanks%20very%20much%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1277646%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20drop%20down%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1277646%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F598822%22%20target%3D%22_blank%22%3E%40sure19%3C%2FA%3E%26nbsp%3BGreat!%20You're%20welcome.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

When I select AMR it shows me the list of names for AMR, after selecting one terminal, next if I choose ANZ, till I again go to the sub selection to select the list of names from the second selector, it still shows the name from AMR.  Is there a way that when I change AMR to ANZ, the secondary selector goes blank or chooses the first name from ANZ?  Thanks in advance for your help.2020-03-27_12-23-17.jpg

3 Replies
Highlighted
Solution

@sure19 You can put this little macro in the code area for the worksheet (i.e. NOT in a separate module!!). It detects a change in cell D1 and blanks cell E1.

You workbook then has to be saved as a macro-enabled one (.xlsm).

 

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 4 And Target.Row = 1 Then
    
        Range("E1") = ""
         
    End If
End Sub

You can read more about it here: 

https://docs.microsoft.com/en-us/office/vba/api/Excel.Worksheet.Change 

Highlighted

@Riny_van_Eekelen  i did that and it worked.  Thanks very much

Highlighted

@sure19 Great! You're welcome.