We help IT Professionals succeed at work.

Userform Listbox Data Load VBA Excel

Hellow Experts,

Please Solve my problem i have item to load in listbox if i post cmbaccname = New Ch Travels & Tours -AP and date start 23-10-2017 and end date 02-12-2017 then J loop 1-9 Works fine but onward loop 10 it shows error "Could not set list property or invalid property value please help me with my code in create button to understand me problem.

Regards
Book1.xlsm
Comment
Watch Question

Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
In VBA  an unbound listbox is limited to 10 columns (0 to 9).
WiseOwl ExcelFinance Exxcutive

Author

Commented:
Please Guide Solution how to bound and fit 22 columns it it accordingly my code and Sheet Thanks Aloot for Previous Information and Comment Sir.
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Add a sheet called "Helper" and then try this.

Private Sub CreateBtn_Click()

Dim r As Range
Dim i As Long
Dim lngLastRow As Long
Dim wsH As Worksheet

Set wsH = Sheets("Helper")
Me.ListBox1.Clear

wsH.UsedRange.Cells.ClearContents
For i = 4 To ThisWorkbook.Sheets("Purchase").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
           
    If ThisWorkbook.Sheets("Purchase").Cells(i, "E").Value = LgrFrm1.CmbAccName.Value And _
    ThisWorkbook.Sheets("Purchase").Cells(i, "A").Value >= CDate(LgrFrm1.TxtStrtDt.Value) And _
    ThisWorkbook.Sheets("Purchase").Cells(i, "A").Value <= CDate(LgrFrm1.TxtEndDt.Value) Then
        wsH.Range("A" & i - 3 & ":V" & i - 3) = Range("A" & i & ":V" & i).Value
    End If
Next i

With LgrFrm1.ListBox1
    lngLastRow = wsH.Range("A1048576").End(xlUp).Row
    Set r = wsH.Range("A1:V" & lngLastRow)
    .RowSource = r.Offset(1, 1).Resize(r.End(xlDown).Row - 1).Address(, , , True)
    .ColumnCount = r.Columns.Count
    .ColumnHeads = False
End With

End Sub

Open in new window

Roy CoxGroup Finance Manager

Commented:
Replace the code in the Initialize event with this

Private Sub UserForm_Initialize()

    Dim rRng As Range
    ''/// set a range for the data to load
    With Sheets("Purchase")
        Set rRng = .Range(.Cells(4, 1), .Cells(.Rows.Count, 22).End(xlUp))
    End With

    With Me.ListBox1
        ''/// ensure 22 columns are available
        .ColumnCount = 22
        .List = rRng.Value
        ''///make sure o item is selected
        .ListIndex = -1

    End With
    With Me.CmbAccName

        .AddItem "New Ch Travels & Tours -AP"

    End With

End Sub

Open in new window

Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Roy, given my code, why is that necessary?
Roy CoxGroup Finance Manager

Commented:
If you want to use Column Headers use the RowSource Property

Private Sub UserForm_Initialize()

    Dim rRng As Range
    ''/// set a range for the data to load
    With Sheets("Purchase")
        Set rRng = .Range(.Cells(4, 1), .Cells(.Rows.Count, 22).End(xlUp))
    End With

    With Me.ListBox1
        ''/// ensure 22 columns are available
        .ColumnCount = 22
        ''/// use RowSource if you want to use Column Headers
        .RowSource = rRng.Address(external:=True)
        .ColumnHeads = True
        ''///limit selection to one item
        .MultiSelect = fmMultiSelectSingle
        ''///make sure no item is selected
        .ListIndex = -1

    End With
    With Me.CmbAccName
        .AddItem "New Ch Travels & Tours -AP"
    End With
End Sub

Open in new window

Roy CoxGroup Finance Manager

Commented:
If you want to filter the data then look at this example
FilterForm--1-.xlsm
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Roy, did you look at my code? It does all that.
WiseOwl ExcelFinance Exxcutive

Author

Commented:
Sir @ Martin Liss Your Code enable to load or add only One Row into Listbox while save data to helper Sheet with space there where criteria not meet and you have used xlDown Property which stop on 1st space and next data not upload please hep me in this regard and oblige.

Sir @ Roy Cox Your Solution work fine to load 22 Columns into list box while i need to apply filter of Purchased account Name and Start and End Date which your solution didnt cover kindly help me in that regard and oblige.

Regards and Thanks to Both Experts for Input from their Precious comment and time and Advice!
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
What start and end date did you use because when I did it I got more than one row,
WiseOwl ExcelFinance Exxcutive

Author

Commented:
23-10-2017 to 03-12-2017 Sir,
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Your date formats are inconsistent. If I format the start and end dates as "yyyymmdd" I get 20171023  for the start date and  20170312 for the end date. The end date is prior to the start date so no matches are possible. The following is the code that I used which includes adding some testing values in Initialize and converting all dates to "yyyymmdd" format. The date values will be displayed in the Immediate Window.

Private Sub UserForm_Initialize()

With Me.CmbAccName

.AddItem "New Ch Travels & Tours -AP"

End With
'for testing
CmbAccName.ListIndex = 0
TxtStrtDt = "23-10-2017"
TxtEndDt = "03-12-2017"
End Sub

Open in new window


Private Sub CreateBtn_Click()

Dim r As Range
Dim i As Long
Dim lngLastRow As Long
Dim wsH As Worksheet
Dim dte As String

Set wsH = Sheets("Helper")
Me.ListBox1.Clear

Sheets("Helper").UsedRange.Cells.ClearContents
For i = 4 To ThisWorkbook.Sheets("Purchase").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
    dte = Format(CDate(ThisWorkbook.Sheets("Purchase").Cells(i, "A").Value), "yyyymmdd")
   Debug.Print i, dte, Format(CDate(LgrFrm1.TxtStrtDt.Text), "yyyymmdd"), Format(CDate(LgrFrm1.TxtEndDt.Text), "yyyymmdd")
    If ThisWorkbook.Sheets("Purchase").Cells(i, "E").Value = LgrFrm1.CmbAccName.Value And _
    dte >= Format(CDate(LgrFrm1.TxtStrtDt.Text), "yyyymmdd") And _
    dte <= Format(CDate(LgrFrm1.TxtEndDt.Text), "yyyymmdd") Then

        wsH.Range("A" & i - 3 & ":V" & i - 3) = Range("A" & i & ":V" & i).Value
    End If
Next i

With LgrFrm1.ListBox1
    lngLastRow = wsH.Range("A1048576").End(xlUp).Row
    Set r = wsH.Range("A1:V" & lngLastRow)
    .RowSource = r.Offset(1, 1).Resize(r.End(xlDown).Row - 1).Address(, , , True)
    .ColumnCount = r.Columns.Count
    .ColumnHeads = False
End With

End Sub

Open in new window

Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I just realized that when you say you used "23-10-2017 to 03-12-2017", that that's day-month-year format but when I look at the worksheet I see this
Cursor_and_Microsoft_Excel.jpgwhich with the exception of row 7 is month-day-year. Do you see the same thing?

I'm going out and won't be back for several hours.
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
OK, try this workbook which hopefully will work for you. I made some changes to your workbook.
  • I replaced the two date textboxes on the userform with DateTimePicker controls. If the DTPicker is not in your toolbox then right-click on the toolbox, select 'Additional Controls' and choose the 'Microsoft Date and Time Picker Control( 6.0 (SP6)'.
  • I changed the format of column 'M' on the Purchase sheet and column 'H' on the Helper sheet to 'Number' with zero decimal places.
  • I added, and hid, the Helper sheet
29071511.xlsm
WiseOwl ExcelFinance Exxcutive

Author

Commented:
Sir Your Provided Code with Date Picket is not for All Users as Everybody have to Instal DatePicket and as i have problem my Additional Controls not working and most of the people have same issue which i have searched over internet for the day 5Hrs but not find proper solution so kindly help with text box and also Please Consider that my System Date Formate is dd-mm-yyyy so this format Excel Consider Date nor String or Text. Thanks Aloot fir Earlier Valuable input.

Regards,
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Try this. I removed the Date Picker controls and substituted textboxes like you had before.
29071511a.xlsm
WiseOwl ExcelFinance Exxcutive

Author

Commented:
Sir,

Code Works Fine but it extract  All 6 Records on Sheet Helper but Load 4 Records on List Box. Screenshot is attached Please Advice.

Thanks For Previous Input and Advice and your Kindness.

Regards,
123.jpg
"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
That's surprising and it would be difficult for me to debug since I don't have the new data and our date formats are different, so you'll need to do that. As a start, put a breakpoint on line 30. You do that by clicking in the left-hand margin of that line. When the code gets there, type ? r,address in the Immediate Window and press return. Let me know what it says. For more information on debugging please see my article on debugging.
Private Sub CreateBtn_Click()

Dim r As Range
Dim i As Long
Dim lngLastRow As Long
Dim wsH As Worksheet
Dim wsP As Worksheet
Dim dte As String
Dim lngNewRow As Long

Set wsH = ThisWorkbook.Sheets("Helper")
Set wsP = ThisWorkbook.Sheets("Purchase")
wsP.Activate
wsH.UsedRange.Cells.ClearContents

For i = 4 To ThisWorkbook.Sheets("Purchase").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
    dte = Format(CDate(wsP.Cells(i, "A").Value), "yyyymmdd")
   'Debug.Print i, dte, Format(CDate(LgrFrm1.dtpStart.Value), "yyyymmdd"), Format(CDate(LgrFrm1.dtpEnd.Value), "yyyymmdd")
    If wsP.Cells(i, "E").Value = LgrFrm1.CmbAccName.Value And _
    dte >= Format(CDate(LgrFrm1.dtpStart.Value), "yyyymmdd") And _
    dte <= Format(CDate(LgrFrm1.dtpEnd), "yyyymmdd") Then
        lngNewRow = lngNewRow + 1
        wsH.Range("A" & lngNewRow & ":V" & lngNewRow) = wsP.Range("A" & i & ":V" & i).Value
    End If
Next i

With LgrFrm1.ListBox1
    lngLastRow = wsH.Range("A1048576").End(xlUp).Row
    Set r = wsH.Range("A1:V" & lngLastRow)
    .RowSource = r.Offset(0, 1).Resize(r.End(xlDown).Row).Address(, , , True)
    .ColumnCount = r.Columns.Count
    .ColumnHeads = False
End With

End Sub

Open in new window

WiseOwl ExcelFinance Exxcutive

Author

Commented:
Sir,

dtpStart Making Error in Last Provided Code Please Guide What is IT???

Regards
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I'm sorry, my fault, I posted code from the incorrect workbook. Here's the code the way it was in the 29071511a workbook. In this case put the breakpoint on line 29.
Private Sub CreateBtn_Click()

Dim r As Range
Dim i As Long
Dim lngLastRow As Long
Dim wsH As Worksheet
Dim wsP As Worksheet
Dim dte As String
Dim lngNewRow As Long

Set wsH = ThisWorkbook.Sheets("Helper")
Set wsP = ThisWorkbook.Sheets("Purchase")
wsP.Activate
wsH.UsedRange.Cells.ClearContents

For i = 4 To ThisWorkbook.Sheets("Purchase").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
    dte = Format(CDate(wsP.Cells(i, "A").Value), "yyyymmdd")
    If wsP.Cells(i, "E").Value = LgrFrm1.CmbAccName.Value And _
    dte >= Format(CDate(LgrFrm1.txtStartDate.Text), "yyyymmdd") And _
    dte <= Format(CDate(LgrFrm1.txtEndDate.Text), "yyyymmdd") Then
        lngNewRow = lngNewRow + 1
        wsH.Range("A" & lngNewRow & ":V" & lngNewRow) = wsP.Range("A" & i & ":V" & i).Value
    End If
Next i

With LgrFrm1.ListBox1
    lngLastRow = wsH.Range("A1048576").End(xlUp).Row
    Set r = wsH.Range("A1:V" & lngLastRow)
    .RowSource = r.Offset(0, 1).Resize(r.End(xlDown).Row).Address(, , , True)
    .ColumnCount = r.Columns.Count
    .ColumnHeads = False
End With

End Sub

Open in new window

WiseOwl ExcelFinance Exxcutive

Author

Commented:
Sir Martin Liss,

Thanks Aloot For your Input Guidance and Help around to figure my problem in the meanwhile i was playing with your 1st provide code with my requirement and it worked me well now and then i refresh with your last update code it worked best for me thanks aloot I Owe you in this regard your continuous Help.

Thanks And Regards,
Chears to Experts-Exchange.com



Private Sub CreateBtn_Click()

Dim r As Range
Dim i As Long
Dim lngLastRow As Long
Dim wsH As Worksheet

Set wsH = Sheets("Helper")

On Error Resume Next
Me.ListBox1.Clear

wsH.UsedRange.Cells.ClearContents
For i = 4 To ThisWorkbook.Sheets("Purchase").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row

    If ThisWorkbook.Sheets("Purchase").Cells(i, "E").Value = LgrFrm1.CmbAccName.Value And _
    ThisWorkbook.Sheets("Purchase").Cells(i, "A").Value >= CDate(LgrFrm1.TxtStrtDt.Value) And _
    ThisWorkbook.Sheets("Purchase").Cells(i, "A").Value <= CDate(LgrFrm1.TxtEndDt.Value) Then
        wsH.Range("A" & i & ":V" & i) = ThisWorkbook.Sheets("Purchase").Range("A" & i & ":V" & i).Value
    End If
Next i


    wsH.UsedRange.SpecialCells(xlCellTypeBlanks).Delete


Sheets("Purchase").Select
    Rows("3:3").Select
    Selection.Copy
    Sheets("Helper").Select
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
'
With LgrFrm1.ListBox1
    lngLastRow = wsH.Range("A" & Rows.Count).End(xlUp).Row
    Set r = wsH.Range("A1:V" & lngLastRow)
    .RowSource = r.Offset(0, 0).Resize(r.End(xlDown).Row).Address(, , , True)
    .ColumnCount = r.Columns.Count
    .ColumnHeads = False
End With
End Sub

Open in new window

Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017