PDA

View Full Version : [SOLVED] Need Help, How to Add more than 10 Columns ?



veranda
04-08-2017, 11:32 AM
Sub TampilkanSemua()

Set DtCari = Sheets("Data")
If DtCari.Range("CI3").Value = "" Then
listCari.Clear
MsgBox "Database pelanggan kosong"
Exit Sub
End If
listCari.Clear
Set Status = DtCari.Range("CI3", DtCari.Range("CI3").End(xlDown))
With listCari
.ColumnCount = 11
.AddItem
.List(.ListCount - 1, 0) = "NOMOR_SPAJ"
.List(.ListCount - 1, 1) = "NOMOR_POLIS"
.List(.ListCount - 1, 2) = "NAMA_TERTANGGUNG"
.List(.ListCount - 1, 3) = "UP"
.List(.ListCount - 1, 4) = "PREMI_DSR"
.List(.ListCount - 1, 5) = "PREMI_TPUP"
.List(.ListCount - 1, 6) = "TOTAL_PREMI"
.List(.ListCount - 1, 7) = "RATE"
.List(.ListCount - 1, 8) = "MPI"
.List(.ListCount - 1, 9) = "PARTIAL"
.List(.ListCount - 1, a) = "ALOKASI INVESTASI"
'.List(.ListCount - 1, 11) = "KODE AGEN"
'.List(.ListCount - 1, 12) = "NAMA AGEN"
'.List(.ListCount - 1, 13) = "SUBMISSION DATE"
'.List(.ListCount - 1, 14) = "EFFECTIVE DATE"
'.List(.ListCount - 1, 15) = "ACCEPT BY UNDERWRITING"
'.List(.ListCount - 1, 16) = "TANGGAL AKHIR ASURANSI"
'.List(.ListCount - 1, 17) = "TANGGAL AKHIR PEMBAYARAN"
.ColumnWidths = 58 & ";" & 62 & ";" & 125 & ";" & 50 & ";" & 50 & ";" & 65 & ";" & 65 & ";" & 30 & ";" & 30 & ";" & 30 & ";" & 30
End With

With DtCari
Set rgTampil = Status.SpecialCells(xlCellTypeVisible)
For Each sTampil In rgTampil
With listCari
.AddItem
.List(.ListCount - 1, 0) = sTampil.Offset(0, -86).Value
.List(.ListCount - 1, 1) = sTampil.Offset(0, -85).Value
.List(.ListCount - 1, 2) = sTampil.Offset(0, -84).Value
.List(.ListCount - 1, 3) = sTampil.Offset(0, -16).Value
.List(.ListCount - 1, 4) = sTampil.Offset(0, -15).Value
.List(.ListCount - 1, 5) = sTampil.Offset(0, -14).Value
.List(.ListCount - 1, 6) = sTampil.Offset(0, -13).Value
.List(.ListCount - 1, 7) = sTampil.Offset(0, -19).Value
.List(.ListCount - 1, 8) = sTampil.Offset(0, -18).Value
.List(.ListCount - 1, 9) = sTampil.Offset(0, -17).Value
'.List(.ListCount - 1, 10) = sTampil.Offset(0, -6).Value
'.List(.ListCount - 1, 11) = sTampil.Offset(0, -85).Value
'.List(.ListCount - 1, 12) = sTampil.Offset(0, -85).Value
'.List(.ListCount - 1, 13) = sTampil.Offset(0, -85).Value
End With
Next sTampil
End With


End Sub

mana
04-08-2017, 05:53 PM
Private Sub UserForm_Initialize()
Dim myList() As String
ReDim myList(1 To 1, 1 To 11)
Dim W As String
Dim i As Long, j As Long


W = Join(Array(20, 30, 40, 50, 50, 30, 30, 30, 30, 30, 30), ";")
With ListBox1
.Clear
.ColumnCount = UBound(myList, 2)
.List = myList
.ColumnWidths = W
.List(.ListCount - 1, 0) = "a"
.List(.ListCount - 1, 1) = "b"

For i = 1 To 3
.AddItem
For j = 1 To .ColumnCount
.List(.ListCount - 1, j - 1) = i & j
Next
Next

End With


End Sub

snb
04-09-2017, 03:32 AM
Use list


Private Sub Userform_initialkize()
listbox1.list=sheets(1).range("A1:Q20").value
end sub

see also: http://www.snb-vba.eu/VBA_Fill_combobox_listbox_en.html

veranda
04-10-2017, 08:56 PM
Private Sub UserForm_Initialize()
Dim myList() As String
ReDim myList(1 To 1, 1 To 11)
Dim W As String
Dim i As Long, j As Long


W = Join(Array(20, 30, 40, 50, 50, 30, 30, 30, 30, 30, 30), ";")
With ListBox1
.Clear
.ColumnCount = UBound(myList, 2)
.List = myList
.ColumnWidths = W
.List(.ListCount - 1, 0) = "a"
.List(.ListCount - 1, 1) = "b"

For i = 1 To 3
.AddItem
For j = 1 To .ColumnCount
.List(.ListCount - 1, j - 1) = i & j
Next
Next

End With


End Sub


Ah, It's Works!
Thankyou very much :)

snb
04-11-2017, 12:31 AM
Do not use .Additem to populate a listbox/combobox.

@man

If you use .list , then .clear is redundant.

mana
04-11-2017, 05:12 AM
I see. You're right.
Thank you.

veranda
04-11-2017, 11:26 PM
Thanks,
I've deleted .Clear so it works perfectly