how show more 10 columns in listbox on userform

Ali M

Board Regular
Joined
Oct 10, 2021
Messages
184
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi
I need showing about 11 columns in listbox on userform based on fill date textbox1 is from date , textbox2 is to date and match with column A (DATE)for sheet1
VBA Code:
Private Sub TextBox1_AfterUpdate()

On Error Resume Next
Me.TextBox1.Value = Format(TextBox1.Value, "mm/dd/yyyy")
End Sub

Private Sub TextBox2_AfterUpdate()

Dim i As Long
On Error Resume Next
Me.TextBox2 = Format(Me.TextBox2, "mm/dd/yyyy")

Me.ListBox1.Clear

For i = 2 To Application.WorksheetFunction.CountA(sheet1.Range("a:a"))
If sheet1.Cells(i, 1).Value >= CDate(Me.TextBox1.Text) And _
sheet1.Cells(i, 1).Value <= CDate(Me.TextBox2.Text) Then
Me.ListBox1.AddItem sheet1.Cells(i, 1).Value
Me.ListBox1.List(ListBox1.ListCount - 1, 1) = sheet1.Cells(i, 2).Value 
Me.ListBox1.List(ListBox1.ListCount - 1, 2) = sheet1.Cells(i, 3).Value
Me.ListBox1.List(ListBox1.ListCount - 1, 3) = sheet1.Cells(i, 4).Value
Me.ListBox1.List(ListBox1.ListCount - 1, 4) = sheet1.Cells(i, 5).Value
Me.ListBox1.List(ListBox1.ListCount - 1, 5) = sheet1.Cells(i, 6).Value
Me.ListBox1.List(ListBox1.ListCount - 1, 6) = sheet1.Cells(i, 7).Value
Me.ListBox1.List(ListBox1.ListCount - 1, 7) = sheet1.Cells(i, 8).Value
Me.ListBox1.List(ListBox1.ListCount - 1, 8) = sheet1.Cells(i, 9).Value
Me.ListBox1.List(ListBox1.ListCount - 1, 9) = sheet1.Cells(i, 10).Value
Me.ListBox1.List(ListBox1.ListCount - 1, 10) = sheet1.Cells(i, 11).Value

End If
Next i
End Sub
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
2,651
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Your post does not really tell us your problem. It's just some code.

What is the problem? What are you expecting to happen and what actually happens instead?

There is no problem to display 11 columns. You have to make sure your ListBox.ColumnCount is 11, ListBox.ColumnWidths is set for the desired widths, and the ListBox.Width is wide enough to show all the columns.
 

Ali M

Board Regular
Joined
Oct 10, 2021
Messages
184
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Your post does not really tell us your problem. It's just some code.
the title of thread tell you the whole story .;)
What is the problem? What are you expecting to happen and what actually happens instead?
what I expected show all of the columns as in code , but reallity just 10 columns shows.
There is no problem to display 11 columns
it doesn't work for me just try to test it .
 

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
2,651
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
the title of thread tell you the whole story .;)
Sorry, but it does not tell the whole story. As you can see from my response there is a lot of information that is still needed.

What happens when you run this code? Is there an error?

When 10 columns show, is there empty space at the right of the ListBox?

What is the value of ListBox.Columns?
What is the value of ListBox.ColumnWidths?
What is the value of ListBox.Width?

Unfortunately I cannot reproduce your problem without your file.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
40,113
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You cannot have more than 10 columns if you use AddItem. You have to use an array or a range to populate the listbox if you need more columns.
 

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
2,651
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
You cannot have more than 10 columns if you use AddItem. You have to use an array or a range to populate the listbox if you need more columns.
Good point. I was setting it up using RowSource.
 

Ali M

Board Regular
Joined
Oct 10, 2021
Messages
184
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
@RoryA thanks for clarification , actually I have not ever used array.
 

Forum statistics

Threads
1,186,001
Messages
5,955,239
Members
438,187
Latest member
DT1962

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top