Saturday, April 22, 2017

Creating Price Quote With Userform That Contains Cascading Drop-Down Lists


        In this study, we created a userform that automatically displayed to make it easier to enter data into the worksheet. The userform contains dependent combo boxes( combobox in which the list depends on the selection made in another combobox),textbox and button.


The Scripting Dictionary Object was used to fill in combo boxes :
Dim SD As Object
Set SD = CreateObject(“Scripting.Dictionary”)
For Each x In Supplier
SD(x) = “”
Next x
ComboBox1.List = SD.keys
excel price quote

Thursday, April 6, 2017

Create Simple Dynamic(Dependent) Drop Down Lists In Excel


       In worksheet, we can create dynamic drop down lists with the Data Validation feature and the Indirect function. 

In sample sheet,we have a table of five columns that indicate five types of foodstuff: fruit, food, meat,vegetable and drink and below them are the specific food name :
       We need to create one drop down list that contains the foodstuff, such as fruit, food, vegetable,meat and drink . The second drop-down would have the specific food name. If we select meat item from first drop-down, the second drop-down will show beef, mutton, chicken, port, fish and veal.
To do this, please apply the following steps:
First, we need to create range names for these columns and the first categories row.
- Let's create a range name for the categories,for this the first row, we selected the A1:E1, and typed the range name Foodstuff into the Name Box, then pressed Enter key.
- Then we need to name the range for each of the columns  as shown below:
excel dependent drop down list
- Now we can create the first drop down list, we selected a blank cell or a column that we want to apply this drop down list (I5 cell is selected), and then we clicked Data > Data Validation .In the Data Validation dialog box, we clicked Settings tab, we chose List from the Allow drop down list, and entered this formula =Foodstuff into the Source box.
Our first drop down list have been created.
- Then we can create the second drop down list,we selected J5 cell, and click Data > Data Validation again, in the Data Validation dialog box, we clicked Settings tab, we chose List from the Allow drop down list, and entered this formula =indirect($I$5) into the Source box.

Our dependent drop down list have been created successfully.
If user choose one type of the foodstuff, the corresponding cell will only display its specific food name.