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

excel calculating days

VBA codes to add comment :
Private Sub CommandButton1_Click() 'Comments are added
Application.ScreenUpdating = False
CommandButton2_Click                     'To delete If cell has comment
On Error Resume Next
For Each cell In Sheets("liste").Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
cell.AddComment
cell.Comment.Visible = True
cell.Comment.Shape.Select
cell.Comment.Shape.TextFrame.AutoSize = True
cell.Comment.Text Text:=CStr(DateDiff("d", Date, cell.Value))
Next

Call create_comment_shape
Application.ScreenUpdating = True
Range("A1").Activate
End Sub


Read more ...

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
...





Read more ...

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 columns. 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) V lstStItems.ColumnCount = 13
If lstStItems.ListCount <= 0 Then
lstStItems.Column = myarr
Else
lstStItems.AddItem myarr(0)
For n = 1 To 12
lstStItems.List(lstStItems.ListCount - 1, n) = myarr(n) V 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
Else
MsgBox " Any listbox item isn't selected !", vbCritical, ""
End If
...
"

excel add item to listbox combobox


Read more ...

Excel Vba :Copy The Listbox Items Into Closed Workbook

VBA Copy Data To The Selected Sheet Of The Closed Workbook


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

List of the listbox or listbox item can be copied into other closed workbook with a button.


Is pressed "Copy Listbox" button , sheets of the closed workbook are listed in the drop-down list. Codes to list sheets of the closed workbook on combobox control :
Sub add_sheets()
Dim m As Byte
Workbooks.Open (ThisWorkbook.Path & "\Database.xls")
        For m = 1 To Sheets.Count
        UserForm2.ComboBox1.AddItem Sheets(m).Name
         Next m
    ActiveWorkbook.Close True
 UserForm2.ComboBox1.Enabled = True
End Sub

For convenience, we put both workbooks in the same folder. Our workbooks ;
📗 address_book_listbox_copy.xls  (workbook with userform)
📗 Database.xls  (the workbook from which the copying was made)


✔️ So that ,user can copy the contents of the listbox to the selected sheet.

excel copy data to closed workbook

All of the codes we used for copying (Copy Listbox button on userform) :
Private Sub CommandButton10_Click()
Application.ScreenUpdating = False
If ListBox1.ListCount = 0 Then
MsgBox "No items that will be copied.", vbCritical, ""
Exit Sub
End If
Call add_sheets

If ComboBox1.Value = "" Then
MsgBox "Please Choose A WorkSheet From Drop-Down List ", vbInformation, ""
ComboBox1.SetFocus
Exit Sub
End If

Workbooks.Open (ThisWorkbook.Path & "\Database.xls")
Sheets(ComboBox1.Value).UsedRange.Cells.Clear
Sheets(ComboBox1.Value).Range("A2:L" & ListBox1.ListCount + 1) = ListBox1.List
Sheets(ComboBox1.Value).Columns.AutoFit
ActiveWorkbook.Close True
MsgBox "The Listbox Records Were Copied.", vbInformation, ""
ComboBox1.Clear
ComboBox1.Enabled = False
Application.ScreenUpdating = True
End Sub



Read more ...