Userform Textboxs are numeric (and null)

Question

I am implementing a Userform and wish to include some checks on the input data prior to running the Userform. In particular, check all inputs into the Userform textboxs are numerical, although it is valid a textbox is blank or Null. I have tried implementing the following:

    Select Case KeyAscii
    Case 0, 46, 48 To 57
    Case Else
    MsgBox "Only numbers allowed"
    End Select

But this does not work. Please, ideas? Thank you very much!!!!!!!!!

Answer 1

Maybe bit long winded - I usually use a class module and the tag property on the control to decide what can be entered in a textbox.

Create a form with four text boxes.
Give the text boxes these tags:

• 1;CDBL
• 2;CINT
• 3;CSTR
• 4;CSENTENCE

The numbers are the columns to paste the values into when the form is saved (I haven't described that bit here).
The text describes what can be entered in the textbox - CDBL is numeric with 2 decimal places, CINT is numeric with 0 decimal places, CSTR is for Proper text and CSENTENCE is for sentence text.

Create a class module called clsControlText.
Add this code to the class module:

Public WithEvents txtBox As MSForms.TextBox

Private Sub txtBox_Change()
  Static LastText As String
  Static SecondTime As Boolean
  Const MaxDecimal As Integer = 2
  Const MaxWhole As Integer = 1

  With txtBox
    If InStr(.Tag, ";") > 0 Then
        Select Case Split(.Tag, ";")(1)
            Case "CDBL", "CCur"
                'Allow only numbers with <=2 decimal places
                If Not SecondTime Then
                    If .Text Like "[!0-9.-]*" Or Val(.Text) < -1 Or _
                        .Text Like "*.*.*" Or .Text Like "*." & String$(1 + MaxDecimal, "#") Or _
                        .Text Like "?*[!0-9.]*" Then
                        Beep
                        SecondTime = True
                        .Text = LastText
                    Else
                        LastText = .Text
                    End If
                End If
                SecondTime = False
            Case "CINT"
                'Allow only whole numbers.
                If .Text Like "[!0-9]" Or Val(.Text) < -1 Or .Text Like "?*[!0-9]*" Then
                    Beep
                    .Text = LastText
                Else
                    LastText = .Text
                End If
            Case "CSTR"
                'Convert text to proper case.
                .Text = StrConv(.Text, vbProperCase)
            Case "CSENTENCE"
                'Convert text to sentence case (capital after full-stop).
                .Text = ProperCaps(.Text)
            Case Else
                'Allow anything.
        End Select
    End If
  End With
End Sub

Private Function ProperCaps(strIn As String) As String
    Dim objRegex As Object
    Dim objRegMC As Object
    Dim objRegM As Object
    Set objRegex = CreateObject("vbscript.regexp")
    strIn = LCase$(strIn)
    With objRegex
        .Global = True
        .ignoreCase = True
         .Pattern = "(^|[\.\?\!\r\t]\s?)([a-z])"
        If .Test(strIn) Then
            Set objRegMC = .Execute(strIn)
            For Each objRegM In objRegMC
                Mid$(strIn, objRegM.firstindex + 1, objRegM.Length) = UCase$(objRegM)
            Next
        End If
        ProperCaps = strIn
    End With
End Function  

Add this code to the user form:

Private colTextBoxes As Collection

Private Sub UserForm_Initialize()

    Dim ctrlSelect As clsControlText
    Dim ctrl As Control


    Me.Caption = ThisWorkbook.Name

    Set colTextBoxes = New Collection
    For Each ctrl In Me.Controls
        Select Case TypeName(ctrl)
            Case "TextBox"
                Set ctrlSelect = New clsControlText
                Set ctrlSelect.txtBox = ctrl
                colTextBoxes.Add ctrlSelect
        End Select
    Next ctrl

End Sub

NB: Not all this code is mine. I found ProperCaps and the code for CDBL elsewhere on this site - or maybe MrExcel.