Make set length of text value?

[brianburen]

I have a userform that shows the results of many values in 20 text strings

such as:

textlabel1= "firstname1 & " " & lastname1 & " " & housenumber1 & " " & street name1"

textlabel2= "firstname2 & " " & lastname2 & " " & housenumber2 & " " & street name2"

I want to set the text lengths of ... to be x amount letters always, so on the form it all lines up nicely


How do i format the length of the text to be 6 letters even if it is a 4 letter street and the extra 2 letters be spaces?

I tried:

firstname1=Format(ws.range("A1").Text, "XXXXXX")

but it doesnt work

 

[tigeravatar]

brianburen,

A tab character goes to the next indent alignment and starts there. The results using chr(9) should look like this:

<TABLE style="WIDTH: 110pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=146><COLGROUP><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" span=2 width=73><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 55pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20 width=73>name</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 55pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=73>last</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>nameone</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">last</TD></TR></TBODY></TABLE>

So I don't understand your question.

 

[brianburen]

it isn't doing that
it's adding 9 blank characters after the text

 

[tigeravatar]

brianburen,

Do this for me. Run this test macro to see what chr(9) does:

Sub test()
 
    MsgBox "name" & Chr(9) & "next" & Chr(10) & _
           "nameone" & Chr(9) & "next"
 
End Sub

It should pop up a messagebox showing what I described earlier. I ran it and it did as expected for me.

 

[brianburen]

tried that and it worked, is it cause Im using a label on a userform?

EDIT: i tried it with a textbox and it still is putting the same amount of space after a 6 letter word as it does a 3 letter word

 

[tigeravatar]

brianburen,

I created a test userform, added a label and a multiline textbox. I didn't change their caption or text properties, except for in the userform click event. This is the only code for the userform:

Private Sub UserForm_Click()
    Me.Label1.Caption = "name" & Chr(9) & "next" & Chr(10) & _
                        "nameone" & Chr(9) & "next"
    Me.TextBox1.Text = "name" & Chr(9) & "next" & Chr(10) & _
                       "nameone" & Chr(9) & "next"
End Sub

After clicking on the userform, this is what displays for me:

I am unable to duplicate your issue

 

[brianburen]

that is what i got using your exact code.

I am on Excel 2003

edit how do you post a picture on here?

 

[tigeravatar]

To display a picture, you need to upload the image to a website (I used my google+ profile), and then you get the URL to that image and put it in the img tags like so:

[img]url[/img]

 

[brianburen]

ok i figured it out

I still had the " " in the code, I took them out and now it works.

in the chr(9) what does the 9 stand for?


Private Sub UserForm_Click()
Me.Label1.Caption = "name" & " " & Chr(9) & " " &"next" & Chr(10) & _
"nameone" & " " & Chr(9) & " " & "next"

 

[tigeravatar]

Chr(#) returns the ascii character of the character code #. ascii code 9 is a horizontal tab. Here's a full list:

http://www.asciitable.com/

 

[brianburen]

OK thanks alot for your help, someday I will grow from a NOOB....lol