Problem search data by selected combobox & datepicker tools

Solved/Closed
abdelfatah_0230 Posts 73 Registration date Thursday July 18, 2019 Status Member Last seen July 23, 2022 - Jul 5, 2020 at 04:39 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jul 23, 2020 at 10:47 AM
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

Related:

1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Updated on Jul 6, 2020 at 12:01 PM
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

0
abdelfatah_0230 Posts 73 Registration date Thursday July 18, 2019 Status Member Last seen July 23, 2022
Jul 7, 2020 at 03:14 PM
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
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552 > abdelfatah_0230 Posts 73 Registration date Thursday July 18, 2019 Status Member Last seen July 23, 2022
Jul 14, 2020 at 11:51 AM
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
0
abdelfatah_0230 Posts 73 Registration date Thursday July 18, 2019 Status Member Last seen July 23, 2022 > TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022
Jul 16, 2020 at 02:57 PM
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
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552 > abdelfatah_0230 Posts 73 Registration date Thursday July 18, 2019 Status Member Last seen July 23, 2022
Jul 23, 2020 at 10:47 AM
Awesome! Thanks for the feedback.
0