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.

1 comment:

  1. Sir i need code for
    When i enter textbox1 as "jan-2016" and textbox2 as "feb-2017"
    then
    from a1 till the end

    ReplyDelete