How can i

[God33]

I asked a question a while ago about making a list of products and then adding sizes that give a price based on a square metre rate.
I have been using this for a while but want to know if I can do this but slightly different.

In column A I put the product but on a drop down list.
In B we put a width
in C we put the height
In D we have the net price
E we have the vat
F the total.

Is there a way this can be done as a template so once everything has been input we can just print off as a quote?
Thanks

 

[alansidman]

If the products are unique and this data is stored in a separate worksheet, you can employ the Vlookup function to fill in the data in columns B, C, and D. Then use a calculation for E and F.

 

[Logit]

Here is a small project that does what you are seeking. I picked this up off the 'Net and cannot claim credit.

'For More : http://merkez-ihayat.blogspot.com


Option Compare Text
Dim tablo2(), tablo3(), Category(), Supplier(), Product(), Code(), SD As Object, bul As String, c As Variant, i As Long


Private Sub UserForm_Initialize()
    Supplier = Application.Transpose(Range("Supplier"))
    Category = Application.Transpose(Range("Category"))
    Product = Application.Transpose(Range("Product"))
    Code = Application.Transpose(Range("Code"))
    Set SD = CreateObject("Scripting.Dictionary")
    For Each x In Supplier
        SD(x) = ""
    Next x
    ComboBox1.List = SD.keys
   End Sub
Private Sub ComboBox1_Change()
Dim a, b As Long, k As Variant


    If ComboBox1.ListIndex = -1 And IsError(Application.Match(ComboBox1, Supplier, 0)) Then
        Set SD = CreateObject("Scripting.Dictionary")
        bul = ComboBox1 & "*"
        For Each c In Supplier:
            If c Like bul Then SD(c) = ""
        Next c
        ComboBox1.List = SD.keys
        ComboBox1.DropDown
            Else
        Evn = ComboBox1
        If Evn = "" Then Exit Sub
        Set d2 = CreateObject("Scripting.Dictionary")
        For i = LBound(Category) To UBound(Category)
            If Supplier(i) = Evn Then d2(Category(i)) = ""
        Next i
        tablo2 = d2.keys
        
        ComboBox2.List = tablo2
        'For alphabetic order
        For a = 0 To ComboBox2.ListCount - 1
  For b = a To ComboBox2.ListCount - 1
        If ComboBox2.List(b) < ComboBox2.List(a) Then
k = ComboBox2.List(a)
    ComboBox2.List(a) = ComboBox2.List(b)
    ComboBox2.List(b) = k
       End If
  Next
  Next
        
        ComboBox2.SetFocus
        If Val(Application.Version) > 10 Then SendKeys "{f4}"
        ComboBox1.BackColor = &H80FFFF
    End If
End Sub
Private Sub ComboBox2_Change()
    If ComboBox1 <> "" Then
        If ComboBox2.ListIndex = -1 And IsError(Application.Match(ComboBox2, Category, 0)) Then
            Set SD = CreateObject("Scripting.Dictionary")
            bul = UCase(ComboBox2) & "*"
            For Each c In tablo2
                If UCase(c) Like bul Then SD(c) = ""
            Next c
            ComboBox2.List = SD.keys
            ComboBox2.DropDown
                Else
            Set d3 = CreateObject("Scripting.Dictionary")
            ara_1 = ComboBox1
            ara_2 = ComboBox2
            If ara_1 = "" Or ara_2 = "" Then Exit Sub
            Set d3 = CreateObject("Scripting.Dictionary")
            For i = LBound(Product) To UBound(Product)
                If Supplier(i) = ara_1 And Category(i) = ara_2 Then d3(Product(i)) = ""
            Next i
            tablo3 = d3.keys
            ComboBox3.List = tablo3
            ComboBox3.SetFocus
            If Val(Application.Version) > 10 Then SendKeys "{f4}"
        End If
        ComboBox2.BackColor = &H80FFFF
     End If
End Sub
Private Sub ComboBox3_Change()
    If ComboBox1 <> "" And ComboBox2 <> "" Then
        If ComboBox3.ListIndex = -1 And IsError(Application.Match(ComboBox3, Product, 0)) Then
            Set SD = CreateObject("Scripting.Dictionary")
            bul = UCase(ComboBox3) & "*"
            For Each c In tablo3
                If c Like bul Then SD(c) = ""
            Next c
            ComboBox3.List = SD.keys
            ComboBox3.DropDown
               Else
            ara_1 = ComboBox1.Text
            ara_2 = ComboBox2.Text
            ara_3 = ComboBox3.Value
            For i = LBound(Product) To UBound(Product)
                If Supplier(i) = ara_1 And Category(i) = ara_2 And Product(i) = CStr(ara_3) Then
                  TextBox1.Value = Format(Code(i), "#,##0.00")
                End If
            Next i
         End If
         ComboBox3.BackColor = &H80FFFF
     End If
End Sub


Private Sub CommandButton1_Click()
    If ComboBox1 <> "" And ComboBox2 <> "" Then
        ActiveCell = UCase(ComboBox1)
        ActiveCell.Offset(, 2) = ComboBox2
        ActiveCell.Offset(, 1) = ComboBox3
        ActiveCell.Offset(, 4) = TextBox1
        ActiveCell.Offset(, 4) = ActiveCell.Offset(, 4) * 1
        Unload Me
            Else
        MsgBox "Eksik!"
        Exit Sub
    End If
End Sub

Download example : https://www.amazon.com/clouddrive/share/32J1qNyVtMvFjpf7c10ZZpamnNUDSO3k2ZPinD2UXPY

 

[God33]

Thanks. This is the sort of thing. I will play about with it and see if it works.

 

[Logit]

You are welcome.