time tracking...

[senile2]

Intro: I volunteer with a bunch of mature people (old guys) who refurb PC's to be given away for free to needy families. We have a number of "Volunteers" who spend differenting amounts of time at the "club".

Have: we have a simple time tracking excel spreadsheet to keep track of volunteers and the hours they are there. It contains the following information: date, name, start, stop, total hours, and comments. I have created a "Start" userform that has date (prefilled in with the current date), name (filled in from a dropdown box) and start (filled in with current time). Works great.

Need: now the problem - I would like to have a second userform to be used when the volunteer gets ready to leave. would like to pick their name from a dropdown list, have a text field for date which defaults to todays date, the time they started picked up from the spreadsheet, a text field with the time they leave as default (but can be overwritten) , and a field for comments. I have the form designed - but I can't figure out how to read the data from the existing spreadsheet - display it on the form - let the user update the form and then store the data back over the existing record.

Any help or advice you can give is greatly appreciated....

senile2

[Leith Ross]

Hello senile2,

Welcome to the Forum!

Please post your workbook. It would help to see your layout and any formulas or code you have written. If your workbook contains sensitive information, please redact it before posting.

How To Post Your Workbook

1. At the bottom right of the Reply window, Click the button Go Advanced
2. At the top of the Your Message Window, in the first row of icons, Click the Paperclip icon.
3. Click the Add Files button at the top right in the dialog box. This displays the File Manager dialog.
4. Click the Select Files button at the bottom middle of the dialog.
5. In the new window Find the file you want to upload, select it, and Click Open.
6. You will now be back in the File Manager dialog. Click the bottom Middle button Upload File.
7. Wait until the file has completely uploaded before you exit the File Manager dialog.

File Manger Picture

[senile2]

Sorry, it took a while to find the reply function....lol

Thanks for the reply.... think I am close, but can't seem to put all the pieces together

senile2

[Leith Ross]

Hello senile2,

Thanks for posting the workbook. I am making some corrections and additions to the code.

Do you want the start time for person to be automatically entered on the Checkout Form when they select their name?

[senile2]

yes... but with the ability to type over it. Same as the "stop" code.

thanks again

senile2

[Leith Ross]

Hello senile2,

Happy 4th of July!
Here is the updated code for the UserForm "tap_hours_stop". the attached workbook has these changes.

Option Explicit

' These variables are available to all routines in this UserForm.

Dim DataTable As Range
Dim LastEntry As Range
Dim NextEntry As Range


Sub SaveData()

    Dim Data(1 To 5) As Variant
    
      ' Copy the entries to the worksheet cells.
      
        DataTable.Item(1, 1) = cbonamelist.Value
        DataTable.Item(1, 2) = txtdate.Value
        DataTable.Item(1, 3) = txtin.Value
        DataTable.Item(1, 4) = txtout.Value
        DataTable.Item(1, 6) = txtcomm
        
End Sub


Private Sub cbonamelist_Click()

    Dim Data As Variant
    Dim r    As Long
    
      ' Lookup the Time-In based on Name and Date.
      
        Data = DataTable.Resize(RowSize:=LastEntry.Row - DataTable.Row + 1).Value
        
        For r = 1 To UBound(Data)
            If Data(r, 2) = CDate(txtdate) And Data(r, 1) = cbonamelist.Value Then
                txtin.Value = Format(Data(1, 3), "hh:mm AM/PM")
            End If
        Next r
        
End Sub

Private Sub CmdEnter_Click()

    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Vol_hours")

        
        'check for a name
        If Trim(Me.cbonamelist.Value) = "" Then
            Me.cbonamelist.SetFocus
            MsgBox "Please enter your name"
            Exit Sub
        End If

        SaveData
        Unload Me

End Sub

Private Sub cmdClose_Click()
    Unload Me
End Sub


Private Sub UserForm_Initialize()

        Set DataTable = Sheet1.Range("A2:F2")
        Set LastEntry = Sheet1.Cells(Rows.Count, "A").End(xlUp)
        
        Set NextEntry = IIf(LastEntry.Row < DataTable.Row, DataTable, DataTable.Offset(1, 0))
    
        txtdate.Value = Format(Date, "mm/dd/yyyy")
        txtout.Value = Format(Time, "hh:mm")
        
End Sub

[senile2]

Leith.... never mind ... I think I figured it out. Needed to change the range for cdate and cbonamelist to match the user form... tried it again it it seems to be working. will continue to test.

How do I mark this closed? I think I am in the ball park enough to finish it..... still have to calculate the time total, but I think there are a lot of examples for that.

thanks again... again....lol

senile2

[senile2]

Leith, hope this gets to you.... Had a old timers moment and was looking at another website ..

I threw you a bit of a curve on the "out" userform (put the name first
instead of the date). I made the minor changes to correct that.

Is there a way to read the txtin field from the spreadsheet and display it
on the OUT userform? Right now it is blank, so if the user doesn't pay
attention, it overwrites the start time in the original record with a blank.
If it could read/display the existing data as default with the ability to
overwrite it .... I'm in business.

Don't know how to thank you other than to simply say thanks.....

Hope you have a good 4th.... ours was very wet. They still managed to get
the fireworks in .... but just barely.

Is there a place on the forum for "projects"? this seems like it would be a
good one .... It is frustrating to know you have the basics for a project and just need a push
to get it over the top. If there was a projects area, things like this one
could be stored for reference and future use.

Thanks again ... great forum


senile2

[Leith Ross]

Hello senile2,

I made the changes you asked for. The attached workbook has them. Although at this point in time, it is moot. But, it is here for you if you should want to look at it. I'll mark this solved for you.

[Leith Ross]

Hello senile2,

I know this is solved but I just couldn't leave this alone. I combined all the functions into a Single UserForm. This should make life easier for everyone. I have included the complete code for the form below. The attached workbook has the new form along with a macro "Run" that will bring it. All your original forms and code are still iintact.

Dim DataBase As Range
Dim NameCell As Range

Sub GetLastTimeEntry()

    Dim Rng As Range
    Dim TimeIn As Double
    Dim TimeOut As Double
    
        Set DataBase = Sheet1.Range("A1").CurrentRegion
        Set DataBase = DataBase.Offset(1, 0).Resize(RowSize:=DataBase.Rows.Count - 1)
        
        TextBox1.Value = ""
        TextBox2.Value = ""
        TextBox2.Visible = False
            
          ' Find the Row of the Name Entered.
            Set NameCell = DataBase.Find(ComboBox1.Value, , xlValues, xlWhole, xlByRows, xlPrevious, False, False, False)
            
            If NameCell Is Nothing Then
              ' Use the next empty row if the name was not found.
                Set NameCell = DataBase.Offset(DataBase.Rows.Count, 0).Cells(1, 2)
            
              ' Close the Drop Down List.
                SendKeys "{ESC}"
                DoEvents
                
              ' Set Time In to now.
                TextBox1.Value = Format(Now(), "hh:mm AM/PM")
                TextBox1.SetFocus
                Exit Sub
            Else
                TextBox1.Value = Format(NameCell.Offset(0, 1), "hh:mm AM/PM")
                TextBox1.SetFocus
            End If
            
        
          ' Time Out
            TextBox2.Visible = True
            TextBox2.SetFocus
            
                If IsEmpty(NameCell.Offset(0, 2)) Then
                    TextBox2.Value = Format(Now(), "hh:mm AM/PM")
                Else
                    TextBox2.Value = Format(NameCell.Offset(0, 2), "hh:mm AM/PM")
                End If
                
            TextBox2.SelStart = 0
            TextBox2.SelLength = Len(TextBox2)
            
          ' Comments
            TextBox3.Enabled = True
            TextBox3.Value = NameCell.Offset(0, 4)
            
End Sub

Private Sub ComboBox1_Click()
        Call GetLastTimeEntry
End Sub

Private Sub CommandButton1_Click()
  ' ENTER DATA - TRANSFER DATA FROM USERFORM TO THE WORKSHEET
  
        With NameCell
            .Offset(0, -1).Value = Format(Now(), "short date")
            .Offset(0, 0).Value = ComboBox1.Value
            .Offset(0, 1).Value = TextBox1.Value
            .Offset(0, 2).Value = TextBox2.Value
            .Offset(0, 4).Value = TextBox3.Value
        End With
        
End Sub

Private Sub CommandButton2_Click()
  ' CLOSE USERFORM
        Unload Me
End Sub

Private Sub TextBox1_Enter()
  ' TIME IN
        CommandButton1.Enabled = True
End Sub

Private Sub TextBox2_Enter()
  ' TIME OUT
        CommandButton1.Enabled = True
End Sub

Private Sub UserForm_Activate()
      ' Activate Names Drop Down List
        SendKeys "{F4}"
End Sub

Private Sub UserForm_Initialize()

    Dim Rng As Range
    
        Set Rng = Sheet2.Range("A1", Sheet2.Cells(Rows.Count, "A").End(xlUp))
        
        With ComboBox1
            .List = Rng.Value
            .Value = " Type In or Choose "
            .SelStart = 0
            .SelLength = Len(.Value)
        End With
    
        Label4.Caption = Format(Now(), "long date")
    
        CommandButton1.Enabled = False
    
        TextBox2.Visible = False
        TextBox3.Enabled = False
        
End Sub