+ Reply to Thread
Results 1 to 24 of 24

Automatic line break after certain amount of characters

  1. #1
    Registered User
    Join Date
    09-13-2016
    Location
    Amsterdam
    MS-Off Ver
    2016, Office365
    Posts
    41

    Automatic line break after certain amount of characters

    Hello everyone,

    I'm new here on this forum. I read lots of topics before I registered myself.
    I'm quite new on VBA programming. Although I understand the basics I've sometimes some difficulties to discover the right expressions to get what I want.

    At this moment I'm on a little project to make a messaging tool for communication ground-air.
    In this tool I've a text box in which one is able to typ a message for air crew. The problem is (multi line is set on) that the whole message will appear on 1 line in the final message. The visible line breaks in the text box aren't real line breaks. I would like to have an automated line break after every 40 characters (spaces included) every time the text exceeds 40 characters.

    I came up with this code which, of course, doesn't work:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The question is: why? I would like to have VBA put a "vbNewLine" (new line) every time the character amount exceeds 40. Its seems to be more difficult than I thought. Or maybe I'm close, but took a wrong code somewhere.


    Thanks a lot!

    Paul

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Automatic line break after certain amount of characters

    Hi Paul,

    Here is one way to consider

    Please Login or Register  to view this content.
    The first Dim line must be placed at the top of the code module for the userform
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Registered User
    Join Date
    09-13-2016
    Location
    Amsterdam
    MS-Off Ver
    2016, Office365
    Posts
    41

    Re: Automatic line break after certain amount of characters

    Thanks for your quick reply. Unfortunately this one doesn't work. It places many, many new lines (can't count them anymore).
    What exactly does the "Len(Replace(MessageBox2.Text, vbNewLine, vbNullString))" code do? And the bSkipEvents? I'm trying to figure out how to adjust this code to a working one.
    So, yes it appends a new line after x characters. But it appends too many. I think more then 50!

  4. #4
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Automatic line break after certain amount of characters

    It works well for me. Can you please post the full code of the userform as it is now?

    The bSkipEvents variable is to prevent adding a new line triggering the change event again, which would cause the problem you mention.
    The Replace part is to ensure that the new line characters are not included in the length of the text- this is to ensure that the line breaks allow 40 characters of actual typed text per line.
    Last edited by xlnitwit; 09-13-2016 at 09:08 AM.

  5. #5
    Registered User
    Join Date
    09-13-2016
    Location
    Amsterdam
    MS-Off Ver
    2016, Office365
    Posts
    41

    Re: Automatic line break after certain amount of characters

    The main userform code is for the initialisation only. The send and reset buttons do have codes as well. The userform is divided in 4 tabs. The second tab has the text box in which I would like to have this tric.

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    The text box is now:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Thanks a lot for your help!

  6. #6
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Automatic line break after certain amount of characters

    The declaration line for bSkipEvents needs to be at the top of the module- i.e. before all procedures, not within the Initialize event.

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Automatic line break after certain amount of characters

    Try
    Please Login or Register  to view this content.
    Last edited by jindon; 09-13-2016 at 10:29 AM.

  8. #8
    Registered User
    Join Date
    09-13-2016
    Location
    Amsterdam
    MS-Off Ver
    2016, Office365
    Posts
    41

    Re: Automatic line break after certain amount of characters

    Hi xlnitwit,

    Your code works pretty well. But it only does a new line after numbers. Not text. I adjusted the code to max 10 characters for easier testing.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Also text formatting like backspace to remove some written text doesn't work correctly. It removes 10 characters and then it moves the cursor to the last line +1.
    It should handle the max 10 characters as if the text is a continuous message.
    Any help appreciated.

  9. #9
    Registered User
    Join Date
    09-13-2016
    Location
    Amsterdam
    MS-Off Ver
    2016, Office365
    Posts
    41

    Re: Automatic line break after certain amount of characters

    Quote Originally Posted by jindon View Post
    Try
    Please Login or Register  to view this content.

    Thanks Jindon, but this one doesn't work at all. Or am I doing something wrong?

  10. #10
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Automatic line break after certain amount of characters

    Jindon's code will run when the focus leaves the textbox rather than as you type. It is in all honesty a far better way to do what you want, I believe.

  11. #11
    Registered User
    Join Date
    09-13-2016
    Location
    Amsterdam
    MS-Off Ver
    2016, Office365
    Posts
    41

    Re: Automatic line break after certain amount of characters

    The code from Jindon does trigger an error:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Maybe the solution is as simple as putting a " somewhere but as I can't understand this code well I don't know where to start.

  12. #12
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Automatic line break after certain amount of characters

    What is the error message or number?

  13. #13
    Registered User
    Join Date
    09-13-2016
    Location
    Amsterdam
    MS-Off Ver
    2016, Office365
    Posts
    41

    Re: Automatic line break after certain amount of characters

    Run-time error '5':
    Invalid procedure call or argument.

  14. #14
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Automatic line break after certain amount of characters

    Did you by any chance not have any spaces at all in the text you entered?

  15. #15
    Registered User
    Join Date
    09-13-2016
    Location
    Amsterdam
    MS-Off Ver
    2016, Office365
    Posts
    41

    Re: Automatic line break after certain amount of characters

    I just start to suspect that the code of Jindon contains a code which is not allowed/supported by VBA Excel. Like the $ characters... Might that be a problem? I'm so far not familiar with $-signs in VBA Excel. This is the first time seeing it.

  16. #16
    Registered User
    Join Date
    09-13-2016
    Location
    Amsterdam
    MS-Off Ver
    2016, Office365
    Posts
    41

    Re: Automatic line break after certain amount of characters

    Quote Originally Posted by xlnitwit View Post
    Did you by any chance not have any spaces at all in the text you entered?
    I just entered some test text so including some spaces.

  17. #17
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Automatic line break after certain amount of characters

    Did you enter any words- or just consecutive letters- that were longer than the maximum length value you are using?

  18. #18
    Registered User
    Join Date
    09-13-2016
    Location
    Amsterdam
    MS-Off Ver
    2016, Office365
    Posts
    41

    Re: Automatic line break after certain amount of characters

    I think I found out what went wrong. I accidently edited the code and saved it and never put it back. Will check it tonight or tomorrow. Keep you informed.

  19. #19
    Registered User
    Join Date
    09-13-2016
    Location
    Amsterdam
    MS-Off Ver
    2016, Office365
    Posts
    41

    Re: Automatic line break after certain amount of characters

    Hi there,
    Ok, it does work! Wonderful job. Thanks Jindon and xlnitwit for your help.
    As long as words aren't 40 characters long (and even in Dutch I don't know words of this length) this works perfectly. One more thing though. When typing text, I'm not able to start a new line myself. Let say I would like to have text like:

    THIS TEXT WILL APPEAR ON THE FIRST LINE
    THIS ONE ON THE SECOND
    BLABLA BLABLA BLABLA
    THIS IS ALREADY LINE 4

    All I get is:
    THIS TEXT WILL APPEAR ON THE FIRST LINE
    THIS ONE ON THE SECOND BLABLA BLABLA
    BLABLA THIS IS ALREADY LINE 4

    It doesn't allow me to start a new line. Is there a way to allow me to do so?

    Once again, thanks!

  20. #20
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Automatic line break after certain amount of characters

    I think that would be extremely difficult. I can't currently think of a way for code to differentiate between a manually entered line feed and one added to fix line lengths. Unless it is possible to use both vbLf and vbCrLf to achieve the same appearance. I have a busy schedule today but will see what I can do. Perhaps Jindon will see this follow up and have a solution for that too.

  21. #21
    Registered User
    Join Date
    09-13-2016
    Location
    Amsterdam
    MS-Off Ver
    2016, Office365
    Posts
    41

    Re: Automatic line break after certain amount of characters

    I really appreciate your help! I'll try to play around with the code as well.

  22. #22
    Registered User
    Join Date
    09-13-2016
    Location
    Amsterdam
    MS-Off Ver
    2016, Office365
    Posts
    41

    Re: Automatic line break after certain amount of characters

    I guess I fixed the problem by changing this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    into this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Will test it in the next hours. But for now it seems it fixed the "bug". Will let you know.

  23. #23
    Registered User
    Join Date
    09-13-2016
    Location
    Amsterdam
    MS-Off Ver
    2016, Office365
    Posts
    41

    Re: Automatic line break after certain amount of characters

    Hi xlnitwit,
    The above mentioned change fixed the problem.
    Just out of curiosity... words are now kept together. How can I adjust the trim-function to just cut the word at exactly the given MaxChar?

    Like:
    THIS IS A TEST MES
    SAGE WITH MAX AM
    OUNT OF CHARACTE
    RS ON EACH LINE

    Paul

  24. #24
    Registered User
    Join Date
    09-13-2016
    Location
    Amsterdam
    MS-Off Ver
    2016, Office365
    Posts
    41

    Re: Automatic line break after certain amount of characters

    A while ago I had this problem as mentioned above. At this moment my post (#23) is unsolved yet. I tried hard to fix this without success.
    Another point is that (lets say I have limited MaxChar at 110) the lines must only break after 110 characters on 1 line. If a line has 100 characters and I start writing on the 2nd line it will break that line already after 10 characters. It counts to 110 no matter if the line is not even close to 110 (10 in this case) and will put a line break whenever it counts 110 characters in the whole text.
    Anyone with some insight in how to handle this issue?
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Thanks a lot (again! )

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 25
    Last Post: 07-12-2016, 05:46 PM
  2. Replies: 6
    Last Post: 11-19-2015, 09:34 AM
  3. [SOLVED] Insert a line break (CHAR(10)) after every 42 characters
    By Rajkiran557 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-22-2014, 06:08 AM
  4. VBA to replace special characters with line break
    By saranyabi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-06-2013, 03:11 PM
  5. Automatic Line Break at X number of characters
    By duyane in forum Excel General
    Replies: 3
    Last Post: 07-17-2012, 02:12 AM
  6. Replies: 5
    Last Post: 10-19-2011, 12:27 PM
  7. [SOLVED] How to break up a cell with 3 embedded (new line characters) value
    By Phil in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-23-2005, 07:05 PM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1