Find word then timestamp

[geekboy.101]

Hi All,

I'm creating a simple attendance log for my students, Something that they can open from a shared location to log into.

I am fairly new to creating a userform but i got it down up to logging in. It is the time out button i'm having problems with and it has come to a point where i am about to bang my head against the wall... literally.

This site has helped me out before, and hopefully can help me out again.
Here's the short story. I want the button to search for a Name (the value is from the a combobox) under column B, then look at columns C and D. If column C is empty, return a message saying "no time in yet". If column C has an entry, i need it now to look at column D. If it is empty, then timestamp. If column D has an entry, return a message saying "you're already logged in." I know I'm getting ambitious but I have attached the sample file I've been working on.

Thanks in advance everyone!

 

[geekboy.101]

Hi here is the link to the file. I don't know how to attach it here.

http://www.mediafire.com/?v6dvwlbqsct8q09

Thanks in advance again guys

 

[AlphaFrog]

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> UserForm_Initialize()<br><br>    <SPAN style="color:#007F00">' populate Group combobox list</SPAN><br>    <SPAN style="color:#007F00">' store column B data in 2nd hidden column in combobox</SPAN><br>    cboName.ColumnCount = 2<br>    cboName.ColumnWidths = ";0"<br>    <SPAN style="color:#00007F">With</SPAN> Sheet1<br>        cboName.List = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> cboName_Change()<br>    <SPAN style="color:#007F00">' Populate textbox with data from hidden column in combobox</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> cboName.ListIndex <> -1 <SPAN style="color:#00007F">Then</SPAN><br>        TextBox1.Text = cboName.List(cboName.ListIndex, 1)<br>        cmdTimeIn.Enabled = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> cmdTimeIn_Click()<br>    <SPAN style="color:#00007F">Dim</SPAN> iRow   <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> ws     <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> Employee <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">If</SPAN> cboName.Value = "" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN> <SPAN style="color:#007F00">'No name selected</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> ws = Worksheets("Sheet2")<br>    <br>    <SPAN style="color:#007F00">'Allows code to make changes to protected sheet</SPAN><br>    <SPAN style="color:#007F00">'but sheet remains protected from user changes</SPAN><br>    ws.Protect Password = "z858199", UserInterFaceOnly:=<SPAN style="color:#00007F">True</SPAN><br>    <br>    <SPAN style="color:#007F00">' Search column B for Employee match</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> rngEmployee = ws.Range("B:B").Find(What:=cboName.Value, _<br>                                           LookIn:=xlValues, _<br>                                           LookAt:=xlWhole, _<br>                                           SearchOrder:=xlByRows, _<br>                                           SearchDirection:=xlNext, _<br>                                           MatchCase:=False)<br>                                        <br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> rngEmployee <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#007F00">'Employee matched</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> rngEmployee.Offset(, 1) = "" <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#007F00">'Column C is empty</SPAN><br>            MsgBox "No time in yet. ", vbInformation, "No Time-In"<br>        <SPAN style="color:#00007F">Else</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> rngEmployee.Offset(, 1) = "" <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#007F00">'Column D is empty</SPAN><br>                rngEmployee.Offset(, -1).Value = Format(Date, "mm/dd/yy")<br>                rngEmployee.Offset(, 1).Value = Format(Now, "hh:mm")<br>            <SPAN style="color:#00007F">Else</SPAN><br>                MsgBox "You're already logged in.", vbInformation, "Logged In"<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        End <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Else</SPAN><br>        <SPAN style="color:#007F00">' No Employee match</SPAN><br>        <SPAN style="color:#007F00">' find first empty row in database</SPAN><br>        iRow = ws.Cells(Rows.Count, 1).<SPAN style="color:#00007F">End</SPAN>(xlUp).Offset(1, 0).Row<br>        ws.Cells(iRow, 1).Value = Format(Date, "mm/dd/yy")<br>        ws.Cells(iRow, 2).Value = Me.cboName.Value<br>        ws.Cells(iRow, 3).Value = Me.TextBox1.Value<br>        ws.Cells(iRow, 4).Value = Format(Now, "hh:mm")<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    <SPAN style="color:#007F00">'clears the data from the form</SPAN><br>    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>    <SPAN style="color:#007F00">'Prevents triggering a cboName_Change</SPAN><br>    Me.cboName.Value = ""<br>    Me.TextBox1.Value = ""<br>    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br><br>    ws.Activate<br>    cmdTimeIn.Enabled = <SPAN style="color:#00007F">False</SPAN><br><br>End <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[geekboy.101]

Hey. Thanks for the quick reply. Works really well. I actually copied your code for the Time In button and modified it a bit for the Time Out button. Here's what I cam up with:

Private Sub cmdTimeOut_Click()
    Dim iRow   As Long
    Dim ws     As Worksheet
    Dim Employee As Range
    
    If cboName.Value = "" Then Exit Sub 'No name selected
    
    Set ws = Worksheets("Sheet2")
    
    'Allows code to make changes to protected sheet
    'but sheet remains protected from user changes
    ws.Protect Password = "z858199", UserInterFaceOnly:=True
    
    ' Search column B for Employee match
    Set rngEmployee = ws.Range("B:B").Find(What:=cboName.Value, _
                                           LookIn:=xlValues, _
                                           LookAt:=xlWhole, _
                                           SearchOrder:=xlByRows, _
                                           SearchDirection:=xlNext, _
                                           MatchCase:=False)
                                        
    If Not rngEmployee Is Nothing Then
        'Employee matched
        If rngEmployee.Offset(, 1) = "" Then
            'Column C is empty
            MsgBox "No time in yet. ", vbInformation, "No Time-In"
        Else
            If rngEmployee.Offset(, 3) = "" Then
                'Column E is empty
                rngEmployee.Offset(, 3).Value = Format(Now, "hh:mm")
            End If
     End If
    
    'clears the data from the form
    Application.EnableEvents = False    'Prevents triggering a cboName_Change
    Me.cboName.Value = ""
    Me.TextBox1.Value = ""
    Application.EnableEvents = False

    ws.Activate
    cmdTimeIn.Enabled = False
End If
End Sub

can you please advise if i did it correctly. Coz it works, but when you're learning like me, you want to know if there are some stuff which i could have left off or removed.

Anyways, Thanks a billion AlphaFrog. You've been a great help.

 

[geekboy.101]

Now I know this has morphed already past my initial question.
But I was wondering what will be the best way to compute the duration of hours spent in class by subtracting column D from column E after they time out.

 

[AlphaFrog]

One mistake I made was that this should be True in both procedures,

    [COLOR="Green"]'clears the data from the form[/COLOR]
    Application.EnableEvents = False    [COLOR="Green"]'Prevents triggering a cboName_Change[/COLOR]
    Me.cboName.Value = ""
    Me.TextBox1.Value = ""
    Application.EnableEvents = [COLOR="Red"]True[/COLOR]

can you please advise if i did it correctly. Coz it works, but when you're learning like me, you want to know if there are some stuff which i could have left off or removed.

I didn't test much. It looks like you did it right except the last line should be...
cmdTimeOut.Enabled = False
...and then you renable the command button in the cboName_Change procedure. You also need to add a test when they click on the TimeOut button, that there has to be already a Time In for that name. Right now they could time out without having Timed In.

I would change a few cosmetic things e.g. When the Form initializes, the two buttons should be disabled. Then enable them when the user selects a name. But only enable them if appropriate for that selected name (test if Time In and Time Out exists). Use a Label object on the Form to provide feedback instead of message boxes.

... the best way to compute the duration of hours spent in class by subtracting column D from column E after they time out.

            If rngEmployee.Offset(, 3) = "" Then
                'Column E is empty
                With rngEmployee
                    .Offset(, 3).Value = Format(Now, "hh:mm")
                    [COLOR="Red"].Offset(, 4).Value = Format(.Offset(, 3) - .Offset(, 2), "hh:mm")[/COLOR]
                End With

 

[geekboy.101]

I corrected everything you mentioned.
And added the code for computing the duration. I was trying to do it by specifying the range and cells, but i figured you have to be entering the formula for the correct cell as well and your way to use Offset is much simpler and much accurate.

I'll upload the final file for future references for anyone who might need it.
And maybe a few suggestions regarding cosmetic features, security, etc. will help. Thanks again for your help.