You can populate and format a textbox with something like:
me.textbox2.value = format(now,"hh:mm")
But if you're reading the pc's date/time, maybe you could just show them what
you're doing and not have them type at all.
I put textbox1, label1, commandbutton1 and commandbutton2 in a userform:
Option Explicit
Dim LogWks As Worksheet
Dim ActionIsCheckIn As Boolean
Dim CurTime As Date
Dim DestCell As Range
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
With DestCell
If ActionIsCheckIn Then
.Offset(0, 2).Value = CurTime
MsgBox "Check In time logged"
Else
.Value = Me.TextBox1.Value
.Offset(0, 1).Value = CurTime
MsgBox "Check Out time logged"
End If
End With
Call CommandButton1_Click
End Sub
Private Sub TextBox1_Change()
Dim iRow As Long
Dim LastRow As Long
Dim FirstRow As Long
Dim myMsg As String
Call CheckWriteButton
If Me.CommandButton2.Enabled = False Then
myMsg = "Please type your name"
Else
With LogWks
FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
'assume they're checking out
ActionIsCheckIn = False
Set DestCell = .Cells(LastRow + 1, "A")
myMsg = "Check Out Time" & " -- " & Format(CurTime, "hh:mm:ss")
For iRow = LastRow To FirstRow Step -1
If StrComp(.Cells(iRow, "A").Value, _
Me.TextBox1.Value, vbTextCompare) = 0 Then
ActionIsCheckIn = IsEmpty(.Cells(iRow, "C"))
If ActionIsCheckIn Then
Set DestCell = .Cells(iRow, "A")
myMsg = "Check In Time" & " -- " _
& Format(CurTime, "hh:mm:ss")
End If
Exit For
End If
Next iRow
End With
End If
Me.Label1.Caption = myMsg
End Sub
Private Sub UserForm_Initialize()
Set LogWks = Worksheets("sheet2")
With Me.CommandButton1
.Enabled = True
.Caption = "Cancel"
End With
With Me.CommandButton2
.Enabled = False
.Caption = "Ok"
End With
CurTime = Now
Me.Label1.Caption = "Please type your name"
End Sub
Sub CheckWriteButton()
Dim okToWrite As Boolean
okToWrite = True
If Trim(Me.TextBox1.Value) = "" Then
okToWrite = False
End If
Me.CommandButton2.Enabled = okToWrite
End Sub
===========
This seemed to work ok. It just started at the bottom of the log worksheet and
looked for a matching name. When it found the first one, it looked to see if
column C (check In) was filled. If it was, then that meant that the user should
be checking out--if it was empty, they should be checking back in.
But I'm not sure I'd use something like this.
If I (as an employee) forgot to check in, then I'd be off by one cycle. And if
I were a trouble maker, I'd type someone else's name--or just mistype mine own.
You could spend lots of time making sure things are ok.
Maybe you could add a combobox (instead of a textbox) that only allows the user
to pick from a list. Then add a top secret password for each person to validate
their entry.
In fact, if you look at the textbox properties, you'll see a passwordchar that
can be used to hide the typed value.
Maybe validating name against password would be sufficient. But then you'd
still have trouble with people forgetting to log in/log out.
======
I think I'd check eBay for time clocks!
Good luck,
Can you
give me again another code that will automatically show the precent time
in textbox.
Or when they key in there name under textbox1 the time will show in
textbox2 as there time in then recorded the data to the worksheet. Next
time they key in there name will the time out the time will show to
texbox3.
Thanks,
jeff
jeff said:
To: Dave
Thanks a lot Dave your very helpfull..
Thanks once again,
jeff
Dave Peterson said:
You may want to look into Data|Form.
If you try this and find it less than optimal, you could use John Walkenbach's
enhanced data form:
http://j-walk.com/ss/dataform/index.htm
I put 3 textboxes on a user form:
Textbox1 held the name, textbox2&3 held the times
I put 2 buttons on the form:
commandbutton1 was cancel
commandbutton2 was "write to sheet"
And I wrote to the next open row based on column A in sheet2.
Here's my code:
Option Explicit
Dim blkProc As Boolean
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim oRow As Long
With Worksheets("sheet2")
oRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
.Cells(oRow, "A").Value = Me.TextBox1.Value
.Cells(oRow, "B").Value = TimeValue(Trim(Me.TextBox2.Value))
.Cells(oRow, "C").Value = TimeValue(Trim(Me.TextBox3.Value))
End With
blkProc = True
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
blkProc = False
Me.CommandButton2.Enabled = False
End Sub
Private Sub TextBox1_Change()
If blkProc Then Exit Sub
Call CheckWriteButton
End Sub
Private Sub TextBox2_Change()
If blkProc Then Exit Sub
Call CheckWriteButton
End Sub
Private Sub TextBox3_Change()
If blkProc Then Exit Sub
Call CheckWriteButton
End Sub
Private Sub UserForm_Initialize()
With Me.CommandButton1
.Enabled = True
.Caption = "Cancel"
End With
With Me.CommandButton2
.Enabled = False
.Caption = "Write to Sheet"
End With
End Sub
Sub CheckWriteButton()
Dim iCtr As Long
Dim okToWrite As Boolean
Dim testTime As Variant
okToWrite = True
If Trim(Me.TextBox1.Value) = "" Then
okToWrite = False
Else
For iCtr = 2 To 3
testTime = ""
On Error Resume Next
testTime = TimeValue(Trim(Me.Controls("textbox" & iCtr).Value))
On Error GoTo 0
If testTime = "" Then
okToWrite = False
Exit For
Else
If Application.Text(testTime, "[h]:mm") _
= Trim(Me.Controls("textbox" & iCtr).Value) Then
'ok
Else
'not yet
okToWrite = False
End If
End If
Next iCtr
End If
Me.CommandButton2.Enabled = okToWrite
End Sub
I also assumed that you'd type h:mm or hh:mm (no seconds).
jeff wrote:
i make excel dialog i put ( textbox for Name, text box for Time In, text box for Time out, command button for Ok and Command Button for Cancel) the inside of excel workbook i have plenty of work sheet with the name of our deferent staf. What i want is if i fill up all the information in excel dialog it will transfer the data to worksheet....
thanks a lot for the help...
