Microsoft Office Forums - View Single Post

gmayor · 09-25-2019, 08:51 PM

The following will do that. It assumes TextBox1 is the name of the textbox in question and CommandBuitton1 is the name of the button that exits the userform.

Code:

Option Explicit

Private Sub CommandButton1_Click()
Dim sNum As String
    sNum = Get_Num(TextBox1.Text)
    If Len(TextBox1.Text) < 7 Or sNum = "" Then
        MsgBox "The data in the text box is incorrect"
        TextBox1.SetFocus
        GoTo lbl_Exit
    End If
    FillBM "CaseNum1", sNum
    Unload Me
lbl_Exit:
    Exit Sub
End Sub

Private Function Get_Num(s As String) As String
Dim i As Long
    For i = 1 To Len(s) - 6
        If Mid(s, i, 7) Like "##-####" Then
            Get_Num = Mid(s, i, 7)
            Exit For
        End If
    Next i
End Function

Private Sub FillBM(strbmName As String, strValue As String)
'Graham Mayor - http://www.gmayor.com
Dim oRng As Range
    With ActiveDocument
        On Error GoTo lbl_Exit
        Set oRng = .Bookmarks(strbmName).Range
        oRng.Text = strValue
        oRng.Bookmarks.Add strbmName
    End With
lbl_Exit:
    Set oRng = Nothing
    Exit Sub
End Sub

You can ensure that only numbers or a hyphen are entered in the field in the first place by adding the following code

Code:

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Dim bTest As Boolean
    bTest = IsAllowed(CStr(KeyAscii))
    If bTest = False Then
        Beep
        KeyAscii = 0
    End If
lbl_Exit:
    Exit Sub
End Sub

Private Function IsAllowed(ByVal i As String) As Boolean
Select Case Val(i)        'Checking to see if inside valid Ascii range for integers
        Case 45, 48 To 57
            IsAllowed = True
        Case Else
            IsAllowed = False
    End Select
lbl_Exit:
    Exit Function
End Function