Formatting default value of input box

[marrick13]

I have a userform that reads data from several worksheets (in the same workbook as the userform). Those values represent dollar amounts and are formatted in the sheet with two decimals. I want to display them as the default entry in an input box so one can see what's already stored as the cost for that item

I've tried several variations, as follows ('"EntryItemAmt" is both the worksheet range name and the VBA variable that's read as the input box default):

EntryItemAmt = Worksheets("Formulas").Range(Format("EntryItemAmt", "0.00"))
EntryItemAmt = Worksheets("Formulas").Format(Range("EntryItemAmt", "0.00"))
EntryItemAmt = Worksheets("Formulas").Range("EntryItemAmt", "0.00").NumberFormat = "0.00"

and none of them work. If the value in the worksheet is, say, 4.16, it displays in the inputbox as "4", and if it's 4.73 in the worksheet, it displays as "5".

Is there a way to have the values show as the input box default as 4.16 and 4.73 instead of 4 or 5?

 

[Jerry Sullivan]

Hi marrick13,

If you're referring to an input box on the UserForm, then you probably have a TextBox control.

Below are 3 ways to populate a TextBox control with a default formatted to two decimal places.

If your worksheet cell is already formatted to two decimals; you can read it directly into the TextBox:

Private Sub UserForm_Initialize()
    Me.TextBox1 = Worksheets("Formula").Range("EntryItemAmt").Text
End Sub

If your Variable is a String; format prior to assigning the value to the String

Private Sub UserForm_Initialize()
    Dim sEntryItemAmt As String
    
    sEntryItemAmt = Format(Worksheets("Formula").Range("EntryItemAmt"), "0.00")
    Me.TextBox1 = sEntryItemAmt
    
End Sub

If your Variable is a Double; format when assigning the Variable's value to the TextBox

Private Sub UserForm_Initialize()
    Dim dEntryItemAmt As Double
    
    dEntryItemAmt = Worksheets("Formula").Range("EntryItemAmt")
    Me.TextBox1 = Format(dEntryItemAmt, "0.00")
    
End Sub

If you're actually using the InputBox function, then the syntax would be similar.

Sub MyMacro()
    Dim dEntryItemAmt As Double
    Dim vResult As Variant
    
    dEntryItemAmt = Worksheets("Formula").Range("EntryItemAmt")
    vResult = InputBox("Enter amount", "MyTitle", Format(dEntryItemAmt, "0.00"))
    
End Sub

 

[marrick13]

Jerry,

Thanks so much - I am using an input box with the userform, not a textbox, and was able to format the default for 2 decimals-but it wasn't reading the data from the worksheet that way. Your suggestion to declare the dEntryItemAmt As Double is what did the trick (the vResult can be a string rather than a variant).

Powerful though Excel is, I didn't have to go through this with Lotus 123. This project is converting a simple automated database application from Lotus (with macros) to Excel, and each time I read a cell in Lotus into a variable, it displays exactly as written. Of course, one doesn't declare variables in Lotus, either...

Thanks again for your help!