0

I have an inventory to manage the stock in the lab with 2 sections: one related to the Order and one related to the used parts, but I have a couple of issues:

  1. In the Userform that I designed to track the order and the used parts, the operation on the last cell that is managing the TOTAL by multiplication with quantity and the price of the single part NOT showing me the decimal separator.

    UserForm with decimal separator issue

    Here the code for one text box to manage the quantity and have the total in the last text box:

    Private Sub cmdReceiving_Click()    
    Dim X As Integer
    Dim nextrow As Range
    Dim DateCus As Range

    On Error GoTo cmdReceiving_Click_Error

    Me.Arec6.Value = Format(Me.Arec6.Value, "€##,###.00")
    Me.Brec6.Value = Format(Me.Brec6.Value, "€##,###.00")
    Me.Crec6.Value = Format(Me.Crec6.Value, "€##,###.00")
    Me.Drec6.Value = Format(Me.Drec6.Value, "€##,###.00")
    Me.Erec6.Value = Format(Me.Erec6.Value, "€##,###.00")
    Me.Frec6.Value = Format(Me.Frec6.Value, "€##,###.00")
    Me.Grec6.Value = Format(Me.Grec6.Value, "€##,###.00")
    Me.Hrec6.Value = Format(Me.Hrec6.Value, "€##,###.00")
    Me.Irec6.Value = Format(Me.Irec6.Value, "€##,###.00")
    Me.Jrec6.Value = Format(Me.Jrec6.Value, "€##,###.00")
    Me.Krec6.Value = Format(Me.Krec6.Value, "€##,###.00")

    The following part of code is referred to Arec3 that is the quantity cell (Qty) in the UserForm. This is going to read in an inventory sheet and get the data from it doing the math

    Private Sub Arec3_Change()
    On Error Resume Next
    Me.Arec2.RowSource = ""
    Me.Arec4 = Application.WorksheetFunction.VLookup(Me.Arec2, Sheet5.Range("Data"), 2, 0)
    Me.Arec5 = Application.WorksheetFunction.VLookup(Me.Arec2, Sheet5.Range("Data"), 3, 0)
    If Me.Arec3.Value > "" Then Me.Arec6 = Me.Arec3.Value * Me.Arec5.Value

    On Error GoTo 0

End Sub

  2. The other issue is about the formatting in the Excel sheet regarding the stock. Once I filled up the UserForm with my info, all these info are stored in an excel sheet but the € column related to the single cost and the Total are always giving me not proper formatting. Even if I select from File->Options->Advanced->"," for thousand and "." for decimal I still have the result in the second picture:

    Formatting thousand/decimal excel sheet

Could someone help me with that? I really don't understand what's wrong.

GSerg
  • 76,472
  • 17
  • 159
  • 346
PiElle
  • 1
  • 1
  • As for 1, please use the debugger and see what's going on. As for 2, you most likely have a column formatted as text, to which you set a text value, so it's not going to be affected by your formats. – GSerg Jul 06 '20 at 16:10
  • Hi, Thanks for your answer. so for 1) apparently during the execution of `.Me.Arec3.Value > "" Then Me.Arec6 = Me.Arec3.Value * Me.Arec5.Value` is not reading the decimal "." separator of the Unit Price (€) and the operation is just considering the unit number as a whole number (e.g. Unit price is 5.73 is read as 573). Is this due to something wrong in the Excel settings or is something else? For 2) how I should let the € symbol, is there any tips to convert text result in number? Thanks again – PiElle Jul 07 '20 at 09:27
  • No, this is due to you using strings as numbers and vice versa. Please use numbers with numbers, and only in the end format the results as strings. – GSerg Jul 07 '20 at 09:49
  • ok, and how I can do that? – PiElle Jul 07 '20 at 09:54
  • I changed the decimal and thousand separators from my Laptop and everything is working fine now. Is this could be a solution? – PiElle Jul 09 '20 at 15:11
  • [Don’t use global state to manage a local problem](https://devblogs.microsoft.com/oldnewthing/20081211-00/?p=19873) – GSerg Jul 09 '20 at 15:22

0 Answers0