Problem search data by selected combobox & datepicker tools [Solved]

Report
Posts
57
Registration date
Thursday July 18, 2019
Status
Member
Last seen
October 8, 2020
-
Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
-
hello i no know why this doesn't work this code and there is no error it should when i select from combo box it should show data in listbox and if i choose the date from and to by datepicker tools it should the data based on from date and to date
i truly appreciate if any body help
this my source data on userform and worksheet




and theses my codes
Option Explicit
Dim sh As Worksheet

Sub fill_ListBox()
Dim i As Long, dtp1 As Date, dtp2 As Date, cmb As Variant

ListBox1.Clear

For i = 2 To sh.Range("G" & Rows.Count).End(xlUp).Row
If DTPicker1.Value = "" Then
dtp1 = sh.Range("C" & i).Value
dtp2 = sh.Range("C" & i).Value
ElseIf DTPicker2.Value = "" Then
dtp1 = DTPicker1.Value
dtp2 = DTPicker1.Value
Else
dtp1 = DTPicker1.Value
dtp2 = DTPicker2.Value
End If
If ComboBox1.Value = "" Or ComboBox1.ListIndex = -1 Then
cmb = sh.Range("G" & i).Value
Else
cmb = ComboBox1.Value
End If

If sh.Range("C" & i).Value >= dtp1 And sh.Range("C" & i).Value <= dtp2 And _
sh.Range("G" & i).Value = cmb Then
With ListBox1
.AddItem sh.Range("A" & i).Value
.List(.ListCount - 1, 1) = sh.Range("B" & i).Value 'Data from Col B
.List(.ListCount - 1, 2) = sh.Range("C" & i).Value 'Data from Col C
.List(.ListCount - 1, 3) = sh.Range("D" & i).Value 'Data from Col D
.List(.ListCount - 1, 4) = sh.Range("E" & i).Value 'Data from Col E
.List(.ListCount - 1, 5) = sh.Range("F" & i).Value 'Data from Col f
.List(.ListCount - 1, 6) = sh.Range("G" & i).Value 'Data from Col g
End With
End If
Next
End Sub

Private Sub ComboBox1_Change()
fill_ListBox
End Sub

Private Sub DTPicker1_Change()
fill_ListBox
End Sub

Private Sub DTPicker2_Change()
fill_ListBox
End Sub

Private Sub UserForm_Initialize()
Dim i As Long, dic As Object

Set sh = sheet1
Set dic = CreateObject("Scripting.Dictionary")

For i = 2 To sh.Range("G" & Rows.Count).End(xlUp).Row
dic(sh.Range("G" & i).Value) = Empty
Next
ComboBox1.List = dic.keys
ListBox1.ColumnCount = 7
fill_ListBox
End Sub

1 reply

Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
445
Hi Abdel,

You are setting your sheet incorrectly in the beginning of the Initialize part:
Set sh = sheet1
should be:
Set sh = Sheets("sheet1")

Not sure why you don't get an error for that.

Edit: Tried "Set sh = sheet1" in a simple test code and it worked. When I run your code, I'm getting a compiling error, saying that a variable is not defined and the "sheet1" text is highlighted. When I change "sheet1" into "Sheets("sheet1")", the error is gone.


PS: When posting codes, could you use the drop down arrow attached to the code button you would normally click and select "basic". This will make your code look like it is in VBA and makes it easier to reference code lines, since they are numbered.

Best regards,
Trowa

Posts
57
Registration date
Thursday July 18, 2019
Status
Member
Last seen
October 8, 2020

thanks Trowa i changed as you said but i do not find any change
this is my file
https://www.dropbox.com/scl/fi/a4k54obah2x8uk43faouu/xc.xls?dl=0&rlkey=jlu45cec2s08mbofrp13ywbyk
Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
445 >
Posts
57
Registration date
Thursday July 18, 2019
Status
Member
Last seen
October 8, 2020

Hi Abdel,

Looking at your file I notice that the left DatePicker is called DTPicker2 and the right one DTPicker1. So to display results in the listbox, we have to change the right DatePicker to select a start date before the dates in you table. Your file works fine for me.

But you probably did that on purpose, since I remember you having your entire sheet reversed.

The other thing that I can think of, is that you don't have the right references activated. You probably already used this, otherwise DatePicker wouldn't show. Top menu of VBA, Tools > References.

These are the references checked for my Excel:

Since there is an Object in the code, make sure the 3 references mentioning Object are checked.

Hopefully that solves it.

Best regards,
Trowa
Posts
57
Registration date
Thursday July 18, 2019
Status
Member
Last seen
October 8, 2020
>
Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020

thanks Trowa about your notes you're right i changed directions dtpicker1,2 actually i don't note that your notes are very valuable ,it helped me so much , now it works perfect many thanks for your assistance
Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
445 >
Posts
57
Registration date
Thursday July 18, 2019
Status
Member
Last seen
October 8, 2020

Awesome! Thanks for the feedback.