Listbox only display max 9 column

hhh

New Member
Joined
Mar 15, 2011
Messages
12
Hi All,

I need all kind bro/sis help. i recently found out that listbox can only display up to 9 column. I currently using search and display result in listbox but it only display up to 9 column but my userform have more than 9 items to display. As a result some info not display in userform. Any other method so that i can create search to display userform directly or maybe once click listbox it will populate full details userform.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Leith Ross

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
Hello hhh,

To break the 9 column barrier you need to load the ListBox by assigning a range to the RowSource property.
Code:
    ListBox1.ColumnCount = 5 
    ListBox1.RowSource = "a1:e4"
Sincerely,
Leith Ross
 

hhh

New Member
Joined
Mar 15, 2011
Messages
12
Hi,


where I should put the code. I aware that it will display all if I set row property. what I want is there is search button to diplsay what I search and items will display in listbox. but now currently it only display up to 9 colummn. As such when I select items in listbox, some info not display in activeuserform due to constraint.

My code:


Private Sub ListBox1_Click()

If Me.ListBox1.ListIndex = -1 Then 'not selected
MsgBox " No selection made"
ElseIf Me.ListBox1.ListIndex >= 1 Then 'User has selected
r = Me.ListBox1.ListIndex

With Me
.TextBox1.Value = ListBox1.List(r, 0)
.TextBox2.Value = ListBox1.List(r, 1)
.TextBox3.Value = ListBox1.List(r, 2)
.TextBox4.Value = ListBox1.List(r, 3)
.TextBox5.Value = ListBox1.List(r, 4)
.TextBox6.Value = ListBox1.List(r, 5)
.TextBox13.Value = ListBox1.List(r, 6)


If ListBox1.List(r, 7) = "Good" Then
.CheckBox1 = True
.CheckBox2 = False
.CheckBox3 = False
End If
If ListBox1.List(r, 7) = "Average" Then
.CheckBox2 = True
.CheckBox1 = False
.CheckBox2 = False
End If
If ListBox1.List(r, 7) = "Poor" Then
.CheckBox3 = True
.CheckBox1 = False
.CheckBox2 = False
End If





If ListBox1.List(r, 8) = "Good" Then
.CheckBox4 = True
.CheckBox5 = False
.CheckBox6 = False
End If
If ListBox1.List(r, 8) = "Average" Then
.CheckBox5 = True
.CheckBox4 = False
.CheckBox6 = False
End If
If ListBox1.List(r, 8) = "Poor" Then
.CheckBox6 = True
.CheckBox4 = False
.CheckBox5 = False
End If




If ListBox1.List(r, 9) = "Good" Then
.CheckBox7 = True
.CheckBox8 = False
.CheckBox9 = False
End If
If ListBox1.List(r, 9) = "Average" Then
.CheckBox8 = True
.CheckBox7 = False
.CheckBox9 = False
End If
If ListBox1.List(r, 9) = "Poor" Then
.CheckBox9 = True
.CheckBox7 = False
.CheckBox8 = False
End If




If .ListBox1.List(r, 10) = "Good" Then
.CheckBox10 = True
.CheckBox11 = False
.CheckBox12 = False
End If
If ListBox1.List(r, 10) = "Average" Then
.CheckBox11 = True
.CheckBox10 = False
.CheckBox12 = False
End If
If ListBox1.List(r, 10) = "Poor" Then
.CheckBox12 = True
.CheckBox10 = False
.CheckBox11 = False
End If


If ListBox1.List(r, 11) = "Good" Then
.CheckBox13 = True
.CheckBox14 = False
.CheckBox15 = False
End If
If ListBox1.List(r, 11) = "Average" Then
.CheckBox14 = True
.CheckBox13 = False
.CheckBox15 = False
End If
If ListBox1.List(r, 11) = "Poor" Then
.CheckBox15 = True
.CheckBox13 = False
.CheckBox14 = False
End If




.cmbAmend.Enabled = True 'allow amendment or
.cmbDelete.Enabled = True 'allow record deletion
.cmbAdd.Enabled = False 'don't want duplicate

End With
End If
End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,510
Messages
5,832,169
Members
430,114
Latest member
kefier

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