Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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
    9

    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,708

    Re: Regex Patern To Limit Text For Input Box

    VB:
    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 08:00.

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

    Re: Limit InputBox To Text Only & X Characters

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

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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    8th 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:

    VB:
    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,708

    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.

    VB:
    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, 04:08
  2. Limit Number Of Text Characters In A Cell
    By csmjpm in forum EXCEL HELP
    Replies: 3
    Last Post: February 1st, 2008, 02:13
  3. Limit One Cell To 55 Characters?
    By incansun in forum EXCEL HELP
    Replies: 11
    Last Post: February 18th, 2007, 08:20
  4. Minimum Characters in an Inputbox (excel/vba)
    By Nemo225 in forum EXCEL HELP
    Replies: 1
    Last Post: May 9th, 2006, 09:17
  5. 256 Text Box Character Limit...NOT Cell Limit
    By Joe Trinkley in forum EXCEL HELP
    Replies: 3
    Last Post: December 22nd, 2004, 17: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