Inputbox: collecting and using value to 3 decimal places

[Yamezz]

I have code that gathers data for new customers. The customer's default price is collected with

DefaultPrice = InputBox("Enter DEFAULT PRICE", "New Customer")

This is a price accurate to 3 decimal places. Most commonly, the value is 19.091
Hovering over DefaultPrice in the VBA editor shows the code has indeed collected 19.091 as the value
The value is entered into the customer table with the code

Sheets("Customers").Cells(LastCustomerRow + 1, 9) = DefaultPrice

The column being pasted into is formatted to Currency with 3 decimal places, however this cell displays $19.090 after pasting and the formula bar simply 19.09 (2 decimal places)

How can I retain the third decimal place?

Something like

Sheets("Customers").Cells(LastCustomerRow + 1, 9) = DefaultPrice.NumberFormat = "$#,##0.000"

 

[dmt32]

Hi,
Need to coerce the output from InputBox which is Text to Double

Sub Yameez()
    Dim DefaultPrice As Variant
    
    Do
        DefaultPrice = InputBox("Enter DEFAULT PRICE", "New Customer")
        'cancel pressed
        If StrPtr(DefaultPrice) = 0 Then Exit Sub
    Loop Until IsNumeric(DefaultPrice)
    
    DefaultPrice = CDbl(DefaultPrice)
    
    With Sheets("Customers").Cells(LastCustomerRow + 1, 9)
        .Value = DefaultPrice
        .NumberFormat = "$#,##0.000"
    End With
End Sub

Dave

 

[dmt32]

Alternatively, you could consider using the Application.InputBox Method where you can specify the data type

Sub Yameez()
    DefaultPrice = Application.InputBox("Enter DEFAULT PRICE", "New Customer", , , , , , 1)
    'cancel pressed
    If DefaultPrice = False Then Exit Sub
 
   With Sheets("Customers").Cells(LastCustomerRow + 1, 9)
        .Value = DefaultPrice
        .NumberFormat = "$#,##0.000"
    End With

End Sub

Dave

 

[BSALV]

in your regional setting, is a comma or a point your decimal separator ? (mine is ,)
Try this one with your 19.019 in a version with the comma and the point in your normal keyboard and numeric keyboard.
This works correct with the "," in the normal keyboard and "," in the numeric keyboard

Sub Jamezz()
     DefaultPrice = Application.InputBox("Enter DEFAULT PRICE", "New Customer", , , , , , 1)
     Range("A1").Value = DefaultPrice
End Sub

 

[Yamezz]

Thanks for the replies. My decimal place is a point (.) I am trying to enter (and retain) 19 dollars and 1.9 cents
I have strange results: I made a Test sheet and used these two codes:

DefaultPrice = Application.InputBox("Enter DEFAULT PRICE", "New Customer", , , , , , 1)
With Sheets("Test").Cells(LastCustomerRow + 1, 9)
        .Value = DefaultPrice
        .NumberFormat = "$#,##0.000"
    End With

DefaultPrice = InputBox("Enter DEFAULT PRICE (LUC or Retail)", "New Customer")
With Sheets("Test").Cells(LastCustomerRow + 1, 9)
        .Value = DefaultPrice
        .NumberFormat = "$#,##0.000"
    End With

Both placed $19.091 in the cell, but when I tried pasting the code into the rest of my new customer macro, I got $19.090 (and the formula bar just shows 19.09)
I removed the default cell formatting (currency with 3 decimal places), but that did not change the behaviour.

The full code of the macro is:

Sub NewCustomer()

ScreenUpdating = False

If IsEmpty(Range("SaleCustomer")) Then
MsgBox ("Please enter Customer name")
Exit Sub
End If

Dim NewCustomer As String, Address As String, LastCustomerRow As Long, PaymentMethod As String
Dim Suburb As String, State As String, Postcode As String, ABN As String, Email As String
Dim RowE As Long, DefaultPrice As Currency, InvoiceType As String

NewCustomer = Sheets("Sale").Range("SaleCustomer").Value

Address = InputBox("Enter ADDRESS (no Suburb, State, Postcode)", "New Customer")
Suburb = InputBox("Enter SUBURB", "New Customer")
State = InputBox("Enter STATE ABBREVIATION", "New Customer")
Postcode = InputBox("Enter POSTCODE", "New Customer")
ABN = InputBox("Enter ABN (with spaces)", "New Customer")
Email = InputBox("Enter EMAIL", "New Customer")
PaymentMethod = InputBox("Enter DEFAULT PAYMENT METHOD" & vbNewLine & "For credit card, enter Shopify", "New Customer", Default:="Direct Debit")
'DefaultPrice = InputBox("Enter DEFAULT PRICE (LUC or Retail)", "New Customer")
DefaultPrice = Application.InputBox("Enter DEFAULT PRICE", "New Customer", Type:=1)
InvoiceType = InputBox("Enter INVOICE TYPE (Email or None)", "New Customer")

LastCustomerRow = Sheets("Customers").Range("A" & Rows.Count).End(xlUp).Row

Sheets("Customers").Unprotect
Sheets("Customers").Activate
Sheets("Customers").Cells(LastCustomerRow + 1, 1) = NewCustomer
Sheets("Customers").Cells(LastCustomerRow + 1, 2) = Address
Sheets("Customers").Cells(LastCustomerRow + 1, 3) = Suburb
Sheets("Customers").Cells(LastCustomerRow + 1, 4) = State
Sheets("Customers").Cells(LastCustomerRow + 1, 5) = Postcode
Sheets("Customers").Cells(LastCustomerRow + 1, 6) = ABN
Sheets("Customers").Cells(LastCustomerRow + 1, 7) = PaymentMethod
Sheets("Customers").Cells(LastCustomerRow + 1, 8) = Email

   With Sheets("Customers").Cells(LastCustomerRow + 1, 9)
        .Value = DefaultPrice
        .NumberFormat = "$#,##0.000"
    End With

Sheets("Customers").Cells(LastCustomerRow + 1, 10) = InvoiceType

Sheets("Customers").Range("A2:J" & LastCustomerRow + 1).Select
    ActiveWorkbook.Worksheets("Customers").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Customers").Sort.SortFields.Add2 Key:=Range( _
        "A2:A6"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Customers").Sort
        .SetRange Range("A2", "J" & LastCustomerRow + 1)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Sheets("Customers").Protect

Sheets("Data").Unprotect
RowE = Sheets("Data").Range("E" & Rows.Count).End(xlUp).Row
Sheets("Data").Range("E2", "E" & RowE + 1).FillDown

Sheets("Sale").Activate

ScreenUpdating = True

End Sub

 

[BSALV]

stupid idea, suppose your cell start with format "text" before you give it the value.

set the numberformat first, before assigning the value (swap both).

 

[dmt32]

Try changing your variable data type which will also allow the managing of Cancel button being pressed

 Dim DefaultPrice As Variant
    DefaultPrice = Application.InputBox("Enter DEFAULT PRICE", "New Customer", , , , , , 1)
    'cancel pressed
    If DefaultPrice = False Then Exit Sub

Dave

 

[Yamezz]

Dave, it seems the variable type was the key. When I did my test code above I forgot to declare the DefaultPrice variable type. I presume not doing so automatically meant it became a variant, and thus the code worked as a test, but not when I specifically declared it as currency.

Thanks for your help guys.

 

[dmt32]

Correct, undeclared variables are of Variant data type by default.

Glad we were able to help & resolve your issue but would suggest that you look at managing the Cancel Button in your code

Dave

 

[Yamezz]

Just made the additions. Thank Dave.