Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

application defined error when populate in listbox on form

0

Hello

I  try  populating  all  of  columns in listbox  on userform based  on two  textboxes  .

should  populate  data  between  two  dates when  fill textbox6,7 together 

it  gives  me  error application defined or object defined error in this  line

While SH1.Cells(i, 1) <> ""
Private Sub TextBox7_AfterUpdate()
Dim i As Long
Dim vData()
j = 0
While SH1.Cells(i, 1) <> ""
Me.ListBox2.Clear
If SH1.Cells(i, 1).Value >= CDate(Me.TextBox6.Text) And _
SH1.Cells(i, 1).Value <= CDate(Me.TextBox7.Text) Then
ReDim Preserve vData(0 To 10, 0 To j)
vData(0, j) = SH1.Cells(i, 1).Value
vData(1, j) = SH1.Cells(i, 2).Value
vData(2, j) = SH1.Cells(i, 3).Value
vData(3, j) = SH1.Cells(i, 4).Value
vData(4, j) = SH1.Cells(i, 5).Value
vData(5, j) = SH1.Cells(i, 6).Value
vData(6, j) = SH1.Cells(i, 7).Value
vData(7, j) = SH1.Cells(i, 8).Value
vData(8, j) = SH1.Cells(i, 9).Value
vData(9, j) = SH1.Cells(i, 10).Value
vData(10, j) = SH1.Cells(i, 11).Value
vData(11, j) = SH1.Cells(i, 12).Value
vData(12, j) = SH1.Cells(i, 13).Value
vData(13, j) = SH1.Cells(i, 14).Value
vData(14, j) = SH1.Cells(i, 15).Value
j = j + 1
End If
Wend
UserForm1.ListBox2.Column = vData
End Sub
    

I hope  somebody  guides  me .

Answer
Discuss

Answers

0
Selected Answer

Ali

Your code failed because you did not tell VBA what the value variable i was. Therefore it was looking for cell(0,1) which does not exist. Also you did not increment i  and the first dimension of your array vData had a final index of 10 (but your code tried to populate elements in positions 11, 12, 13 and 14).

In the revised file attached, I have modified your code as shown below (changes in bold). Note that I moved the ListBox2.Clear command to before your loop runs and ensured i is incremented (even if j is not).

Private Sub TextBox7_AfterUpdate()
Dim i As Long
Dim vData()
Me.ListBox2.Clear
'set array index
j = 0
' set first row to check
i = 6
While SH1.Cells(i, 1) <> ""


If SH1.Cells(i, 1).Value >= CDate(Me.TextBox6.Text) And _
SH1.Cells(i, 1).Value <= CDate(Me.TextBox7.Text) Then
    ' changed from 0 to 10  to 14
    ReDim Preserve vData(0 To 14, 0 To j)
    vData(0, j) = SH1.Cells(i, 1).Value
    vData(1, j) = SH1.Cells(i, 2).Value
    vData(2, j) = SH1.Cells(i, 3).Value
    vData(3, j) = SH1.Cells(i, 4).Value
    vData(4, j) = SH1.Cells(i, 5).Value
    vData(5, j) = SH1.Cells(i, 6).Value
    vData(6, j) = SH1.Cells(i, 7).Value
    vData(7, j) = SH1.Cells(i, 8).Value
    vData(8, j) = SH1.Cells(i, 9).Value
    vData(9, j) = SH1.Cells(i, 10).Value
    vData(11, j) = SH1.Cells(i, 12).Value
    vData(12, j) = SH1.Cells(i, 13).Value
    vData(13, j) = SH1.Cells(i, 14).Value
    vData(14, j) = SH1.Cells(i, 15).Value
    'increment array index
    j = j + 1

    End If
' loop down worksheet
i = i + 1
Wend

If j > 0 Then
    UserForm1.ListBox2.Column = vData
    Else
    UserForm1.ListBox2.AddItem "*No matches *"
End If

End Sub

Hope this fixes things for you.

Discuss

Discussion

Hi John
It 's changed  error  could not set column property .invalid property value. in this  line 
UserForm1.ListBox2.Column = vData
Ali M (rep: 18) Sep 9, '22 at 11:40 am
Hi Ali, that's odd since it worked fine for me. I assume you used my file so will check later when I get home or tomorrow 
John_Ru (rep: 3992) Sep 9, '22 at 12:30 pm
Hi Ali

My file worked fine for me when I typed 03/07/2022 in TextBox6, then pressed Tab to TextBox7 where I typed 10/07/2022. The ListBox was then populated with filtered dates and data.

If your date range gives no matches however, vData is nothing so you get the error. I have revised the Answer and file to replace your line:
UserForm1.ListBox2.Column = vData

with:
If j > 0 Then
    UserForm1.ListBox2.Column = vData
    Else
    UserForm1.ListBox2.AddItem "*No matches *"
End If


It should work fine now. Please mark the Answer as Selected if so.
John_Ru (rep: 3992) Sep 9, '22 at 1:54 pm
Hi Ali

My file worked fine for me when I typed 03/07/2022 in TextBox6, then pressed Tab to TextBox7 where I typed 10/07/2022. The ListBox was then populated with filtered dates and data.

If your date range gives no matches however, vData is nothing so you get the error. I have revised the Answer and file to replace your line:
UserForm1.ListBox2.Column = vData

with:
If j > 0 Then
    UserForm1.ListBox2.Column = vData
    Else
    UserForm1.ListBox2.AddItem "*No matches *"
End If


It should work fine now. Please mark the Answer as Selected if so.
John_Ru (rep: 3992) Sep 9, '22 at 1:54 pm
Hi Ali

My file worked fine for me when I typed 03/07/2022 in TextBox6, then pressed Tab to TextBox7 where I typed 10/07/2022. The ListBox was then populated with filtered dates and data.

If your date range gives no matches however, vData is nothing so you get the error. I have revised the Answer and file to replace your line:
UserForm1.ListBox2.Column = vData

with:
If j > 0 Then
    UserForm1.ListBox2.Column = vData
    Else
    UserForm1.ListBox2.AddItem "*No matches *"
End If


It should work fine now. Please mark the Answer as Selected if so.
John_Ru (rep: 3992) Sep 9, '22 at 1:54 pm
Hi john ,
I see  now  the  problem  was date  format  into  textbox   is  not  matched  with  date   format inside  the  sheet . so  the date  format  inside  the  sheet is  based on PC . I  corrected  the  date  for  the  sheet and  the  userform  to  become  the  same  thing  as in PC . important   addition  condition  if  the  date  is  not  the  same  format  to  inform  me  this  is  wrong . truley  I  don't  expect the  problem  from date  format  .  I  thought   there  is  missed thing  in the  code .
thanks  very  much  for  your assistance .
Ali M (rep: 18) Sep 9, '22 at 2:40 pm
Sorry Ali but I'm not clear - is your problem solved? 
John_Ru (rep: 3992) Sep 9, '22 at 2:51 pm
you don't  seem  to  understand  last comment .
just  I  wanted to  share  my  problem  with  you  , as  you  have  ever  said  the  first file  you attached  works   for  you  , yes  you're  right . my  problem  was  Date format in  textboxes  is  not  matched  with Date format  inside  the  sheet  in  column A   that's  why  show  error .so   you've  solved problem whether in first or  second  file.
everything  is  ok .
Ali M (rep: 18) Sep 9, '22 at 3:39 pm
Great, thanks for confirming that Ali 
John_Ru (rep: 3992) Sep 9, '22 at 4:21 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login