Sunday, May 21, 2017

Product Filtering Between Two Dates Using Userform

Between the selected dates ,product-based filtering can be done using the userform.

        When the dates (start date,end date) are entered in text boxes and if report button is pressed  the userform  elongation effect is activated and listbox appears.
When choosing a date to add into text boxes,the date userform was used instead of date picker control :

The found results are listed on the listbox.

Saturday, May 20, 2017

Excel Vba Class Modul Examples

       Classes are a powerful tool in intermediate to advanced level VBA programming.
In three separate workbooks, we have created useful userforms using class modules :

- Fast percentage calculation userform
- Product discount calculation according to quantity,price and percent rate
- Hiding / displaying the columns of sheet (column management with userform)

Wednesday, May 17, 2017

Find Data Between Two Dates In Excel Vba

          Records in between two specific dates easily can be filtered using drop-down lists .
Ago ,drop-down lists (combo boxes) were filled with unique values as ascending order using ADO connection.  Thus, it is easier to choose between the dates on the worksheet. The used codes for this :

Set con = CreateObject("adodb.connection")
    #If VBA7 And Win64 Then
con.Open "provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.FullName & ";extended properties=""excel 12.0;hdr=yes"""
con.Open "provider=microsoft.jet.oledb.4.0;data source=" & ThisWorkbook.FullName & ";extended properties=""excel 8.0;hdr=yes"""
    #End If       
Set rs = CreateObject("adodb.recordset")
sorgu = "select Date from [Page1$] group by Date"
rs.Open sorgu, con, 1, 1
While Not rs.EOF
Sheets("Page1").ComboBox1.AddItem VBA.Format(rs("Date").Value, "")
Sheets("Page1").ComboBox2.AddItem VBA.Format(rs("Date").Value, "")

Then ,the data between the two dates selected from combo boxes is filtered through Vba codes :

 lngStart = VBA.CDate(Sheets("Page1").ComboBox1) 'assume this is the start date
    lngEnd = VBA.CDate(Sheets("Page1").ComboBox2) 'assume this is the end date
    If lngStart > lngEnd Then
    MsgBox "The Start Date Can Not Be Bigger Than The End Date.", vbCritical, ""
    Exit Sub
    End If
    Sheets("Page1").Range("B:B").AutoFilter field:=1, _
    Criteria1:=">=" & lngStart, Operator:=xlAnd, Criteria2:="<=" & lngEnd

The filtered data can be copied to other sheet if it wished.

Saturday, April 22, 2017

Creating Price Quote With Userform That Contains Cascading Drop-Down Lists

        In this study, we created a userform that automatically displayed to make it easier to enter data into the worksheet. The userform contains dependent combo boxes( combobox in which the list depends on the selection made in another combobox),textbox and button.

The Scripting Dictionary Object was used to fill in combo boxes :
Dim SD As Object
Set SD = CreateObject(“Scripting.Dictionary”)
For Each x In Supplier
SD(x) = “”
Next x
ComboBox1.List = SD.keys
excel price quote