mismatch this code for add items to listbox in userfrom

[MohamedAmin]

Private Sub CmdAddItem_Click() 'add item
ThisWorkbook.Activate
'====check product list =====
If Me.CbProductList.MatchFound = False Then: MsgBox "Data Error : Please select of items list ", vbCritical, "Inventory Program ": Me.CbProductList.SetFocus: Exit Sub

'===== check q as number====
If Not IsNumeric(Me.TbQuantity.Value) Then: MsgBox "Data Error: not have quantity: Please enter the quantity ", vbCritical, "Inventory Program": Me.TbQuantity.SetFocus: Exit Sub

'====== check raseed =======
If Not Me.CbInvStore.Value = Data.Range("BF7") Then GoTo 11
If Val(Me.TbQuantity.Value) + Val(Me.TbShipQuantity.Value) > Val(Me.TbRaseedNow.Value) Then
confir = MsgBox(" Quantity available is not sufficient warehouse for sale. please check inventory " & vbCrLf & "Do you want to continue ?", _
vbYesNo + vbMsgBoxLeft, "Inventory Programs ")
If confir = vbNo Then: Me.TbQuantity.Value = "": Me.TbQuantity.SetFocus: Exit Sub
End If
'===========================

11 np = Val(Me.TbNetPrice.Value) * Val(Me.TbQuantity.Value) * (Val(Me.TbMohDiscount.Value) / 100)

'On Error Resume Next
With ListBox1
Dim i As Integer
Dim b
i = .ListCount
.AddItem i
.List(i, 0) = Me.TbCode.Value
.List(i, 1) = Me.CbProductList.Value
.List(i, 2) = Me.TbProductType.Value
.List(i, 3) = Me.TbQuantity.Value
.List(i, 4) = Format(Me.TbNetPrice.Value, "0.00")
.List(i, 5) = Val(Me.TbQuantity.Value) * Val(Me.TbNetPrice.Value)
.List(i, 7) = Val(Me.TbQuantity.Value) * Val(Me.TbShipQuantity.Value)
.List(i, 8) = Me.TbProductPrice.Value

If Me.CbInvStore = "Purchase" Then
.List(i, 8) = Val(Me.TbNetPrice.Value) + Val(Me.TbShipQuantity.Value)
End If

If Not IsNumeric(Me.TbMohDiscount.Value) Then GoTo 1
.List(i, 6) = Format(Me.TbMohDiscount.Value / 100, "0.00%")

'b = 1 + (Me.TbMohDiscount / 100)

1 If b <= 0 Or b = Empty Then
.List(i, 9) = np
Else
.List(i, 9) = np / b
End If

.List(i, 9) = Val(.List(i, 5)) - np + Val(.List(i, 7))

If Me.CbInvStore = "Sales" Then
.List(i, 10) = Me.TbProductPrice.Value * Me.TbQuantity.Value (Here is the Mismatch )
.List(i, 11) = Val(.List(i, 9)) - np - Val(.List(i, 10))
End If

.List(i, 4) = Format(.List(i, 4), "$#,##0.00")
.List(i, 5) = Format(.List(i, 5), "$#,##0.00")
.List(i, 7) = Format(.List(i, 7), "$#,##0.00")
.List(i, 8) = Format(.List(i, 8), "$#,##0.00")
.List(i, 9) = Format(.List(i, 9), "$#,##0.00") 'net price
.List(i, 10) = Format(.List(i, 10), "$#,##0.00")
.List(i, 11) = Format(.List(i, 11), "$#,##0.00")

'.List(i, 10) = Val(.List(i, 3)) * Val(.List(i, 9)) 'q only * net price = total net price
'.List(i, 11) = Val(.List(i, 8)) * Val(Me.TbNetPrice.Value) 'all q+b * net price = total puplic price
'.List(i, 10) = Format(.List(i, 10), "#,##0.00")
'.List(i, 11) = Format(.List(i, 11), "#,##0.00")
.Selected(1) = True
End With

'For Total
Dim sum As Double
For Row = 1 To ListBox1.ListCount - 1
sum = sum + ListBox1.List(Row, 9) (Here is the Mismatch )
Next Row
Me.TbTotalNetPrice = sum
Me.TbTotalNetPrice = Format(Me.TbTotalNetPrice, "$#,##0.00")

'For Cost
If Me.CbInvStore = "Sales" Then
Dim sumc As Double
For Row = 1 To ListBox1.ListCount - 1
sumc = sumc + ListBox1.List(Row, 10) (Here is the Mismatch )
Next Row
Me.TbInvoCost = sumc
Me.TbInvoCost = Format(Me.TbInvoCost, "$#,##0.00")

'For Profit
Dim sump As Double
For Row = 1 To ListBox1.ListCount - 1
sump = sump + ListBox1.List(Row, 11) (Here is the Mismatch )
Next Row
Me.TbProfit = sump
Me.TbProfit = Format(Me.TbProfit, "$#,##0.00")
Me.TbProfit = Format(sum - sumc, "$#,##0.00")

End If
Me.TbCode = ""
Me.TbQuantity = ""
Me.TbBonusNet = ""
Me.TbBonusPercent = ""

Set TotalS = Profit.Range("Q8")
Me.TotalSales = Format(TotalS.Value + Me.TbTotalNetPrice.Value, "$#,##0.00")

Set Totalc = Profit.Range("R8")
Me.TotalCost = Format(Totalc.Value + Me.TbInvoCost.Value, "$#,##0.00")

Set Totalp = Profit.Range("S8")
Me.TotalProfit = Format(Totalp.Value + Me.TbProfit.Value, "$#,##0.00")

Me.LbPercentage.Value = Format(TotalProfit.Value / TotalSales.Value, "0.00%")


Me.TbCode.SetFocus


End Sub

 

[Dan_W]

I won't be able to run it, I'm sorry, so we're going to have to try and solve this together.
Did you try my suggestion above: mismatch this code for add items to listbox in userfrom

 

[MohamedAmin]

I won't be able to run it, I'm sorry, so we're going to have to try and solve this together.
Did you try my suggestion above: mismatch this code for add items to listbox in userfrom

This one?

Debug.Print TypeName(ListBox1.List(Row, 9)

 

[MohamedAmin]

I tried to stop this format then after I stop them it works

'.List(i, 9) = Format(.List(i, 9), "$#,##0.00")      
'.List(i, 10) = Format(.List(i, 10), "$#,##0.00")
'.List(i, 11) = Format(.List(i, 11), "$#,##0.00")

 

[MohamedAmin]

I tried to stop this format then after I stop them it works

'.List(i, 9) = Format(.List(i, 9), "$#,##0.00")     
'.List(i, 10) = Format(.List(i, 10), "$#,##0.00")
'.List(i, 11) = Format(.List(i, 11), "$#,##0.00")

But after that giving me another error

 

[Dan_W]

I tried to stop this format then after I stop them it works

'.List(i, 9) = Format(.List(i, 9), "$#,##0.00")     
'.List(i, 10) = Format(.List(i, 10), "$#,##0.00")
'.List(i, 11) = Format(.List(i, 11), "$#,##0.00")

I thought you said the error occured on this line:

sum = sum + ListBox1.List(Row, 9)

I'm guessing that your code is telling VBA to do something with whatever is in column 9 (10 and 11) that isn't possible. It might be that there is no data there at all, or perhaps it's not something that can be formatted as a number. If you can find out what it is, then maybe we can solve it.

 

[Dan_W]

But after that giving me another error

Ok - well there we go - that tells you the problem - there isn't anything in (now) column 10...

 

[MohamedAmin]

I thought you said the error occured on this line:

sum = sum + ListBox1.List(Row, 9)

I'm guessing that your code is telling VBA to do something with whatever is in column 9 (10 and 11) that isn't possible. It might be that there is no data there at all, or perhaps it's not something that can be formatted as a number. If you can find out what it is, then maybe we can solve it.

Sum will calculating all the amount numbers in column 9

 

[MohamedAmin]

Yes-ish. How did you go?

Can you tell me if any wrong with this code? If I do Me.TbProductPrice.Value * Me.TbQuantity.Value giving me a mismatch also if I do.List(i, 10) = Me.TbProductPrice.Value or .List(i, 10) = Me.TbQuantity.Value gives me the result but if + or - giving me wrong amount

.List(i, 10) = Me.TbProductPrice.Value * Me.TbQuantity.Value

 

[Dan_W]

It's very likely that there is nothing wrong with the code - the problem may have something to do with the data, or how you're referencing it.
When the error occurs, please press Debug. Then in the Immediate Window, please execute the following commands:

Debug.Print i, Listbox1.ListCount, ListBox1.ColumnCount

and then

Debug.Print Me.TbProductPrice.Value, Me.TbQuantity.Value

and please let me know what the output for each is. Thank you.

 

[MohamedAmin]

It's very likely that there is nothing wrong with the code - the problem may have something to do with the data, or how you're referencing it.
When the error occurs, please press Debug. Then in the Immediate Window, please execute the following commands:

Debug.Print i, Listbox1.ListCount, ListBox1.ColumnCount

and then

Debug.Print Me.TbProductPrice.Value, Me.TbQuantity.Value

and please let me know what the output for each is. Thank you.

When running
Debug.Print i, Listbox1.ListCount, ListBox1.ColumnCount

There is a "Runtime Error '424' Object Required"

As for
Debug.Print Me.TbProductPrice.Value * Me.TbQuantity.Value

I would have to run the userform for it to give me the information, otherwise the values are empty by default.