Excel Discount Calculation On Userform With VBA Class

The Discounted Price Calculation With VBA Class Module

           
           On the userform,the price of the product and discounted price is calculated as automatically  with text boxes.VBA class was used for calculation.

Following formula is used  to calculate the discount :

Discounted Price  =  Quantity * Price (Quantity * Price /100) * Discount  

If you wish calculations and processes can be saved from the userform to sheet.


» As seen , calculations are made automatically on the userform without pressing any button.

Codes of class module:
Public WithEvents txBox As MSForms.TextBox

Private Sub txBox_Change()
Dim miktar As Double
Dim birim As Double
Dim iskonto As Double
Dim sonuc As Double
Dim toplam As Double

If txBox.Tag = "toplam" Or txBox.Tag Like "sonuc*" Then Exit Sub
On Error Resume Next
If Not txBox.Tag Like "sonuc*" Then
IdNr = Right(txBox.Tag, 1)
miktar = 0: birim = 0: iskonto = 0

For Each textbx In UserForm1.Controls
If textbx.Tag = "miktar" & IdNr Then miktar = textbx.Text
If textbx.Tag = "birim" & IdNr Then birim = textbx.Text
If textbx.Tag = "iskonto" & IdNr Then iskonto = textbx.Text
Next

sonuc = miktar * birim - (miktar * birim / 100) * iskonto 'Discount Formula

For Each textbx In UserForm1.Controls
If textbx.Tag = "sonuc" & IdNr Then textbx.Text = VBA.Format(sonuc, "#,##.00")
Next

For Each textbx In UserForm1.Controls
If textbx.Tag Like "sonuc*" Then toplam = toplam + textbx
Next

For Each textbx In UserForm1.Controls
If textbx.Tag Like "toplam" Then textbx.Text = VBA.Format(toplam, "#,##.00")
Next
End If
End Sub

We provided the connection between userform and class with the following codes in Userform_Initialize procedure:
Dim cls As Class1
Set siniflar = New Collection
For Each textbx In Me.Controls
If TypeName(textbx) = "TextBox" Then
Set cls = New Class1
Set cls.txBox = textbx
siniflar.Add cls
Set cls = Nothing
End If
Next


No comments:

Post a Comment