YooooowaN, welcome to the board.
There are a number of things to consider as far as how your sheet is set up, but basically you just need to time stamp.
Here's one of many possibilities. Unless you want just one button (one in / out time for the entire group) it would require a button and a designated row in the worksheet for each person. If you put this button say in row 5 column A and put a person's name in row 5 column B, you could track the times horizontally across the sheet. For that you could use something like:
With Range("IV5").End(xlToLeft)(1, 2)
.Value = Time
.Font.Size = 8
I've changed the font size to make any time of day fit into a standard width column.
There are so many ways this could be done that you'll have to decide what you want.
Yes, you can do what you want. The following will prompt for an employee # and input the date & time in Sheet1:
Private Sub CommandButton1_Click()
Dim yourelate As String
Dim LastRow As Object
yourelate = Application.InputBox("Please enter employee ID #", "Employee ID", Type:=1)
Set LastRow = Sheets("Sheet2").Range("A65536").End(xlUp)
.Offset(1, 0) = yourelate
.Offset(1, 1) = Date
.Offset(1, 2) = Time
You can also check out the timesheet solutions at www.ozgrid.com for some ideas.
Note that anything's possible, but (and it's a big but) you're relying on employees to be honest about their time, specifically, when they go over their alotted break time. What's to keep someone else from logging in for them? Excel's security is notoriously weak, so someone can pretty quickly figure out how to alter/defeat an Excel solution.
As someone who has punched timecards for late emloyees (in my youth), I can tell you that no system is infallible. You're relying on the "locks on doors" principle, in that they only keep honest people honest.
We use phone log in's for our telemarketers (which can easily be defeated by a co-worker logging in for someone else), and biometric readers for our operations folks. Now that's a good investment, because it can't be cheated (unless you give someone your hand).
From what I've found, the best way to manage employee timeliness or tardiness is to actually manage to it. If there's a problem, you keep the records, not them. Cheaters and slackers will try their damndest to break whatever system you put in place; only good management skills and intuition will bust that.
Sorry for the rant (but we deal with it all the time) & hope that helps,
(Heya Ace! Nice rainy day here and I'm looking at/drooling on my Chugach Mountain Guide (avoiding housework))
Beautiful spring day here. (After about 2 weeks of 3 to 4 inches of rain a day (I measure it at work) and steady 60 to 80 mph winds with at least one 12 hour period of 90 mph+ & gusts up to 120! (Literally...) - Normal lousy whether for us at this time of year.
(Some of those mountians are about 40 feet from my bedroom window and go **** near straight up from there.)
In my experience, as long as the housework is done before the boss gets home you should be alright )
Thanks for your response (sorry it took me sometime to reply).
I'm quite new to VB, can you tell me where I can paste these codes (peenysaver)?
To add, will this also work on different worksheets (each employee has its own) where the employee's log in infos will be saved on a file that I myself can only access (all files are saved on a network server) for tracking purposes?
Say they each have a clock in and out button on their respective files. The employee's date, time and employee number (can we add another option - 1st, 2nd and 3rd brk?) will then be saved on a single worksheet.
Is it also possible to automatically get the computer's date and time once they press the button so the actual login time is entered?
I'll give you some steps to use the code that I posted on multiple sheets:
1) Open the VB Editor (ALT+F11)
2) Goto Insert-->Module
3) Paste the posted code in the new window that opens on the right
4) Change the name from Private Sub CommandButton1_Click() to-->
5) Adjust the referenced ranges as necessary
6) Exit the VBE (ALT+Q)
7) On a test sheet draw a button from the Forms toolbar and when prompted to assign a macro, select "Timestamp"
8) Click it and go.
Post back how it works for you and it can easily be adjust to fit your needs.
As for the system Date & Time, that's what the VBA functions "Date" & "Time" will enter"
As for the breaks being entered on a different sheet, you'd need to be more specific, but this ought to be a start.
I was able to repro it. I was thinking if its possible to add a prompt wherein they are also asked to enter if they'll be taking their 1st, 2nd, or 3rd break; or, if they are reporting back after each break (a single prompt for this one will do).
As an example:
Press the button
The first prompt in the program asks you for your employee ID
The next one will ask for what type of break you are about to take
As an overview, I am planning to have the same button on 20 files. Each employee has their own and these files are all connected to one excel file. So I need all the date, time, employee ID, and the type of break they will be taking all saved in one excel file (all files by the way are saved on a company server). Is there any way we can also get the filename (xls) on which the data came from (to prevent cheating)?
If possible, I'd reconsider individual workbooks and use worksheets within the same wb if possible. You can always use the xlVeryHidden property to ensure that your users don't see them and can't unhide them. Otherwise, you'll need to open the employee workbook first, transfer the data, then close it, which isn't that hard, but might be an issue for employees who are trying to get in "under the wire"...
But if you post the code that you came up with and add the correct path someone can ammend it to do what you want.