TextBox1 中的 EXCEL VBA 货币格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14158820/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
EXCEL VBA Currency format in TextBox1
提问by 4 Leave Cover
I want to format the value in TextBox1with the following:
我想TextBox1用以下格式格式化值：
- Ringgit Malaysia currency as in (RM) without the bracket
- make it 2 decimal place e.g. RM 10.00
- make the format appear in
TextBox1even when user is not entering any value e.g. RM 0.00 - allow user to enter integer only and also e.g. when user type 1234 in
TextBox1,TextBox1value will change from RM 0.00 to RM 12.34 User don't have to hit the '.', the integer will just move front e.g. RM 0.01 -> RM 0.12 -> RM 1.23 -> RM 12.34 as user press 1234 in theTextBox1.
- 马币马来西亚货币如（RM）没有括号
- 保留小数点后两位，例如 RM 10.00
TextBox1即使用户未输入任何值（例如 RM 0.00），也会显示格式- 允许用户只输入整数，例如当用户输入 1234 时
TextBox1，TextBox1值将从 RM 0.00 变为 RM 12.34 用户不必点击“.”，整数将向前移动，例如 RM 0.01 -> RM 0.12 -> RM 1.23 -> RM 12.34，因为用户在TextBox1.
If any misunderstanding elsewhere within my questions, please let me know and I will correct it. Thank you all in advance.
如果我的问题在其他地方有任何误解，请告诉我，我会更正。谢谢大家。
采纳答案by Lopsided
First:Your number format is easy enough. Open the Number format dialog box and create a custom format using this string:
第一：你的数字格式很简单。打开数字格式对话框并使用以下字符串创建自定义格式：
"RM"#,#0.00
“RM”#,#0.00
Second:To show empty cells as a zero, you have two options.
第二：要将空单元格显示为零，您有两个选择。
- If the cells is a result of an equation or data somewhere else, you can use a formula; e.g., =if(A1="",0,A1) This will take the value of A1 unless it is empty, in which case it would be 0.
- You may also set the worksheet to display all zero values under options in the file tab.
- 如果单元格是其他地方的方程式或数据的结果，您可以使用公式；例如，=if(A1="",0,A1) 这将采用 A1 的值，除非它是空的，在这种情况下它将是 0。
- 您还可以将工作表设置为在文件选项卡的选项下显示所有零值。
Third (and finally):As far as the automatic placement of decimals, this is not uncommon. Simply follow the steps in this tutorial:
第三（也是最后）：就小数的自动放置而言，这并不少见。只需按照本教程中的步骤操作：
How to insert decimal points automatically in Excel
I hope this helps, good luck!
我希望这会有所帮助，祝你好运！
EDIT:
编辑：
At first glance I thought you needed help with cell formatting, but you need help with a textbox control within a VBA Userform and/or a the worksheet itself. The tool that you need to accomplish this task is an Input Mask. However, from what I understand, there is currently no built-in support for this in a VBA userform. You can build code to do this when the user changes or exits the box.
乍一看，我以为您需要有关单元格格式的帮助，但您需要有关 VBA 用户窗体中的文本框控件和/或工作表本身的帮助。完成此任务所需的工具是输入掩码。但是，据我所知，目前在 VBA 用户表单中没有对此的内置支持。您可以构建代码以在用户更改或退出框时执行此操作。
I would recommend code when they exit that will first validate the input to confirm it is numeric, then change the value to match the desired format. Here is an example:
我会在他们退出时推荐代码，首先验证输入以确认它是数字，然后更改值以匹配所需的格式。下面是一个例子：
Private Sub myTextbox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim myVal As String
myVal = Trim(myTextbox.Value)
If myVal = "" Then Exit Sub 'Ignore this subroutine if the box is empty.
If IsNumeric(myVal) = False Then 'If the input is not a number, ask for correct input.
MsgBox "Please enter a number value."
myTextbox.SetFocus
Exit Sub
ElseIf InStr(1, myVal, ".", vbTextCompare) > 0 Then 'If the input includes decimal, use it.
myTextbox.Value = "RM " & Format(myVal, "#,##0.00")
ElseIf InStr(1, myVal, ".", vbTextCompare) = 0 Then 'If the input doesn't include decimal, enter one and divide by 100.
myTextbox.Value = "RM " & (Format(myVal, "#,##0.00") / 100)
End If
End Sub
And if you want to get really fancy, you can first search the input for "RM". This would ensure that an "invalid input" message box wouldn't pop up if the user entered a value such as "RM 123.45". Unfortunately I don't have time to write that code out for you at the moment.
如果你想变得真正花哨，你可以先在输入中搜索“RM”。这将确保如果用户输入诸如“RM 123.45”之类的值，则不会弹出“无效输入”消息框。不幸的是，我目前没有时间为您编写该代码。
