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

ootkhopdi

Board Regular
Joined
Sep 30, 2013
Messages
68
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.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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)...
Code:
[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]
Code:
[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]
 
Upvote 0
Hi Rick,

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

Code:
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
 
Last edited:
Upvote 0
To prevent pasting non numeric data into the textboxes you could add the following one liner code to the Class Module :

Code:
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
 
Last edited:
Upvote 0
Hi Rick,

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

Code:
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.
 
Last edited:
Upvote 0
To prevent pasting non numeric data into the textboxes you could add the following one liner code to the Class Module :

Code:
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.
 
Upvote 0
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 :

Code:
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 :

Code:
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
 
Upvote 0
True .. You could check each character if it is numeric in a loop such as :

Code:
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 :

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top