Consulting

Results 1 to 7 of 7

Thread: How to force WorkSheet InputBox to accept currency or decimal value

  1. #1
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location

    How to force WorkSheet InputBox to accept currency or decimal value

    Hi:
    I have an InputBox that posts the input value to a cell. Cell A2
    How can I get the inputBox to accept currency or decimal values and post to a cell on the ActiveSheet.Cell A2

  2. #2
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    Can you show how you wrote it?

    Artik

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Depending on what you need, one or both of these might work


    Option Explicit
    
    
    Sub DecimalInput()
        Dim D As Variant
        
    '   0   A Formula
    '   1   A Number
    '   2   Text (a string)
    '   4   A logical value (True or False)
    '   8   A cell reference, as a Range object
    '   16  An error value, such as #N/A
    '   64  An array of values
        
        D = Application.InputBox("Enter a Decimal", "Enter Decimal", 0#, , , , , 1)
        
        If VarType(D) = vbBoolean Then
            If Not D Then Exit Sub
        
        Else
            ActiveSheet.Range("A2").NumberFormat = "General"
            ActiveSheet.Range("A2").Value = D
        End If
    End Sub
    
    Sub CurrencyInput()
        Dim D As Variant
        
        D = Application.InputBox("Enter a Currency", "Enter Currency", 0#, , , , , 1)
        
        If VarType(D) = vbBoolean Then
            If Not D Then Exit Sub
        
        Else
            ActiveSheet.Range("A2").NumberFormat = "General"
            ActiveSheet.Range("A2").Value = CCur(D)
        End If
    End Sub
    
    
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Sub M_snb()
           On Error Resume Next
           [A2] = FormatCurrency(Application.InputBox("Enter a Currency", "Enter Currency"))
    End Sub

  5. #5
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location
    Hi Guys:
    All of your responses worked great; thanks.
    One question: Is there a FormatDecimal / FormatFloating equivalent to FormatCurrency ?
    If so, what is it.

    Thanks

  6. #6
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Formatnumber

    Next time:

    Alt-F11
    F2
    VBA
    Strings

    Function FormatNumber(Expression, [NumDigitsAfterDecimal As Long = -1], [IncludeLeadingDigit As VbTriState = vbUseDefault], [UseParensForNegativeNumbers As VbTriState = vbUseDefault], [GroupDigits As VbTriState = vbUseDefault]) As String
    Member of VBA.Strings
    Formats expression as number

  7. #7
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location
    Thanks snb:

    I had previously tried something like this along with trying to use FormatNumber, but I never did get the FormatNumber portion to work.
    This is what I had BEFORE the options that the board provided.

    decResult = 0
    dec = CDec(0.00000000001)
    
    myValue = Application.InputBox("Enter the Quantity here: ", " USE ONLY NUMBERS", 0#, , , , , 1)
    decResult = decResult + dec
    It worked because I only needed 2 places after the decimal, but it was a Kludge.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •