I have a listbox which I ad data to with listbox.additem "" & c.offset(0,-1)
& ......
But some cells has longer names in them and thus I cannot seem to list them
neatly next to each other for example:

Appels 1000 Bear
Apricot-jam 30 Impala

How can i get the spacing correct?

Any help will do
Thanks

With Me.ListBox1
For Each c In Range("A1:A10")
For i = 0 To 2
tmp = tmp & c.Offset(0, i).Value & vbTab
Next i
.AddItem tmp
Next c
End With


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"JustLearning" wrote in message
...
I have a listbox which I ad data to with listbox.additem "" &
c.offset(0,-1)
& ......
But some cells has longer names in them and thus I cannot seem to list
them
neatly next to each other for example:

Appels 1000 Bear
Apricot-jam 30 Impala

How can i get the spacing correct?

Any help will do
Thanks

You can set the ColumnCount property of the listbox to 3, for example, and
load an array to the list. E.g.,

Sub AAA()
Dim NumRows As Long
Dim NumCols As Long
Dim Arr() As String
NumRows = 2
NumCols = UserForm1.ListBox1.ColumnCount
ReDim Arr(0 To NumRows - 1, 0 To NumCols - 1)
Arr(0, 0) = "a"
Arr(0, 1) = "b"
Arr(0, 2) = "c"
Arr(1, 0) = "d"
Arr(1, 1) = "e"
Arr(1, 2) = "f"

UserForm1.ListBox1.ColumnWidths = "50;50;50"
UserForm1.ListBox1.List = Arr
UserForm1.Show
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"JustLearning" wrote in message
...
I have a listbox which I ad data to with listbox.additem "" &
c.offset(0,-1)
& ......
But some cells has longer names in them and thus I cannot seem to list
them
neatly next to each other for example:

Appels 1000 Bear
Apricot-jam 30 Impala

How can i get the spacing correct?

Any help will do
Thanks

It works but still it does not solve the problem:
I have found that if I use a word smaller than 8 characters and a word
longer than 13 characters this happens:

St Annes 2007/02/17
St Augustine 2007/02/17

But if i count the char and use an if statement that states if the char < 8
then using two vbtabs or else only one vbtab then i get

St Annes 2007/02/17
St Augustine 2007/02/17
But this if statement effects the 3,4,5 columns and messes the columns up
again


"Bob Phillips" wrote:

With Me.ListBox1
For Each c In Range("A1:A10")
For i = 0 To 2
tmp = tmp & c.Offset(0, i).Value & vbTab
Next i
.AddItem tmp
Next c
End With


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"JustLearning" wrote in message
...
I have a listbox which I ad data to with listbox.additem "" &
c.offset(0,-1)
& ......
But some cells has longer names in them and thus I cannot seem to list
them
neatly next to each other for example:

Appels 1000 Bear
Apricot-jam 30 Impala

How can i get the spacing correct?

Any help will do
Thanks

Hi Chip,

I have tried your solution as well but I have a lot of info coming up in
this listbox, how do i set the array with random info? The code from you and
Bob I used as one and each time it writes the string and goes to the next
line and writes that string as well but each time a new string is written to
the listbox it deletes the prevouis string thus ending up with a blank
listbox at the end? Any suggestions?



Set c = .find("", LookIn:=xlValues)
If ((Now()) - (c.Offset(0, -1))) = 17 Then
With UserForm2.ListBox1
For Each c In Range("A2:A1000")
For i = 0 To 3
tmp = tmp & c.Offset(0, i).Value
Select Case i
Case 0
a = tmp
Case 1
a1 = tmp
Case 2
a2 = tmp
Case 3
a3 = tmp
End Select
tmp = ""
Next i
i = 0
NumRows = 1000
NumCols = UserForm2.ListBox1.ColumnCount ' (columncount=4)
ReDim Arr(0 To NumRows - 1, 0 To NumCols - 1)
Arr(d, 0) = a
Arr(d, 1) = a1
Arr(d, 2) = a2
Arr(d, 3) = a3
UserForm2.ListBox1.ColumnWidths = "50;50;50"
UserForm2.ListBox1.List = Arr
d = d + 1
UserForm2.Show
Next c
End With

"Chip Pearson" wrote:

You can set the ColumnCount property of the listbox to 3, for example, and
load an array to the list. E.g.,

Sub AAA()
Dim NumRows As Long
Dim NumCols As Long
Dim Arr() As String
NumRows = 2
NumCols = UserForm1.ListBox1.ColumnCount
ReDim Arr(0 To NumRows - 1, 0 To NumCols - 1)
Arr(0, 0) = "a"
Arr(0, 1) = "b"
Arr(0, 2) = "c"
Arr(1, 0) = "d"
Arr(1, 1) = "e"
Arr(1, 2) = "f"

UserForm1.ListBox1.ColumnWidths = "50;50;50"
UserForm1.ListBox1.List = Arr
UserForm1.Show
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"JustLearning" wrote in message
...
I have a listbox which I ad data to with listbox.additem "" &
c.offset(0,-1)
& ......
But some cells has longer names in them and thus I cannot seem to list
them
neatly next to each other for example:

Appels 1000 Bear
Apricot-jam 30 Impala

How can i get the spacing correct?

Any help will do
Thanks

If you can live with a fixed-width font such as Courier New in the listbox,
you can use the SizeString function on www.cpearson.com/excel/SizeString.htm
to create substrings of a specific number of characters, padded on the left
or right with space characters (or any character you want) or truncated on
the right to fill the specified number of characters.

Sub AAA()
Dim S As String
Dim Rng As Range
UserForm1.ListBox1.Font.Name = "Courier New"
UserForm1.ListBox1.Clear
For Each Rng In Range("A1:A3")
S = SizeString(Text:=Rng(1, 1).Text, Length:=20, _
TextSide:=TextLeft, PadChar:=" ") & _
SizeString(Text:=Rng(1, 2), Length:=20, _
TextSide:=TextLeft, PadChar:=" ") & _
SizeString(Text:=Rng(1, 3), Length:=20, _
TextSide:=TextLeft, PadChar:=" ")
UserForm1.ListBox1.AddItem S
Next Rng
UserForm1.Show
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)




"JustLearning" wrote in message
...
It works but still it does not solve the problem:
I have found that if I use a word smaller than 8 characters and a word
longer than 13 characters this happens:

St Annes 2007/02/17
St Augustine 2007/02/17

But if i count the char and use an if statement that states if the char <
8
then using two vbtabs or else only one vbtab then i get

St Annes 2007/02/17
St Augustine 2007/02/17
But this if statement effects the 3,4,5 columns and messes the columns up
again


"Bob Phillips" wrote:

With Me.ListBox1
For Each c In Range("A1:A10")
For i = 0 To 2
tmp = tmp & c.Offset(0, i).Value & vbTab
Next i
.AddItem tmp
Next c
End With


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"JustLearning" wrote in message
...
I have a listbox which I ad data to with listbox.additem "" &
c.offset(0,-1)
& ......
But some cells has longer names in them and thus I cannot seem to list
them
neatly next to each other for example:

Appels 1000 Bear
Apricot-jam 30 Impala

How can i get the spacing correct?

Any help will do
Thanks

Yes that works!! But still have a small problem my userform gives me an
object variable or with variable not set error with the new code after it
ran?(When trying to close the form, I am using unload userform2)
And why if I use a different font it gets all messy again?

"Chip Pearson" wrote:

If you can live with a fixed-width font such as Courier New in the listbox,
you can use the SizeString function on www.cpearson.com/excel/SizeString.htm
to create substrings of a specific number of characters, padded on the left
or right with space characters (or any character you want) or truncated on
the right to fill the specified number of characters.

Sub AAA()
Dim S As String
Dim Rng As Range
UserForm1.ListBox1.Font.Name = "Courier New"
UserForm1.ListBox1.Clear
For Each Rng In Range("A1:A3")
S = SizeString(Text:=Rng(1, 1).Text, Length:=20, _
TextSide:=TextLeft, PadChar:=" ") & _
SizeString(Text:=Rng(1, 2), Length:=20, _
TextSide:=TextLeft, PadChar:=" ") & _
SizeString(Text:=Rng(1, 3), Length:=20, _
TextSide:=TextLeft, PadChar:=" ")
UserForm1.ListBox1.AddItem S
Next Rng
UserForm1.Show
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)




"JustLearning" wrote in message
...
It works but still it does not solve the problem:
I have found that if I use a word smaller than 8 characters and a word
longer than 13 characters this happens:

St Annes 2007/02/17
St Augustine 2007/02/17

But if i count the char and use an if statement that states if the char <
8
then using two vbtabs or else only one vbtab then i get

St Annes 2007/02/17
St Augustine 2007/02/17
But this if statement effects the 3,4,5 columns and messes the columns up
again


"Bob Phillips" wrote:

With Me.ListBox1
For Each c In Range("A1:A10")
For i = 0 To 2
tmp = tmp & c.Offset(0, i).Value & vbTab
Next i
.AddItem tmp
Next c
End With


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"JustLearning" wrote in message
...
I have a listbox which I ad data to with listbox.additem "" &
c.offset(0,-1)
& ......
But some cells has longer names in them and thus I cannot seem to list
them
neatly next to each other for example:

Appels 1000 Bear
Apricot-jam 30 Impala

How can i get the spacing correct?

Any help will do
Thanks

object variable or with variable not set error

Without seeing the code, I can't really guess what's going on. In the VBA
editor, go to the Tools menu, choose Options then the General tab. There,
ensure that the "Error Trapping" setting is "Break In Class Module" not
"Break On Unhandled Errors".

Run the code and see what line it blows up on. If it blows up, click the
"Debug" button to display the offending line of code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"JustLearning" wrote in message
...
Yes that works!! But still have a small problem my userform gives me an
object variable or with variable not set error with the new code after it
ran?(When trying to close the form, I am using unload userform2)
And why if I use a different font it gets all messy again?

"Chip Pearson" wrote:

If you can live with a fixed-width font such as Courier New in the
listbox,
you can use the SizeString function on
www.cpearson.com/excel/SizeString.htm
to create substrings of a specific number of characters, padded on the
left
or right with space characters (or any character you want) or truncated
on
the right to fill the specified number of characters.

Sub AAA()
Dim S As String
Dim Rng As Range
UserForm1.ListBox1.Font.Name = "Courier New"
UserForm1.ListBox1.Clear
For Each Rng In Range("A1:A3")
S = SizeString(Text:=Rng(1, 1).Text, Length:=20, _
TextSide:=TextLeft, PadChar:=" ") & _
SizeString(Text:=Rng(1, 2), Length:=20, _
TextSide:=TextLeft, PadChar:=" ") & _
SizeString(Text:=Rng(1, 3), Length:=20, _
TextSide:=TextLeft, PadChar:=" ")
UserForm1.ListBox1.AddItem S
Next Rng
UserForm1.Show
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)




"JustLearning" wrote in message
...
It works but still it does not solve the problem:
I have found that if I use a word smaller than 8 characters and a word
longer than 13 characters this happens:

St Annes 2007/02/17
St Augustine 2007/02/17

But if i count the char and use an if statement that states if the char
<
8
then using two vbtabs or else only one vbtab then i get

St Annes 2007/02/17
St Augustine 2007/02/17
But this if statement effects the 3,4,5 columns and messes the columns
up
again


"Bob Phillips" wrote:

With Me.ListBox1
For Each c In Range("A1:A10")
For i = 0 To 2
tmp = tmp & c.Offset(0, i).Value & vbTab
Next i
.AddItem tmp
Next c
End With


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"JustLearning" wrote in
message
...
I have a listbox which I ad data to with listbox.additem "" &
c.offset(0,-1)
& ......
But some cells has longer names in them and thus I cannot seem to
list
them
neatly next to each other for example:

Appels 1000 Bear
Apricot-jam 30 Impala

How can i get the spacing correct?

Any help will do
Thanks

This is strange I use this function for all my "Prevouis menu functions" ?
Can it be because it is still reverring to the listbox??


Private Sub CommandButton1_Click()

Unload UserForm2 line that is giving the error

End Sub

"Chip Pearson" wrote:

object variable or with variable not set error

Without seeing the code, I can't really guess what's going on. In the VBA
editor, go to the Tools menu, choose Options then the General tab. There,
ensure that the "Error Trapping" setting is "Break In Class Module" not
"Break On Unhandled Errors".

Run the code and see what line it blows up on. If it blows up, click the
"Debug" button to display the offending line of code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

Hi Chip,

Got it figured out - In your function that is pointing to the Sizestring
there is a userform1.show and taking that out fixed the problem when using
the unload userform1 statement!
Thanks for the help!!
Much appreciated from South-Africa





"JustLearning" wrote:

This is strange I use this function for all my "Prevouis menu functions" ?
Can it be because it is still reverring to the listbox??


Private Sub CommandButton1_Click()

Unload UserForm2 line that is giving the error

End Sub

"Chip Pearson" wrote:

object variable or with variable not set error

Without seeing the code, I can't really guess what's going on. In the VBA
editor, go to the Tools menu, choose Options then the General tab. There,
ensure that the "Error Trapping" setting is "Break In Class Module" not
"Break On Unhandled Errors".

Run the code and see what line it blows up on. If it blows up, click the
"Debug" button to display the offending line of code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

Yes, the reference to UserForm1 was for illustration purposes.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"JustLearning" wrote in message
...
Hi Chip,

Got it figured out - In your function that is pointing to the Sizestring
there is a userform1.show and taking that out fixed the problem when using
the unload userform1 statement!
Thanks for the help!!
Much appreciated from South-Africa





"JustLearning" wrote:

This is strange I use this function for all my "Prevouis menu functions"
?
Can it be because it is still reverring to the listbox??


Private Sub CommandButton1_Click()

Unload UserForm2 line that is giving the error

End Sub

"Chip Pearson" wrote:

object variable or with variable not set error

Without seeing the code, I can't really guess what's going on. In the
VBA
editor, go to the Tools menu, choose Options then the General tab.
There,
ensure that the "Error Trapping" setting is "Break In Class Module" not
"Break On Unhandled Errors".

Run the code and see what line it blows up on. If it blows up, click
the
"Debug" button to display the offending line of code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)