Restrict All Textboxes which are in a frame to Allow input Numbers only

[ootkhopdi]

Hi..

i have a user form Named"Form1"
in this form i have 2 frames Fr1 and Fr2
in fr2 i have 10 textboxes..

i want to restrict only Number value in all text boxes of Fr2
how can i do it..
thanks in advance.

 

[Rick Rothstein]

i want to restrict only Number value in all text boxes of Fr2
how can i do it..

What do you mean by "number"? Digits only or can there be a decimal point? What about plus or minus signs?

 

[ootkhopdi]

My Mean by "Number" is Digits only . no decimal point ,no plus or minus sign.

 

[Rick Rothstein]

My Mean by "Number" is Digits only . no decimal point ,no plus or minus sign.

The following code will restrict all TextBoxes located in your frame named Fr2 to accept only digits. You will need to add a Class Module to your project... you will be placing code in it as well as in the UserForm's code module (see the comments at the start of each code window below to see where that code in that code window should be installed)...

[table="width: 500"]
[tr]
	[td]' Place the following in a userform
' ==================================
Dim AobjTextBoxes() As New Class1

Private Sub UserForm_Initialize()
    Dim intCtlCnt As Integer, objControl As Control
   
    For Each objControl In Me.Fr2.Controls
        If TypeOf objControl Is MSForms.TextBox Then
            intCtlCnt = intCtlCnt + 1
            ReDim Preserve AobjTextBoxes(1 To intCtlCnt)
            Set AobjTextBoxes(intCtlCnt).TextBoxEvents = objControl
        End If
    Next objControl
    Set objControl = Nothing
End Sub[/td]
[/tr]
[/table]

[table="width: 500"]
[tr]
	[td]' Place in a Class module
' ========================
Public WithEvents TextBoxEvents As MSForms.TextBox

Dim LastPosition As Long

Private Sub TextBoxEvents_Change()
  Static LastText As String
  Static SecondTime As Boolean
  If Not SecondTime Then
    With TextBoxEvents
     If .Text Like "*[!0-9]*" Then
        Beep
        SecondTime = True
        .Text = LastText
        .SelStart = LastPosition
      Else
        LastText = .Text
      End If
    End With
  End If
  SecondTime = False
End Sub

Private Sub TextBoxEvents_MouseDown(ByVal Button As Integer, _
                               ByVal Shift As Integer, _
                               ByVal X As Single, ByVal Y As Single)
  With TextBoxEvents
    LastPosition = .SelStart
    'Place any other MouseDown event code here
  End With
End Sub

Private Sub TextBoxEvents_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  With TextBoxEvents
    LastPosition = .SelStart
    'Place any other KeyPress checking code here
  End With
End Sub[/td]
[/tr]
[/table]

 

[Jaafar Tribak]

Hi Rick,

Wouldn't the following code suffice in the Class module :

Public WithEvents TextBoxEvents As MSForms.TextBox

Private Sub TextBoxEvents_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If Chr(KeyAscii) Like "[!0-9]" Then KeyAscii = 0
End Sub

 

[Jaafar Tribak]

To prevent pasting non numeric data into the textboxes you could add the following one liner code to the Class Module :

Private Sub TextBoxEvents_BeforeDropOrPaste(ByVal Cancel As MSForms.ReturnBoolean, ByVal Action As MSForms.fmAction, ByVal Data As MSForms.DataObject, ByVal X As Single, ByVal Y As Single, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)
      Cancel = Not IsNumeric(Data.GetText)
End Sub

 

[Rick Rothstein]

Hi Rick,

Wouldn't the following code suffice in the Class module :

Public WithEvents TextBoxEvents As MSForms.TextBox

Private Sub TextBoxEvents_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If Chr(KeyAscii) Like "[!0-9]" Then KeyAscii = 0
End Sub

No... type a number into one of the "protected" TextBoxes, then copy this text...

A12BC3D4

and paste it anywhere into the TextBox... the letters remain. The code I posted won't allow the non-numeric text to be pasted into the TextBox.


EDIT NOTE: I just saw your additional post. I'll have to think about whether that was enough or not. The code I posted is some 15+ years old, originally written for VB6 (compiled version) with the event headers modified for Excel's VBA world. My recollection for using the SecondTime variable and tracking the cursor position was under some circumstance (can't think of what it is now, some 15 years plus later), data got entered incorrectly without it. I'll see if I can remember the circumstance and, if I can, whether your event procedures avoid the problem I am half-remembering.

 

[Rick Rothstein]

To prevent pasting non numeric data into the textboxes you could add the following one liner code to the Class Module :

Private Sub TextBoxEvents_BeforeDropOrPaste(ByVal Cancel As MSForms.ReturnBoolean, ByVal Action As MSForms.fmAction, ByVal Data As MSForms.DataObject, ByVal X As Single, ByVal Y As Single, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)
      Cancel = Not IsNumeric(Data.GetText)
End Sub

That code does not stop all non-digit entries... I am able to paste " 123", "$123", "+123", "123-", "1,2,3", "2d3", "&HEAD", "&o1", "$1,,,,,2,,,,3e+4", and so on into the "protected" TextBoxes.

 

[Jaafar Tribak]

That code does not stop all non-digit entries... I am able to paste " 123", "$123", "+123", "123-", "1,2,3", "2d3", "&HEAD", "&o1", "$1,,,,,2,,,,3e+4", and so on into the "protected" TextBoxes.

True .. You could check each character if it is numeric in a loop such as :

Private Sub TextBoxEvents_BeforeDropOrPaste(ByVal Cancel As MSForms.ReturnBoolean, ByVal Action As MSForms.fmAction, ByVal Data As MSForms.DataObject, ByVal X As Single, ByVal y As Single, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)
    Dim i As Long
    For i = 1 To Len(Data.GetText)
        If Not IsNumeric(Mid(Data.GetText, i, 1)) Then Cancel = True: Exit Sub
    Next
End Sub

Or maybe a quicker code without looping such as :

Private Sub TextBoxEvents_BeforeDropOrPaste(ByVal Cancel As MSForms.ReturnBoolean, ByVal Action As MSForms.fmAction, ByVal Data As MSForms.DataObject, ByVal X As Single, ByVal y As Single, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)
    Dim sTemp As String
    sTemp = Val(Data.GetText)
    Cancel = Not (sTemp = Data.GetText)
End Sub

 

[Rick Rothstein]

True .. You could check each character if it is numeric in a loop such as :

Private Sub TextBoxEvents_BeforeDropOrPaste(ByVal Cancel As MSForms.ReturnBoolean, ByVal Action As MSForms.fmAction, ByVal Data As MSForms.DataObject, ByVal X As Single, ByVal y As Single, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)
    Dim i As Long
    For i = 1 To Len(Data.GetText)
        If Not IsNumeric(Mid(Data.GetText, i, 1)) Then Cancel = True: Exit Sub
    Next
End Sub

Or maybe a quicker code without looping such as :

Private Sub TextBoxEvents_BeforeDropOrPaste(ByVal Cancel As MSForms.ReturnBoolean, ByVal Action As MSForms.fmAction, ByVal Data As MSForms.DataObject, ByVal X As Single, ByVal y As Single, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)
    Dim sTemp As String
    sTemp = Val(Data.GetText)
    Cancel = Not (sTemp = Data.GetText)
End Sub

I haven't fully tested your first code above (I really wish I could remember what I needed all the code I posted to protect against... it will eventually come to me), but your second code allow floating point numbers to be copy/pasted into the "protected" TextBox.