Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Limit InputBox To Text Only & X Characters

  1. #1
    Join Date
    7th September 2009
    Posts
    10

    Limit InputBox To Text Only & X Characters

    what be the most economical regex pattern which would limit a text stings length to x characters and only allow a to z, A to Z, 0 to 9, and these 8 common punctuation marks , . - _ ( ) ? '

    Thank you

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,787

    Re: Regex Patern To Limit Text For Input Box

    Code:
    Sub TextOnly()
        Dim strReturn As String
        Dim lNumCheck As Long
        Dim bNumber As Boolean
        
        strReturn = Application.InputBox(Prompt:="Enter text", Type:=2)
        If strReturn = vbNullString Then Exit Sub
             
        If Len(strReturn) > 8 Then
             MsgBox "No more than 8 Characters"
             Run "TextOnly"
        End If
        
        For lNumCheck = 1 To Len(strReturn)
            bNumber = False
            bNumber = IsNumeric(Mid(strReturn, lNumCheck, 1))
                If bNumber = True Then
                    MsgBox "No numbers allowed"
                    Exit Sub
                End If
        Next lNumCheck
         
         
    End Sub
    Last edited by Dave Hawley; January 22nd, 2010 at 09:00.

  3. #3
    Join Date
    23rd April 2007
    Posts
    3,688

    Re: Limit InputBox To Text Only & X Characters

    The Like operator will test if any of the characters are not allowed.

    Code:
    If strReturn Like "*[!,.)'( _0-9A-Za-z(?)-]*" Then
        MsgBox "bad Character somewhere"
    End If

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    9th October 2009
    Posts
    30

    Re: Limit InputBox To Text Only & X Characters

    Borrowing from http://www.ozgrid.com/forum/showthread.php?t=37624 I would like to suggest the following code to get you started in the right direction:

    Code:
    Const MAX_LENGTH As String = "2"
    
    Function RegExResult(strData As String) As String
        Dim RE As Object, REMatches As Object
         
        Set RE = CreateObject("vbscript.regexp")
        With RE
            .MultiLine = False
            .Global = False
            .IgnoreCase = True
            .Pattern = "([\w]|[,.\-\_()?\']){1," & MAX_LENGTH & "}"
        End With
         
        Set REMatches = RE.Execute(strData)
        If REMatches.Count > 0 Then
            If REMatches(0) = "" Then
                RegExResult = "No match"
            Else
                RegExResult = REMatches(0)
            End If
        Else
            RegExResult = "No match"
        End If
         
    End Function
    
    
    Sub TextOnly()
        Dim strReturn As String
        Dim lNumCheck As Long
        Dim bNumber As Boolean
         
        strReturn = Application.InputBox(Prompt:="Enter text", Type:=2)
        If strReturn = vbNullString Then Exit Sub
         
        Dim result As String
        result = RegExResult(strReturn)
        
        MsgBox "Input: " & strReturn & vbCr & "Filtered: " & result
        
    End Sub

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,787

    Re: Limit InputBox To Text Only & X Characters

    Quote Originally Posted by mikerickson
    The Like operator will test if any of the characters are not allowed.

    Code:
    If strReturn Like "*[!,.)'( _0-9A-Za-z(?)-]*" Then
        MsgBox "bad Character somewhere"
    End If
    Nice

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Limit Number Of Characters In Autoshape
    By sheilaj in forum EXCEL HELP
    Replies: 19
    Last Post: October 4th, 2009, 05:08
  2. Limit Number Of Text Characters In A Cell
    By csmjpm in forum EXCEL HELP
    Replies: 3
    Last Post: February 1st, 2008, 03:13
  3. Limit One Cell To 55 Characters?
    By incansun in forum EXCEL HELP
    Replies: 11
    Last Post: February 18th, 2007, 09:20
  4. Minimum Characters in an Inputbox (excel/vba)
    By Nemo225 in forum EXCEL HELP
    Replies: 1
    Last Post: May 9th, 2006, 10:17
  5. 256 Text Box Character Limit...NOT Cell Limit
    By Joe Trinkley in forum EXCEL HELP
    Replies: 3
    Last Post: December 22nd, 2004, 18:57

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno