Code for character allowance in cell
Results 1 to 9 of 9

Thread: Code for character allowance in cell

  1. #1
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,379
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Code for character allowance in cell

    Morning,

    I am looking for a code which will show a msgbox to advise me the character count in the active cell is anything but 17

    Some info for you.
    Worksheet is called MC LIST
    The code will only need to work for column B
    The range would be B6 & down the page

    Example,

    So i type in cell B9 15 characters & when i leave the cell a msg box pops up saying only 15 characters typed etc.

    Same scenario if i was to type 19 characters etc.

    If 17 characters are typed then when i leave the active cell no msg box is to be shown.
    @DanteAmor

    Many thanks
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,449
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Code for character allowance in cell

    Try this Worksheet_Change event code. To implement ..
    1. Right click the 'MC LIST' sheet name tab and choose "View Code".
    2. Copy and Paste the code below into the main right hand pane that opens at step 1.
    3. Close the Visual Basic window & test.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
      Dim Changed As Range, cell As Range
      Dim sMsg As String
      
      Set Changed = Intersect(Target, Range("B6:B" & Rows.Count))
      If Not Changed Is Nothing Then
        For Each cell In Changed
          If Len(cell.Text) <> 17 Then sMsg = sMsg & vbLf & cell.Address(0, 0) & vbTab & Len(cell.Text)
        Next cell
        If Len(sMsg) > 0 Then MsgBox "Cell" & vbTab & "Characters" & sMsg
      End If
    End Sub
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,732
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Code for character allowance in cell

    I can also recommend using Data Validation:

    Limit Characters Length In A Cell

    1. Select the range that you will limit date entries with specify character length i.e. B6:B1000.


    2. Click the Data validation in the Data Tools group under Data tab.


    3. In the Data Validation dialog box, select the Text Length item from the Allow: drop down box.


    4. In the Data: drop down box, select equal to.


    5. In Length: Entry 17.


    6. Click OK.

    ----------------------------------
    But, if you want the code.

    In addition to the message, what you need to do, do not allow text entry.
    If so, then it could be like this:


    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
      Dim c As Range
      On Error GoTo AllowEvents
      If Target.Count > 1000 Then Exit Sub
      If Not Intersect(Target, Range("B:B")) Is Nothing Then
        For Each c In Target
          If c.Row > 5 And c.Column = 2 Then
            If Len(c.Value) <> 17 Then
              Application.EnableEvents = False
              MsgBox "Only 17 characters are allowed"
              c.Value = ""
              c.Select
            End If
          End If
        Next
      End If
    AllowEvents:
      Application.EnableEvents = True
    End Sub
    ----------------------------------
    SHEET EVENT
    Right click the tab of the sheet (MC LIST) you want this to work , select view code and paste the code into the window that opens up.
    Regards Dante Amor

  4. #4
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,379
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code for character allowance in cell

    @DanteAmor
    I have used your code as shown below.

    Many thanks

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)  Dim c As Range
      On Error GoTo AllowEvents
      If Target.Count > 1000 Then Exit Sub
      If Not Intersect(Target, Range("B:B")) Is Nothing Then
        For Each c In Target
          If c.Row > 5 And c.Column = 2 Then
            If Len(c.Value) <> 17 Then
              Application.EnableEvents = False
              MsgBox "VIN MUST BE 17 CHARACTERS", vbCritical, "VIN CHARACTER COUNT MESSAGE"
              c.Value = ""
              c.Select
            End If
          End If
        Next
      End If
    AllowEvents:
      Application.EnableEvents = True
    
    End Sub

    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,732
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Code for character allowance in cell

    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,449
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Code for character allowance in cell

    Quote Originally Posted by ipbr21054 View Post
    @ DanteAmor
    I have used your code as shown below.
    Just wondering why you don't use Excel's built-in Data Validation as suggested by Dante? With that you can include your message heading and text via the 'Error Alert' tab in the Data Validation dialog.

    Also, I assume that it is fine for the user to delete a VIN number? If so, your code still gives the error message pop-up (particularly painful if you have selected, say, 20 cells in the column and pressed 'Delete') whereas the Data Validation would not.

    However, if you want to persist with the vba (& there may be good reason to do that) and deleting a VIN number is acceptable, then I would suggest one change to the code:
    Code:
    If Len(c.Value) <> 17 And Len(c.Value) > 0 Then
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  7. #7
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,379
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code for character allowance in cell

    Thatsnk,
    I had already encountered the error message when deleting & had also added that code last night.

    Have a nice day
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  8. #8
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,449
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Code for character allowance in cell

    Quote Originally Posted by ipbr21054 View Post
    Thatsnk,
    I had already encountered the error message when deleting & had also added that code last night.

    Have a nice day
    OK, good.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  9. #9
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,732
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Code for character allowance in cell

    Quote Originally Posted by Peter_SSs View Post
    If so, your code still gives the error message pop-up (particularly painful if you have selected, say, 20 cells in the column and pressed 'Delete') whereas the Data Validation would not.

    Code:
    If Len(c.Value) <> 17 And Len(c.Value) > 0 Then
    Hi Peter, thanks for the observation. We usually tried some scenarios trying to cover all possibilities, but this time I never tried to delete a cell.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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