首页 文章

如何在Excel VBA中控制TextBox的KeyPress方法

提问于
浏览 次
0

我有一个代码将在Multipage中创建TextBoxes：

Private Sub CommandButton1_Click()

RowChar = 70
MultiPage1.Pages.Clear

For i = 0 To TextBox1.Value - 1
    MultiPage1.Pages.Add
    MultiPage1.Pages(i).Caption = "Variable" & i + 1

    Call LabelPerPage

    Set txtbx = UserForm1.MultiPage1.Pages(i).Controls.Add("Forms.TextBox.1", "NameBox")
    With txtbx
        .Top = 20
        .Left = 100
    End With

    Set txtbx = UserForm1.MultiPage1.Pages(i).Controls.Add("Forms.TextBox.1", "MinBox")
    With txtbx
        .Top = 50
        .Left = 100
    End With


    Set txtbx = UserForm1.MultiPage1.Pages(i).Controls.Add("Forms.TextBox.1", "LsbBox")
    With txtbx
        .Top = 20
        .Left = 300
    End With

    Set txtbx = UserForm1.MultiPage1.Pages(i).Controls.Add("Forms.TextBox.1", "MaxBox")
    With txtbx
        .Top = 50
        .Left = 300
    End With

    If i = 0 Then
        FormulaString = "= C15"
    Else
        FormulaString = FormulaString & "  " & Chr(RowChar) & "15"
        RowChar = RowChar + 3
    End If
Next i
TextBox2.Value = FormulaString
End Sub

Private Sub LabelPerPage()
    Set txtbx = UserForm1.MultiPage1.Pages(i).Controls.Add("Forms.Label.1")
    With txtbx
        .Top = 20
        .Left = 50
        .Caption = "NAME:"
    End With

    Set txtbx = UserForm1.MultiPage1.Pages(i).Controls.Add("Forms.Label.1")
    With txtbx
        .Top = 50
        .Left = 50
        .Caption = "MIN:"
    End With

    Set txtbx = UserForm1.MultiPage1.Pages(i).Controls.Add("Forms.Label.1")
    With txtbx
        .Top = 20
        .Left = 250
        .Caption = "LSB:"
    End With

    Set txtbx = UserForm1.MultiPage1.Pages(i).Controls.Add("Forms.Label.1")
    With txtbx
        .Top = 50
        .Left = 250
        .Caption = "MAX:"
    End With
End Sub

我试图创建一个代码，它将在其中创建一个页面和文本框，我的问题是我不能在TextBox上做KeyPress，因为它只会因为我的代码而自动创建 .

Goal:

1.）我的目标是做KeyPress，TextBox无法输入数字值或字母 .

2.）我想将textbox1最小的2个文本框与textbox2进行比较

**我希望有人可以帮助我，因为我尽力解决它 . 我尝试这样做代码：

Option 1:

Private Sub MaxBox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If (KeyAscii > 46 And KeyAscii < 58) Or KeyAscii = 43 Then
KeyAscii = KeyAscii
Else
KeyAscii = 0
MsgBox "Invalid key pressed, you can enter numbers only"
End If
End Sub

Option 2:

Private Sub OnlyNumbers()
    If TypeName(Me.ActiveControl) = "MaxBox" Then
        With Me.ActiveControl
            If Not IsNumeric(.Value) And .Value <> vbNullString Then
                MsgBox "Sorry, only numbers allowed"
                .Value = vbNullString
            End If
        End With
    End If
End Sub

但它不会起作用 . 如果有人可以帮助，请提前感谢你:)

excel vba excel-vba textbox keypress

1 回答

  • 1

    为什么不完全失去 keyPress ，因为你唯一想要实现的只是数字输入？您可以在userform代码中执行以下操作：

    Option Explicit
'Variable to capture Change event from your textbox:
Private WithEvents maxbox As MSForms.TextBox

'The creation of the thing; I just created a multipage control to reuse your lines.
Private Sub UserForm_Initialize()
    Dim txtbox as MSForms.TextBox
    Dim i As Integer
    i = 0
    Set txtbox = UserForm1.MultiPage1.Pages(i).Controls.Add("Forms.TextBox.1", "MaxBox")
    With txtbox 
        .Top = 50
        .Left = 50
    End With
    Set maxbox = txtbox
End Sub

'Capture change of maxbox:
Private Sub maxbox_Change()
    'In case somebody entered something non-numeric:   
    If IsNumeric(maxbox.Text) = False Then
        'Remove the input character
        maxbox.Text = Left(maxbox.Text, Len(maxbox.Text) - 1)
        'And alert the user
        MsgBox "numeric only!"
    End If
End Sub

    如果您需要多个，您还可以创建一个捕获事件的自定义类，并将该类的 collection 添加到Userform . 为此，您可以查看答案on this question

    Edit: 对于后半部分（针对Minbox进行验证），您可以使用相同的事件：只需添加另一个 if 语句以确保数值为 > CInt(minbox.text) （或其他数字类型） .

    Edit 2: 如果字符串的长度为0（即有人按下退格键/删除键以触发更改事件），您可能希望为 Left(maxbox.Text, Len(maxbox.Text) - 1) 位添加错误处理 .

    回复于

相关问题