Wednesday, September 21, 2016

Calculating Days Between Two Dates - 2

Subtracting The Today's Date From Cell Date And Viewing Result In Cell Comment

          In this study, we have used the same template again. When the button on userform is clicked  , today's date is subtracted from the date in cell. Result can be viewed on the added comment in cell.

Example :  20.10.2017 - 21.09.2016 (Today's date) = 394 days

Tuesday, September 20, 2016

Excel Vba Calculating Days Between Two Dates

Calculating Days Between Date In Cell And Today 

            On userform ,days can be calculated until date in cell from today's date. Related codes :

TextBox15.Value = DateDiff("d", Date, Cells(ActiveCell.Row, 1).Value)
If Not IsDate(Cells(ActiveCell.Row, 1)) Then
TextBox15.Value = "Incorrect Value !"
End If

Saturday, September 17, 2016

Excel Adding Item To Listbox And Combobox

Excel Vba From Textbox To Listbox & Combobox

           We conducted the processes without using the worksheet only on the userform in this sample.

With button on the userform, item can be added from textbox to combobox and to listbox .Also item in listbox can be updated and can be deleted with buton,

The listbox contains 13 column.Therefore we have used an array to fill the listbox :

myarr = Array(cmbBtch.Value, txtBtchNo.Value, cmbSupCode.Value, txtSupName.Value, txtDate.Value, _
cmbItmCode.Value, txtItmName.Value, txtBox.Value, txtTara.Value, txtGwght.Value, txtTtara.Value, txtNwght.Value, txtPrice.Value)
lstStItems.ColumnCount = 13
If lstStItems.ListCount <= 0 Then
lstStItems.Column = myarr
lstStItems.AddItem myarr(0)
For n = 1 To 12
lstStItems.List(lstStItems.ListCount - 1, n) = myarr(n)
Next n

With an other button ,text boxes and combo boxes can be filled with listbox selected item's value :
If lstStItems.ListIndex <> -1 Then
        With lstStItems
        cmbBtch.Value = .List(.ListIndex, 0)
        txtBtchNo.Value = .List(.ListIndex, 1)
        cmbSupCode.Value = .List(.ListIndex, 2)
        txtSupName.Value = .List(.ListIndex, 3)
        txtDate.Value = .List(.ListIndex, 4)
        cmbItmCode.Value = .List(.ListIndex, 5)
        txtItmName.Value = .List(.ListIndex, 6)
        txtBox.Value = .List(.ListIndex, 7)
        txtTara.Value = .List(.ListIndex, 8)
        txtGwght.Value = .List(.ListIndex, 9)
        txtTtara.Value = .List(.ListIndex, 10)
        txtNwght.Value = .List(.ListIndex, 11)
        txtPrice.Value = .List(.ListIndex, 12)
        End With
       MsgBox " Any listbox item isn't selected !", vbCritical, ""
    End If

Saturday, September 10, 2016

Excel Vba Dependent (Cascaded) Filtering With Ado

            The columns (based on column B,C,D) can be filtered as dependent with userform quickly.

The userform contains 3 textbox and 3 listbox. When any textbox is clicked ,the userform extends downwards later listbox that associated to textbox appears . 

Data in column are listed as unique and are sorted alphabetic . With text boxes,value can be searched  within the listbox with Ado Connection :
 Dim s As String, con As Object
Application.ScreenUpdating = False

    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=No;"""
    con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 8.0;HDR=No;"""
    #End If
    s = "select distinct f2 from [Main$A3:D" & Range("D" & Rows.Count).End(xlUp).Row & "]  where not isnull(f2)"
    If TextBox1.Text <> "" Then s = s & " and f2 like '" & VBA.UCase(LCase(TextBox1.Text)) & "%'"
On Error GoTo hata
    ListBox1.Column = con.Execute(s).getrows
Application.ScreenUpdating = True

In addition, the filtering is done with "AutoFilter" method within the worksheet.

Filtering results can be copied to other sheet.

Saturday, September 3, 2016

Excel Vba :Copy The Listbox Items Into Closed Workbook

We have used address book template as sample in this study.

List of the listbox (or filtered items) can be copied into other closed workbook with a button.

Is pressed "copy button", sheets of the closed workbook are listed in the drop-down list. So that ,user can copy the contents of the listbox to the page it wants.