Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old May 20th, 2004, 01:31 PM
Registered User
Join Date: May 2004
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Excel VBA - Wordwrap conundrum

I am designing a 'program' in excel to help capture information on forms that are received by my employer. the idea is that the user fills in the blanks, and the pre-defined text is completed with the added data using the concatenate function. However, this information is then to be copied from excel and pasted into a program that is specific to our job. This program only allows 72 characters per line of text, and DOES NOT wrap text across lines.

So, if you are 5 spaces from the end of the line, and wish to type 'sentence' you end up with 'sente' on the first line and 'nce' on the second.

I am looking for a way for count back from the end of the line (if you are still typing at that point, i.e. last character of line is not a space) to the last 'space' that was entered, then force a carriage return, and start the process again. This has to be able to handle up to 1000 characters.

If you know of a way to do this, I would be ecstatic if you could tell me how it is done. Oh yeah, the text is all kept in a certain cell (L3 at the moment).

Thank you.
Old May 21st, 2004, 06:12 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts

I don't think it's possible to evaluate -on the fly- in Excel and I can't find any smart solutions. If someone else can, I'd like to know too.

This sub must be run when all text has been entered into the cell. I don't know whether the receiving program will be able to understand the linebreak made with chr(10). If not, try substituting chr(10) with VBCRLF in the sub.

Sub BreakSentence()
Dim Sentence, NewSentence As String
Dim Lngt, Pos, o, p As Long

Sentence = Cells(3, 12).Value
Lngt = Len(Sentence)

' more than 1 line ?
    If Lngt < 72 Then
        Exit Sub
    End If

    Do Until Lngt < 73

        ' if we're not lucky that pos 73 is empty then...
        If Not Mid(Sentence, 73, 1) = " " Then

        ' ... go backwards to the first empty space and save the position in Pos
        ' and exit the loop
            For p = 72 To 1 Step -1
                If Mid(Sentence, p, 1) = " " Then
                    Pos = p
                    Exit For
                End If
            Next p
        ' Add characters from pos1 to Pos of original sentence, in NewSentence and...
                If NewSentence = "" Then
                    NewSentence = Left(Sentence, Pos)
                    NewSentence = NewSentence & Chr(10) & Left(Sentence, Pos)
                End If
        ' ...make Sentence contain the rest of the original sentence
                Sentence = Right(Sentence, Lngt - Pos)
        ' Add characters from pos1 to 72 of original sentence, in NewSentence and...
                If NewSentence = "" Then
                    NewSentence = Left(Sentence, 72)
                    NewSentence = NewSentence & Chr(10) & Left(Sentence, 72)
                End If
        ' ...make Sentence contain the rest of the original sentence
                Sentence = Right(Sentence, Lngt - 72)
        End If

        ' redefine the length of the sentence
        Lngt = Len(Sentence)

' Add the remains of the sentence to NewSentence
NewSentence = NewSentence & Chr(10) & Sentence
Cells(3, 12).Value = NewSentence

End Sub

Similar Threads
Thread Thread Starter Forum Replies Last Post
Code works in Excel VBA but not Access VBA fossx Access VBA 2 May 21st, 2007 08:00 AM
Converting excel data to Access using excel VBA ShaileshShinde VB Databases Basics 1 April 26th, 2006 07:57 AM
Update query conundrum! timoma Access 4 April 1st, 2005 02:04 PM
Excel VBA to SQL & back to VBA edesousa Excel VBA 1 June 1st, 2004 02:39 AM
AutoNumber Conundrum enterbase Access VBA 4 January 28th, 2004 05:29 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.