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.

Sunday, March 5, 2017

Creating Invoice And Entering Data Quickly Through Userforms


           In this invoice template ,the userforms that contains textbox,listbox and buttons are used to enter quickly data of customers and products:
- Requested data can be searched in lists(product list or customer list) through the text boxes . Then,     when the list item is double-clicked or when the enter key is pressed, the item's data is entered in       the sheet.
- The created macro gives warning using cell blink (flashing cell method) when product quantity not    entered to quantity range. -It can be seen in the above video-
- Addition and multiplication operations in column G are performed through formulas (=E21*F21,      =SUM(G21:G35) etc.) The used formulas in worksheet are protected by the following codes :
      Private Sub Worksheet_Change(ByVal Target As Range)
     If Target.Address = "$G$21" Then
      Target.Formula = "=E21*F21"
      ElseIf Target.Address = "$G$22" Then
      Target.Formula = "=E22*F22"
       ElseIf Target.Address = "$G$23" Then
      Target.Formula = "=E23*F23"
       ElseIf Target.Address = "$G$24" Then
      Target.Formula = "=E24*F24"
       ElseIf Target.Address = "$G$25" Then
      Target.Formula = "=E25*F25"
       ElseIf Target.Address = "$G$26" Then
      Target.Formula = "=E26*F26"
       ElseIf Target.Address = "$G$27" Then
      Target.Formula = "=E27*F27"
       ElseIf Target.Address = "$G$28" Then
      Target.Formula = "=E28*F28"
       ElseIf Target.Address = "$G$29" Then
      Target.Formula = "=E29*F292"
       ElseIf Target.Address = "$G$30" Then
      Target.Formula = "=E30*F30"
       ElseIf Target.Address = "$G$31" Then
      Target.Formula = "=E31*F31"
       ElseIf Target.Address = "$G$32" Then
      Target.Formula = "=E32*F32"
       ElseIf Target.Address = "$G$33" Then
      Target.Formula = "=E33*F33"
       ElseIf Target.Address = "$G$34" Then
      Target.Formula = "=E34*F34"
       ElseIf Target.Address = "$G$35" Then
      Target.Formula = "=E35*F35"
       ElseIf Target.Address = "$G$36" Then
      Target.Formula = "=SUM(G21:G35)"
       ElseIf Target.Address = "$G$41" Then
      Target.Formula = "=SUM(G36:G39)"
      End If
      End Sub

- The created invoice can be copied to selected record sheet.

Wednesday, January 25, 2017

Excel Vba Games

Fun Games Created With Excel VBA Codes

There are three games in three separate workboks:
  1. Tetris
  2. Find Matches
  3. Rocket