How to put timestamp on a cell using a button VBA

[serenitylai]

Here's suppose to happen when the employee clicks on the search button based on the employee number the name of the employee will show on the Textbox2 and by clicking the LOGIN Button the timestamp will appear on the spreadsheet on that date as shown on the second picture. I have finished the code on the first button which is the Search button. here's the code as well:


Private Sub Search_Click()
On Error GoTo MyErrorHandler:
Dim empnum As Long
Dim name As String


empnum = TextBox1.Text


name = Application.WorksheetFunction.VLookup(empnum, Sheet2.Range("A3:B9"), 2, False)
TextBox2.Text = name
MyErrorHandler:
If Err.Number = 1004 Then
MsgBox "Employee Not Present in the table."
End If
If Err.Number = 13 Then
MsgBox "please put employee number"
End Sub


18603
18605

[SamT]

This depends on how you filled the Date header cells (F2 - AJ2.)

If you placed a Date ("1-Mar-2017") in F2 or the Formula in F2 is =DATE() AND the formula in G2 is =F2+1, AND G2 was filled across to the right (with Ctrl+R,) then we can simply "DateCol =Rows(2).Find(Date).Column"

If the Formula in F2 is =NOW(), or if you entered values like "Mar-1," "Mar-2," "Mar-3," etc in every cell, We must do it differently.


I would make this timecard form differently. First I would not use the Search and Login buttons.



Option Explicit

'Placed at module level for use anywhere in UserForm Code
Dim EmpNoRow As Long

Private Sub TextBox1_Change()

With Me.TextBox1
'Suggested.
'Verify Valid EmpNo Code
'This is NOT Code. You must "fill in the Blanks"

'If Len(,Value} <> 6 _
'Or If .Value < Min(Range("A:A")) _
'Or If .Value > Max AA etc _
'Then
'MsgBox "Invalid Emp No"
'.SetFocus
'Exit Sub
'End If


'THIS IS Code
EmpNoRow = Sheet2.Range("A:A").Find(.Value).Row
If EmpNoRow = 0 Then
MsgBox "Employee Not Present in the table."
.SetFocus
Exit Sub
End If
End With

With Sheet2
Me.TextBox2.Text = .Cells(EmpNoRow, "B").Value
.Rows(2).Find(Date).EntireColumn.Cells(EmpNoRow) = Format(Now, "h:mmAM")
End With

End Sub