PDA

View Full Version : [SOLVED] How to force WorkSheet InputBox to accept currency or decimal value



simora
08-17-2019, 07:31 PM
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

Artik
08-18-2019, 03:01 AM
Can you show how you wrote it?

Artik

Paul_Hossler
08-18-2019, 07:31 AM
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

snb
08-18-2019, 08:10 AM
Sub M_snb()
On Error Resume Next
[A2] = FormatCurrency(Application.InputBox("Enter a Currency", "Enter Currency"))
End Sub

simora
08-19-2019, 01:07 AM
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

snb
08-19-2019, 02:23 AM
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

simora
08-20-2019, 02:07 PM
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.