How to use ListBox.AddItem method for more than 10 Column in excel VBA

To Download this file Please goto end of this Page........................

Today we will learn from a different view. Generally there is some restrictions to add items with more than 10 column through ListBox.AddItem method.

But in this video I will use 15 column on listbox through listbox.additem method. If you interested or want to learn then watch this tutorial or follow this blogspot.

VBA code which is used in this tutorials

Option Explicit
' this is for only one checkbox will checked and others will non-checked

Private Sub CheckBox1_Click()
If Me.CheckBox1 = True Then
Me.CheckBox2 = False
Me.CheckBox3 = False
Me.CheckBox4 = False
Me.CheckBox5 = False
Me.CheckBox6 = False
Me.CheckBox7 = False
End If

End Sub

Private Sub CheckBox2_Click()
If Me.CheckBox2 = True Then
Me.CheckBox1 = False
Me.CheckBox3 = False
Me.CheckBox4 = False
Me.CheckBox5 = False
Me.CheckBox6 = False
Me.CheckBox7 = False
End If
End Sub

Private Sub CheckBox3_Click()
If Me.CheckBox3 = True Then
Me.CheckBox2 = False
Me.CheckBox1 = False
Me.CheckBox4 = False
Me.CheckBox5 = False
Me.CheckBox6 = False
Me.CheckBox7 = False
End If
End Sub

Private Sub CheckBox4_Click()
If Me.CheckBox4 = True Then
Me.CheckBox2 = False
Me.CheckBox3 = False
Me.CheckBox1 = False
Me.CheckBox5 = False
Me.CheckBox6 = False
Me.CheckBox7 = False
End If
End Sub

Private Sub CheckBox5_Click()
If Me.CheckBox5 = True Then
Me.CheckBox2 = False
Me.CheckBox3 = False
Me.CheckBox4 = False
Me.CheckBox1 = False
Me.CheckBox6 = False
Me.CheckBox7 = False
End If
End Sub

Private Sub CheckBox6_Click()
If Me.CheckBox6 = True Then
Me.CheckBox2 = False
Me.CheckBox3 = False
Me.CheckBox4 = False
Me.CheckBox5 = False
Me.CheckBox1 = False
Me.CheckBox7 = False
End If
End Sub

Private Sub CheckBox7_Click()
If Me.CheckBox7 = True Then
Me.CheckBox2 = False
Me.CheckBox3 = False
Me.CheckBox4 = False
Me.CheckBox5 = False
Me.CheckBox6 = False
Me.CheckBox1 = False
End If
End Sub
' ......................................................................................................................

Private Sub CommandButton1_Click()
Me.TextBox1 = LCase(Me.TextBox1)
If Me.CheckBox7 = True Then
Call search_all
Exit Sub
End If

Dim sh As Worksheet
Set sh = Sheets("Sheet1")
Dim i As Long
Dim x As Long
Dim p As Integer, k As Integer

If Me.CheckBox1 = True Then
k = 1 ' A col
End If

If Me.CheckBox2 = True Then
k = 2
End If

If Me.CheckBox3 = True Then
k = 3
End If
If Me.CheckBox4 = True Then
k = 6
End If
If Me.CheckBox5 = True Then
k = 10
End If

If Me.CheckBox6 = True Then
k = 11
End If

With Me.ListBox1
.Clear
.ColumnCount = 15
.Font.Size = 9
.List = sh.Range("A1", "O1").Value
.RemoveItem 0
.ColumnWidths = "100,100,80,60,60,80,60,70,80,80,80,50,50,50,40"

.AddItem
.List(0, 0) = "Title"
.List(0, 1) = "AUTHOR"
.List(0, 2) = "EDITOR/TRANSLATOR"
.List(0, 3) = "EDITION/YEAR"
.List(0, 4) = "IMPRINT"
.List(0, 5) = "SUBJECT"
.List(0, 6) = "PAGE/VOL"
.List(0, 7) = "TYPE"
.List(0, 8) = "CALL NO"
.List(0, 9) = "ISBN"
.List(0, 10) = "ACC NO"
.List(0, 11) = "PRICE"
.List(0, 12) = "RACK"
.List(0, 13) = "SHELF"
.List(0, 14) = "SERIAL NO"
.Selected(0) = True
For i = 2 To sh.Range("A" & Rows.Count).End(xlUp).Row
For x = 1 To Len(sh.Cells(i, 1))
p = Me.TextBox1.TextLength

If LCase(Mid(sh.Cells(i, k), x, p)) = Me.TextBox1 And Me.TextBox1 <> "" Then
.AddItem
.List(.ListCount - 1, 0) = sh.Cells(i, 1)
.List(.ListCount - 1, 1) = sh.Cells(i, 2)
.List(.ListCount - 1, 2) = sh.Cells(i, 3)
.List(.ListCount - 1, 3) = sh.Cells(i, 4)
.List(.ListCount - 1, 4) = sh.Cells(i, 5)
.List(.ListCount - 1, 5) = sh.Cells(i, 6)
.List(.ListCount - 1, 6) = sh.Cells(i, 7)
.List(.ListCount - 1, 7) = sh.Cells(i, 8)
.List(.ListCount - 1, 8) = sh.Cells(i, 9)
.List(.ListCount - 1, 9) = sh.Cells(i, 10)
.List(.ListCount - 1, 10) = sh.Cells(i, 11)
.List(.ListCount - 1, 11) = sh.Cells(i, 12)
.List(.ListCount - 1, 12) = sh.Cells(i, 13)
.List(.ListCount - 1, 13) = sh.Cells(i, 14)
.List(.ListCount - 1, 14) = sh.Cells(i, 15)

End If
Next x
Next i
End With

End Sub

Private Sub UserForm_Initialize()
Me.CheckBox7 = True

End Sub

'........................................................................................................................................

If you want to search item from any where from your database, then just follow the codes carefully. Here I created a module and copy the code which is used in the commandbutton to search item by keywords of TextBox. Here I replaced me with ListBox1 and reset the k value which is from 1 to 15.

Option Explicit Sub search_all() UserForm2.TextBox1 = LCase(UserForm2.TextBox1) Dim sh As Worksheet Set sh = Sheets("Sheet1") Dim i As Long Dim x As Long Dim p As Integer, k As Integer With UserForm2.ListBox1 .Clear .ColumnCount = 15 .Font.Size = 9 .List = sh.Range("A1", "O1").Value .RemoveItem 0 .ColumnWidths = "100,100,80,60,60,80,60,70,80,80,80,50,50,50,40" .AddItem .List(0, 0) = "Title" .List(0, 1) = "AUTHOR" .List(0, 2) = "EDITOR/TRANSLATOR" .List(0, 3) = "EDITION/YEAR" .List(0, 4) = "IMPRINT" .List(0, 5) = "SUBJECT" .List(0, 6) = "PAGE/VOL" .List(0, 7) = "TYPE" .List(0, 8) = "CALL NO" .List(0, 9) = "ISBN" .List(0, 10) = "ACC NO" .List(0, 11) = "PRICE" .List(0, 12) = "RACK" .List(0, 13) = "SHELF" .List(0, 14) = "SERIAL NO" .Selected(0) = True For i = 2 To sh.Range("A" & Rows.Count).End(xlUp).Row For x = 1 To Len(sh.Cells(i, 1)) p = UserForm2.TextBox1.TextLength For k = 1 To 15 If LCase(Mid(sh.Cells(i, k), x, p)) = UserForm2.TextBox1 And UserForm2.TextBox1 <> "" Then .AddItem .List(.ListCount - 1, 0) = sh.Cells(i, 1) .List(.ListCount - 1, 1) = sh.Cells(i, 2) .List(.ListCount - 1, 2) = sh.Cells(i, 3) .List(.ListCount - 1, 3) = sh.Cells(i, 4) .List(.ListCount - 1, 4) = sh.Cells(i, 5) .List(.ListCount - 1, 5) = sh.Cells(i, 6) .List(.ListCount - 1, 6) = sh.Cells(i, 7) .List(.ListCount - 1, 7) = sh.Cells(i, 8) .List(.ListCount - 1, 8) = sh.Cells(i, 9) .List(.ListCount - 1, 9) = sh.Cells(i, 10) .List(.ListCount - 1, 10) = sh.Cells(i, 11) .List(.ListCount - 1, 11) = sh.Cells(i, 12) .List(.ListCount - 1, 12) = sh.Cells(i, 13) .List(.ListCount - 1, 13) = sh.Cells(i, 14) .List(.ListCount - 1, 14) = sh.Cells(i, 15) End If Next k Next x Next i End With End Sub

The following lines are ver important to additems more than 10 column using ListBox.AddItem method

With UserForm2.ListBox1 .Clear .ColumnCount = 15 .Font.Size = 9 .List = sh.Range("A1", "O1").Value .RemoveItem 0 .ColumnWidths = "100,100,80,60,60,80,60,70,80,80,80,50,50,50,40"

End with

I hope You will find your quaries as resolved.

For Support and FeedBack

Please follow my blogspot and subscribe my channel

If you want to download this file Please click on the download button. Download link will show you after 10 sec.