VBA Spacing between two words

[jensslofstra]

Hi everyone,
I've been trying the whole day but can't figure it out.
I need to align words in two rows. (It's used in an automatic header.)
What I've stumbled upon was that adding spaces doesn't automaticly mean words align perfectly.

What is want to accomplish is something like this.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer:[/TD]
[TD]Mister John Doe[/TD]
[/TR]
[TR]
[TD]Company:[/TD]
[TD]John's Enterprice[/TD]
[/TR]
</tbody>[/TABLE]

I've tried this code already and also used Chr(9) and vbTab instead of spaces.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    With Worksheets("Offerte").PageSetup
        .LeftHeader = Worksheets("Sheet1").Range("B3") & Space(16) & Worksheets("Sheet1").Range("C3") & Chr(10) & Worksheets("Sheet1").Range("B4") & Space(14) & Worksheets("Sheet1").Range("C4")
    End With
End Sub

 

[jmacleary]

Hi there. The difficulty you are having is down to which font you are using. Many (or even most) fonts use a variable amount of width for different letters (an i is much narrower than a w), so alignment with that type of font is impossible. If you change the cell font to a monospace font (monospace means every character is given the same width) - courier is the most popular but there are many others - then you will be able to position stuff exactly. (or is it possible to use 2 columns?).

 

[DanteAmor]

You must change the font in the header.

Try this:

    With Worksheets("Offerte").PageSetup
        .LeftHeader = [COLOR=#0000ff]"&""Consolas,Normal"""[/COLOR] & _
            Worksheets("Sheet1").Range("B3") & Space([COLOR=#0000ff]15[/COLOR]) & _
            Worksheets("Sheet1").Range("C3") & Chr(10) & _
            Worksheets("Sheet1").Range("B4") & Space([COLOR=#0000ff]16[/COLOR]) & _
            Worksheets("Sheet1").Range("C4")
    End With

Print Preview: