validating text boxes in user forms

[VBAmalta89]

I have a user form which contains a frame with 20 text boxes. Is there a way to create a validation procedure to ensure that only numbers are entered in the text boxes?

Ideally a code which can be used for all text boxes within the frame would be available. Currently I am trying to do it by validating each and every text box but it comes very long as I need to repeat the process 20 times!

 

[al_b_cnu]

Hi,

I think you need a class module for this, Try adapting the code from Andrew Poulsom's post here: http://www.mrexcel.com/forum/showthread.php?t=65825

 

[al_b_cnu]

Hi,

More specifically, try this:

Create a Class module named TBClass containing this code:

Option Explicit

'   Class module named TBClass

Public WithEvents TBGroup As MSForms.TextBox

Private Sub TBGroup_Change()
Dim iPtr As Integer
Dim sValueIn As String, sValueOut As String, sChar As String

sValueIn = TBGroup.Value
If IsNumeric(sValueIn) = False Then
    For iPtr = 1 To Len(sValueIn)
        sChar = Mid$(sValueIn, iPtr, 1)
        If IsNumeric(sChar) Then sValueOut = sValueOut & sChar
    Next iPtr
With Application
    .EnableEvents = False
    TBGroup.Value = sValueOut
    .EnableEvents = True
End With

End If
End Sub

Add this code to your userform:

Option Explicit

'Userform Code

Dim TBs() As New TBClass

Private Sub UserForm_Initialize()
    Dim TBCount As Integer
    Dim Ctrl As Control
    TBCount = 0
    For Each Ctrl In UserForm1.Controls
        If TypeName(Ctrl) = "TextBox" Then
            TBCount = TBCount + 1
            ReDim Preserve TBs(1 To TBCount)
            Set TBs(TBCount).TBGroup = Ctrl
        End If
    Next Ctrl
End Sub