Dropdown lists in excel 2007

Solved/Closed
Gouws
Posts
45
Registration date
Sunday February 7, 2010
Status
Member
Last seen
April 15, 2012
- Feb 19, 2010 at 12:28 AM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
- Feb 27, 2013 at 05:56 PM
Hello,
I want to create dropdown lists in excel, it should work as follow:In cell2,1 must be a drop down list with 4 names (e.g.Mill,Comp,Loads,Blending), if I select say Mill then in cell2,2 a dropdown list with all the mill parts must appear, same for the other if I select one of the other names their parts list must appear.Can anyone show me how to do this?

TX
G

6 replies

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Feb 19, 2010 at 04:57 AM
Assumptions
1. You are not going to sue me it any thing goes wrong
2. You have a backup copy of the file


This is how I would approach
1. On a new sheet (let say sheet2) in Cells A1, B1, C1 and D1 enter values Mill, Comp, Loads, Blending respectively
2. Put the values of Mill, Comp, Loads and Blending under its respective column heading (make sure no blank line)
3. Go to Formula Tab on menu
4. Select Cells A1:D1
5. Click on define Name
6. Enter the name, lets say "MyMainList" and click Ok
7. Select all the values in col A, starting from A2 and click on name, give it name "Mill"
8. Select all the values in col B, starting from B2 and click on name, give it name "Comp"
9. Select all the values in col C, starting from C2 and click on name, give it name "Loads"
10. Select all the values in col D, starting from D2 and click on name, give it name "Blending "
11. Go to main sheet (lets say sheet1) and select cell A2
12. Go to Data tab on menu and click on Data Validation
13. Click on the drop down titled "Allow" and choose List
14. In Source box write =MyMainList
15. Optionally go to input tab and error tab to enter messages for that
16. Press ALT+F11
17. Double click on "sheet1" in the project explorer
18. Paste code.

Private Sub Worksheet_Change(ByVal Target As Range) 
'Target is the cell or a range of cells whose values were changed
' and that triggered the change. Excel itself will handles this part.
' This code is written with assumption that at a time only
' one cell would be changed

    ' if the changed column is not column A, then we are exiting
    If Target.Column <> 1 Then Exit Sub 

    ' we are disabling excel ability to react when the cell or range is 
    ' changed. It will be enabled later on. This prevents cyclic or
    ' recursive calls     
    Application.EnableEvents = False 
     
    Dim mainsrc As String 
    Dim subsrc As String 
     
    Dim lThisRow As Long 
    ' find which row was modifed that triggered this function to be called
    lThisRow = Target.Row 
    
    ' Check if the value in column A on the row that was changed is ""
    If Cells(lThisRow, 1) = "" Then 

        ' since the value in column A is blank, we are deleting any 
        ' validation from Column B on the same row
        Cells(lThisRow, 2).Validation.Delete 

        ' goto to a label "exit_sub"
        GoTo exit_sub 
    End If 
     

    mainsrc = Cells(lThisRow, 1).Value 
     
    On Error Resume Next 
    'attempting to read what is the current validation formula in cell in
   ' column B of the changed row
    subsrc = Cells(lThisRow, 2).Validation.Formula1 
    On Error GoTo 0 
    
    ' check if the current validation in the column B is same as one that
    ' is needed now due to change in the value of column 1
    If ("=" & subsrc = mainsrc) Then 
        ' the validation I n column B is for same major category
        ' no need to reset validation
        GoTo exit_sub 
    End If 
  
  ' since the major category was changed, we reset any selected value
  ' in column B of the changed row to ""    
  Cells(lThisRow, 2) = "" 

  ' putting the new validation in B
  With Cells(lThisRow, 2).Validation 
        .Delete 
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & mainsrc 
        .IgnoreBlank = True 
        .InCellDropdown = True 
        .InputTitle = "This is my Input Title" 
        .ErrorTitle = "Oops Error" 
        .InputMessage = "Select a Value" 
        .ErrorMessage = "Not a valid value" 
        .ShowInput = True 
        .ShowError = True 
    End With 
     

' a label to allow for "Goto" statement
exit_sub: 
     
    ' re-enabling the ability for excel to react to events/triggers
    Application.EnableEvents = True 

End Sub

19. Post Feedback
20. Send me the money :p
9
Hi rivisa1, thank it's working
G
0
Cool, I thought this is not possible; it could be easily using Ajax!! But you have done here just like that! ;-) Many Thanks rizvisa
0
is there any chance to make the above scenario without macro?
0
This is really helpful. Hope to see more info.
0
i want to add a name to the drop down list. How do I do that? please help
0
try this one, in cell a1,a2,a3,a4,a5-type anything you want.click D2 CELL down to the CELL that you want to have a drop-down list.click DATA in the tool bar menu, find Data Validation, it will show you the drop-down list pane, click on the Drop- Down List, select LIST. take out the check in Ignore blank then click Source, highlight the item that you'd typed in A CELLS A. Click enter then ok.
3
SARC
Posts
29
Registration date
Saturday August 7, 2010
Status
Member
Last seen
August 31, 2010

Aug 19, 2010 at 02:55 AM
wow this is awesome Rizvisa
0
Hi Rzvisa,,...

How would you modify this if you wanted the validation list "MyMainList" in G6 ??
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Aug 23, 2010 at 07:50 AM
I have added comments in the code. read the code and the comments
0
Hi,,..

Thanks Rizvisa... you are a life saver.... the comments really helped me understand the programme.. Thanks a lot... one more thing pls... what do these lines stand for??
Dim mainsrc As String 
    Dim subsrc As String 


are they main source and sub source?


Thanks once again.. :)
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Aug 23, 2010 at 01:58 PM
Dim is short for dimension and is way you declare a variable

Dim mainsrc As String
is only saying, there will be a variable called mainsrc and it will contain data type sting.

Dim subsrc As String
is same as main. Only we are using it to capture the current validation formula in cell B


They are not the main or sub source. It is just a variable. this value is what is the major selection. and what ever is selected must correspond to one of the data ranges that was defiend in step 7 -10 (mill. blend etc). Hence what ever user selects, in cell A, we know what data range is to be used for validation in cell B
0
ok,,.. thank you for your explanations,,.. helped me a lot,,.. thanks..................
0
Hi,,..

What if I wanted this to work on just 1 cell in a column?? now if I change the anything else in column A the validation list doesnt work.. could you tell me pls how to make this so that the validation lists will apear only on cells A2 & B2??

Thanks
0

Didn't find the answer you are looking for?

Ask a question
SARC
Posts
29
Registration date
Saturday August 7, 2010
Status
Member
Last seen
August 31, 2010

Aug 31, 2010 at 10:30 AM
how can I upload a workbook?/
0
SARC
Posts
29
Registration date
Saturday August 7, 2010
Status
Member
Last seen
August 31, 2010

Aug 31, 2010 at 10:35 AM
ok I guess I have to open a new thread.. i've opened a new thread named validation lists in VBA... but how can I upload a file?
0
SARC
Posts
29
Registration date
Saturday August 7, 2010
Status
Member
Last seen
August 31, 2010

Aug 31, 2010 at 10:39 AM
OK I found the problem rizviza..... it was because I had entered values in other cells in the same row as the validation...

Thanks for all your help.. Cheers.....
0
Rizviza,

I have a similar query, in that I am creating 2 drop down lists, The first drop down list (Column A) is for customer type, and based on what is selected from the Column A drop down list, brings back values of all Customers under that customer type, which appear in the drop down list in column B.

Due to the length of values that come back in column B, is it possible to enter say 'F' and for only values begining with F to show? If it requires visual basic, my knowledge is Zero, so I would need very basic steps to take me through this!

Thanks.
0