时间戳

首页 > 解决方案 > 仅允许数字作为用户输入值到 Application.InputBox for CommandButton1_Click()

vba - 仅允许数字作为用户输入值到 Application.InputBox for CommandButton1_Click()

问题描述

我正在为一种令人讨厌的可能性而苦苦挣扎，即用户可以插入小数/分数值，尽管应该只允许整数。我尝试使用 KeyAscii (48-57) 检查用户是否输入数字。但是，我无法让它工作，也无法使用其他一些功能。用户不能插入除数值（从 0 到 9 到 99）以外的任何内容。这适用于我附加代码中的“rowNum”。我控制了其他一切，但我无法让我的 VBA 为用户在按下 CommandButton_Click() 后插入的小数/分数值工作。我现在已经尝试了好几个小时来找到一个没有成功的解决方案。所有帮助表示赞赏！

Private Sub CommandButton1_Click()
Dim rowNum As Variant
Dim x As Variant
On Error Resume Next

rowNum = Application.InputBox(Prompt:="Please enter the row from where you want to add new empty rows downwards:", _
                                    Title:="Need to add new empty rows?", Type:=1)

                If (VarType(rowNum) = vbBoolean) And (rowNum = False) Then
                MsgBox "You canceled the event!", vbExclamation, "No Input!"

                ElseIf rowNum = 0 Then
                MsgBox "Smallest allowed row number is 7!", vbExclamation, "Invalid Input!"

                ElseIf rowNum < 0 Then
                MsgBox "Negative values are not allowed!", vbExclamation, "Invalid Input!"

                ElseIf rowNum < 7 Then 
        MsgBox "Smallest allowed row number is 7!!", vbExclamation, "Invalid Input!"

        ElseIf rowNum > 99 Then 
        MsgBox "Highest allowed row number is 99!", vbExclamation, "Invalid Input!"

                              End If
                              If rowNum < 7 Or rowNum > 99 Then End



x = Application.InputBox(Prompt:="Please insert the amount of empty rows that you need:", _
                            Title:="Amount of new rows?", Type:=1)


                If (VarType(x) = vbBoolean) And (x = False) Then
                MsgBox "You canceled the event!", vbExclamation, "No Input!"

                ElseIf x = 0 Then
                MsgBox "Smallest amount of new rows is 2!", vbExclamation, "Invalid Input!"

                ElseIf x < 2 Then
                MsgBox "Smallest amount of new rows is 2!", vbExclamation, "Invalid Input!"

                ElseIf x > 10 Then
                MsgBox "Highest amount of new rows is 10!", vbExclamation, "Invalid Input!"

                              End If
                              If x < 2 Or x > 10 Then End

Rows(rowNum & ":" & rowNum + x - 1).EntireRow.Insert Shift:=xlDown

End Sub

标签： vbaexcel

解决方案

添加另一个 elseif 子句

ElseIf x <> Int(x) Then
       MsgBox "Fraction number is not  allowed", vbExclamation, "Invalid Input!"


End if

可以解决问题

推荐阅读