VBA Code:
Private Sub Add_button_Product_Click()
'''''''''''''Validation'''''''''''''
If Me.txt_ProductDescription.Value = "" Then
MsgBox "Please Enter Product Name", vbCritical
Exit Sub
End If
If IsNumeric(Me.txt_QTY) = False Then
MsgBox "Please Enter The Correct QTY", vbCritical
Exit Sub
End If
If IsNumeric(Me.Txt_UnitCost) = False Then
MsgBox "Please Enter The Correct Unit Cost Price", vbCritical
Exit Sub
End If
If IsNumeric(Me.txt_RetailPrice) = False Then
MsgBox "Please Enter The Correct Retail Price", vbCritical
Exit Sub
End If
If IsNumeric(Me.txt_UnitCost_CS) = False Then
MsgBox "Please Enter The Correct Unit Cost per Case", vbCritical
Exit Sub
End If
If IsNumeric(Me.txt_WSPrice_CS) = False Then
MsgBox "Please Enter The Correct Wholesale Price per case", vbCritical
Exit Sub
End If
If IsNumeric(Me.Txt_Frieght) = False Then
MsgBox "Please Enter The Correct Frieght", vbCritical
Exit Sub
End If
''''''' Check Duplicate
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Master List")
If Application.WorksheetFunction.CountIf(sh.Range("B:B"), Me.txt_ProductDescription.Value) > 0 Then
MsgBox "This Product is already available in Master List", vbCritical
Exit Sub
End If
'''''''''''''''''' Add Data
Dim Lr As Integer
Lr = Application.WorksheetFunction.CountA(sh.Range("A:A"))
sh.Range("A" & Lr + 1).Value = Lr
sh.Range("B" & Lr + 1).Value = Me.Txt_stocknumber.Value
sh.Range("d" & Lr + 1).Value = Me.Txt_packing.Value
sh.Range("e" & Lr + 1).Value = Me.txt_QTY.Value
sh.Range("f" & Lr + 1).Value = Me.Txt_Unit.Value
sh.Range("g" & Lr + 1).Value = Me.Txt_UnitCost.Value
sh.Range("h" & Lr + 1).Value = Me.txt_UnitCost_CS.Value
sh.Range("i" & Lr + 1).Value = Me.txt_RetailPrice.Value
sh.Range("j" & Lr + 1).Value = Me.txt_WSPrice_CS.Value
sh.Range("k" & Lr + 1).Value = Me.Txt_Frieght.Value
sh.Range("l" & Lr + 1).Value = Me.txt_Percentage.Value
''''''''''''' Clear Boxes
Me.Txt_stocknumber.Value = ""
Me.Txt_packing.Value = ""
Me.txt_QTY.Value = ""
Me.Txt_Unit.Value = ""
Me.Txt_UnitCost.Value = ""
Me.txt_UnitCost_CS.Value = ""
Me.txt_RetailPrice.Value = ""
Me.txt_WSPrice_CS.Value = ""
Me.Txt_Frieght.Value = ""
Me.txt_Percentage.Value = ""
Call show_data
MsgBox "Product has beed added in Master List", vbInformation
End Sub
Sub show_data()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Master List")
Dim Lr As Integer
Lr = Application.WorksheetFunction.CountA(sh.Range("A:A"))
If Lr = 1 Then Lr = 2
With Me.ListBox1
.ColumnCount = 13
.ColumnHeads = True
.ColumnWidths = "60,200,100,0,150,50,50,100,100,100,100,20,10"
.RowSource = "Master List!A2:M" & Lr
End With
End Sub
Private Sub UserForm_Activate()
Call show_data
End Sub
M trying to create this vba.. but in my show data () in .rowsource shows debug and error pls help.. and can you help me also for multiple products encoding? thanks