line break code in VBA Excel Solved

Question

Hello,

I am editing labels in Excel VBA; For each label, I always have 2 lines or almost, depending on the length of the label I print and the product code that comes immediately after.

I would like to be able to "force" the line break, so that it always happens at the same place in my string. But I don't know what code I need to put for that in my string.

Who knows the solution to this problem?
Thank you very much in advance.
Roland
Example: I want to print on my label "product machine thing B02465"

But I want to get it all on two lines
"product machine thing"
"B02465"

Of course, my Excel line is set to "wrap text automatically"

Configuration: Windows XP / Internet Explorer 8.0

Mike-31 · Accepted Answer

Hi,

for example, if you want to write "hello" on one line and "who is this" on the next line in A2
[A2] = "hello" & vbCrLf & "who is this"

--
Best regards,
Mike-31

A period of failure is an ideal time to sow the seeds of knowledge.

ccm81 · Answer

Hello I think a chr(10) should do the trick Have a good continuation

lecrol · Answer

Thank you both, chefs!
I tried chr(10) and it works great

If I may take the liberty, would you happen to know if it's also possible to "force" the size or font of the characters printed this way? Still under VBA of course.
That's the question for "2000 heroes" (Lol)
Best regards
Roland

Mike-31 · Answer

Re,

example to test column A from A1 to the last non-empty cell in A
if the cells are not empty, they will be set to bold (Bold = True
) and font size 18

Sub test()
Dim c As Range
For Each c In Range("A1:A" & Range("A65536").End(xlUp).Row)
If c <> "" Then
With c.Font
.Bold = True
.Size = 18
End With
End If
Next c
End Sub

--
A+
Mike-31

A period of failure is a perfect time to sow the seeds of knowledge.

ccm81 · Answer

re
I don't know if we can only act on the output size, I'll try something like this, assuming the data to be printed comes from a sheet,
- change the size
- print
- revert to the initial size

Private Sub CommandButton1_Click() Dim s As Long s = Range("A2").Font.Size Range("A2").Font.Size = 2 * s 'for example MsgBox ("printing now") ' you run your print code Range("a2").Font.Size = s End Sub

but the specialist Mike-31 surely must have better

best wishes

Mytå · Answer

Hello forum

You can always play with the Zoom on the page

 With ActiveSheet.PageSetup .Zoom = 200 End With ActiveWindow.SelectedSheets.PrintOut

Mytå
--
Thank you for following up on your question, we are not robots...
The urgent is done, the impossible is in progress and for miracles; please allow some time.
Installed versions [MsProject 2003(FR), Excel 2003-2007(FR)]

lecrol · Answer

A big thank you to the specialist bosses.
But my request was too "picky." I actually wanted to change the font but within the same string of characters sent from VBA to an Excel cell (label cell).

I wanted to write my label this way
Product label
Price

With Chr(10), I know how to place my price on the 2nd line of the same cell
And there, being a nuisance that I am, I wanted my price to be printed in bold and font 12 in each label cell, on this “forced” 2nd line by a

range("cell").value = "product label" & chr(10) & price

But I recognize that it's a luxury of presentation and that it's not essential

For now, I'm struggling with a form and a sub-form in Access to then enter these same barcode labels

Thank you again, chiefs
Roland

Mike-31 · Answer

Re,

no, let's not exaggerate ccm, I'm not a specialist, just a tinkerer, but thanks anyway.
A few explanations about line returns
vbCr is equivalent to Chr(13) Paragraph break character
vbLf is equivalent to Chr(10) Line break character
Combination of carriage return characters and line feed
vbCrLf is equivalent to Chr(13) + Chr(10)

If we group the data, in A we have information like "product thing B02465"
create a button with the control box, if it's the first one, paste this code in the sheet properties (right-click on the sheet tab/View Code)

the line return will occur 2 characters before the numerical value BO2465

Private Sub CommandButton1_Click()
Dim Start As Byte
Dim Cell As Range
For Each Cell In Range("A1:A" & Range("A65536").End(xlUp).Row)
Start = InStr(1, Cell, " B")
If Not Start = 0 Then
Cell = Left(Cell, Start) & vbLf & Right(Cell, Len(Cell) - Start) ' the replacement value is in cell E1 (to adapt)
Cell.Font.Size = 18
End If
Next Cell
End Sub

if you need a little setup, let me know
--
A+
Mike-31

A period of failure is a perfect time to sow the seeds of knowledge.

Mike-31 · Answer

Re,

It is not difficult to create line breaks, make parts of the text bold or colored, etc. ...
The most difficult part is understanding the request, especially when the information comes in fragmented and changing ways.
For my part, what I do not understand is this last piece of information

range("cellule").value = "product label" & chr(10) & price

Very different from the initial request
"product thing B02465"

Can we have a clear detail of the request? From this last sentence, I deduce that the label is in one column, the price in another, and that we are concatenating all of this into a cell over two lines, with this second line being "forced" by a

What should be understood by "forced" as well?
--
A+
Mike-31

A period of failure is a perfect time to sow the seeds of knowledge.

ccm81 · Answer

Hi everyone

Well, to respond to post 7 from lecol, we just need to go fishing in the codes of Mike-31 and Myta

And for example

 .Value = Label & Chr(10) & Price With .Characters(Start:=Len(Label) + 2).Font .Size = 12 .Bold = True End With

Applied to each label cell should do the trick

Have a great day

lecrol · Answer

For my part, what I don't understand is this last piece of information
range("cellule").value = "product label" & chr(10) & price
Very different from the initial request
"product machine thing B02465"

Mike-31, thank you for your response

The line:
range("cellule").value = "product label" & chr(10) & price is my VBA code to fill my Excel cell
Its difference from the line in the previous post is that here, in this second example, the price is instead of the product code

It is simply a single text (a string of characters) that I send from VBA to a single Excel cell
I want the text to be placed on 2 lines in that same cell, this through the "automatic line break" option in Excel
And I achieve that very well with the Chr(10) that I place in my string, under VBA
That’s what I call "forcing" the line break

Thank you again
Roland

Mike-31 · Answer

Re,

The request is relevant, and just for fun, assuming that the data is in column A formatted according to the initial request (reference and price), for example, product thing 2465.30, this code allows you to modify the data in place by inserting a line break before the price regardless of the length of the string, coloring the line break red and bold. If a currency unit follows the price, e.g., product thing 2465.30 €, a slight modification to the code is necessary.

Private Sub CommandButton1_Click()
Dim tabLignes() As Long, i As Long, j As Long, tmp As Long
Dim Debut As Byte
Dim Cell As Range
Dim tmpStr() As String
Application.ScreenUpdating = False
For Each Cell In Range("A1:A" & Range("A65536").End(xlUp).Row)
Debut = InStr(1, Cell, " " & StrReverse(Split(StrReverse(Cell.Text), Chr(32))(0)))
If Not Debut = 0 Then
Cell = Left(Cell, Debut) & Chr(10) & Right(Cell, Len(Cell) - Debut)
End If
tmpStr = Split(Cell.Text, Chr(10))
ReDim tabLignes(1 To UBound(tmpStr) + 1, 1 To 2)
For i = LBound(tmpStr) To UBound(tmpStr)
tmp = 0
For j = LBound(tmpStr) To i - 1
tmp = tmp + Len(tmpStr(j))
Next j
tabLignes(i + 1, 1) = tmp + 1 + i
tabLignes(i + 1, 2) = Len(tmpStr(i))
Next i
Cell.Characters(tabLignes(2, 1), tabLignes(2, 2)).Font.ColorIndex = 3
Cell.Characters(tabLignes(2, 1), tabLignes(2, 2)).Font.Bold = True
Next Cell
Application.ScreenUpdating = True
[A1].Select
End Sub

A+
Mike-31

A period of failure is a perfect time to plant the seeds of knowledge.

Mike-31 · Answer

Hi,

It's a shame that Lecrol isn't following the discussion either to adapt or refine the code to these needs, or simply to mark the status as resolved so that this discussion serves as a reference.

--
See you,
Mike-31

A period of failure is a perfect time to sow the seeds of knowledge.

lecrol · Answer

My sincere apologies, Mike 31.
Forgive me, but I completely forgot to mark my question as resolved!

Actually, my line break is perfectly managed by this CHR(10).
As for using two different fonts for the same string of characters printed in a single Excel cell, a string sent via VBA, I think it's probably excessive or exaggerated. So, I'm giving up on that for the moment.

On this site, thanks to you, I already find so many precious bits of help that I don't want to take advantage.

I must say, at the moment, I am deeply involved in development for my daughter. With Access and VBA, I have several bits of programs in progress. So, I'm moderating my requests...

Best regards
Roland

Mike-31 · Answer

Re,

As I mentioned earlier, this discussion is relevant to follow regarding line returns; I provided their meanings, for example, for a line return either we enter Chr(10) which is 7 bytes or vbhf 4 bytes, the result is identical except that the title is lighter by 3 bytes, multiplied by X times it can significantly lighten a file.

vbCr is equivalent to Chr(13) Paragraph break character.
vbLf is equivalent to Chr(10) Line break character.
Combination of carriage return characters and line breaks:
vbCrLf is equivalent to Chr(13) + Chr(10)

It is entirely possible to give a different formatting for each line obtained, font color, bold, font size, font type, etc...
It remains to know exactly the content of the cells, where the line break should occur in the string and how many line breaks we want, and to know the desired formatting for each line.
Moreover, the code in my second-to-last post cuts the string into two lines, making the second line bold and red,
but we could push the code further by making multiple line breaks in the cell and giving a format to each line, as well as limiting the number of lines so that if we click multiple times on the button the code only runs once.

To be continued!
A+
Mike-31

A period of failure is a perfect time to sow the seeds of knowledge.

Line break code in VBA Excel

15 réponses

Samsung j5 unable to answer

Scam of the suvedene highway

Minecraft restore the cursor and the bar.

My ps3 keeps shutting down on its own...

Samsung series 4 led tv logo display...

How to enable bluetooth on a samsung tv?

Ctrl key issue

[metin2] sura ...

Freebox tv locked step 2 or network

Error 46 (brother dcp-150c) cleaning impossible