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
WiseOwl ExcelAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
In VBA  an unbound listbox is limited to 10 columns (0 to 9).
0
WiseOwl ExcelAuthor 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.
0
Martin LissOlder than dirtCommented:
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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Roy CoxGroup Finance ManagerCommented:
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

0
Martin LissOlder than dirtCommented:
Roy, given my code, why is that necessary?
0
Roy CoxGroup Finance ManagerCommented:
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

0
Roy CoxGroup Finance ManagerCommented:
If you want to filter the data then look at this example
FilterForm--1-.xlsm
0
Martin LissOlder than dirtCommented:
Roy, did you look at my code? It does all that.
0
WiseOwl ExcelAuthor 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!
0
Martin LissOlder than dirtCommented:
What start and end date did you use because when I did it I got more than one row,
0
WiseOwl ExcelAuthor Commented:
23-10-2017 to 03-12-2017 Sir,
0
Martin LissOlder than dirtCommented:
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

0
Martin LissOlder than dirtCommented:
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.
0
Martin LissOlder than dirtCommented:
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
0
WiseOwl ExcelAuthor 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,
0
Martin LissOlder than dirtCommented:
Try this. I removed the Date Picker controls and substituted textboxes like you had before.
29071511a.xlsm
0
WiseOwl ExcelAuthor 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
0
Martin LissOlder than dirtCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WiseOwl ExcelAuthor Commented:
Sir,

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

Regards
0
Martin LissOlder than dirtCommented:
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

0
WiseOwl ExcelAuthor 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

0
Martin LissOlder than dirtCommented:
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
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.