Banner

Saturday, June 10, 2017

Create Employee Present Data Base In Excell Worksheet VBA




Private Sub UserForm_Initialize()
Me.TextBox1.Text = Format(Date, "DD/MMM/YYYY")
For i = 2 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
Me.ListBox1.AddItem Sheet1.Cells(i, 1).Value
Next i
End Sub

Private Sub SpinButton1_SpinDown()
On Error Resume Next
Me.TextBox1.Text = CDate(Me.TextBox1.Text) - 1
For i = 0 To Me.ListBox1.ListCount - 1
Me.ListBox1.Selected(i) = False
Next i
End Sub

Private Sub SpinButton1_SpinUp()
On Error Resume Next
Me.TextBox1.Text = CDate(Me.TextBox1.Text) + 1
For i = 0 To Me.ListBox1.ListCount - 1
Me.ListBox1.Selected(i) = False
Next i
End Sub

Private Sub CommandButton1_Click()
Dim i As Long, x As Long
For i = 2 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
For x = 0 To Me.ListBox1.ListCount - 1
For a = 2 To 18
If Me.ListBox1.Selected(x) = True Then
If Sheet1.Cells(i, 1).Value = Me.ListBox1.List(x, 0) And _
Sheet1.Cells(1, a).Value = CDate(Me.TextBox1.Text) Then
Sheet1.Cells(i, a).Value = "Present"
End If
End If
Next a
Next x
Next i
End Sub

No comments:

Post a Comment

Please do not enter any spam message in comment box