E
Ed
Hi, Eijaz. Let me preface everything by saying I'm pretty much a novice, so take my comments with that understanding. That being said, that first thing that comes to my mind when you describe what you want is one workbook with a page for each employee and maybe a page with employee numbers and names. Each workbook covers one month, and has the days down the rows (header in row 1, 1 Apr 2004 is row 2, etc.).
Throw in a UserForm with four buttons: Shift Start, Break Start, Break End, Shift End. Each button pops up an input box to ask for employee number. When the employee clicks OK, code captures the date and time along with the employee number as variables and uses them to put the right things in the right places. A message box pops up saying, "Hi, Eijaz. (That's the reason for the last page with employee numbers and names - by comparing the number, you get a personalized message. Could also be used by administration to give that person a specific message.) You have started/ended shift/break at <this time>."
Then again, I'm not experienced enough to know what happens when 200 employees try to access the same UserForm at the same time! So maybe another way is to have the code generate a separate log file (text file) that gets sent to a network folder; save the text file as "EmpNo_Date_Action_Time.txt" (for example), then have a macro that reads the names of the docs in that folder, breaks out the elements needed using Left, Right, and Mid into variables, and puts the info in the appropriate place on your spreadsheet.
Don't know if any of the above is valid, but that's what fell out of my head! Good luck.
Ed
Hi All,
I have made a Employee Break Monitoring Sheet which has 31 sheets for the 31 days (e.g "Apr_1_04") of a month & a Final Sheet which will show the Total Break Calculations for all Employees.
In a 9:30 hr shift, there is a 1st 15 min break, 2nd 30 mins break & 3rd again a 15 mins break. that totals upto 1hr break for every employee. so 9.30-1 = 8.30 hr is the productivity time they have to spend every day at work.
To achieve this, for every employee, i have made a specific workbook to calculate his downtime. so for 200 employees i have made 200 workbook. each workbook is around 1mb in size, so it takes up a lot of network drive space, which i donot want to happen.
Now, I have used Command Buttons (using Control Toolbox, not the Forms Toolbar) on each Employee worksheet (all for 31 sheets) like this:
_____________________________
| Shift Start |
|______________________________|
______________1st Break__________________
| Logout | | Login |
|___________________| |__________________|
______________2nd Break_________________
| Logout | | Login |
|___________________| |__________________|
______________3rd Break__________________
| Logout | | Login |
|___________________| |__________________|
______________________________
| Shift End |
|______________________________|
An employee has to click the Command button "Start Shift" When his/her shift Starts, which will enter a date & time (date long format) into a cell the Final sheet for that particular day.
On click the Command Buttons enable property is made false, & its caption property shows the Date & Time he logged in, so he/she cannot click the Button again & manipulate the time. Please note Final sheet protected.
Private Sub CommandButton1_click()
CommandButton1.Caption=Time
CommandButton1.Enable=False
Worksheets("Final").Activate
Range.Cells("c5").value=Time
End Sub
Similary, for other buttons, but here the format value entered on Final Sheet will be just Short Format e.g. "hh:mm".
Q1] Instead of making 200 Workbooks, Could i not make only 1 Shared Workbook with suppose say, 1 Entry sheet which will contain the above buttons, & also will show a ListBox which will show the users logged in to the worksheet & 31 Sheets for 31 days, containing usernames along with their breaktimes for that day & a Final Sheet which will show the usernames & their monthly logged in break times & productivity hours
.
But is it possible for the buttons to show different time to different users & change their state when multiple users are accessing the same buttons at the same time. IS IT POSSIBLE?
Is there a Better Alternative to this Idea which will make sense? Also, the reason for the ListBox is so that the users can click their respective names & then click the buttons to register their break time in the final sheet for that day.
There is a Coding for the listbox found in Excel which i want to use :
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Users = ActiveWorkbook.UserStatus
With Workbooks.Application.Worksheets(2)
For row = 1 To UBound(Users, 1)
.Cells(row, 1) = Users(row, 1)
.Cells(row, 2) = Users(row, 2)
Select Case Users(row, 3) 'this is not required & can be removed.
Case 1
.Cells(row, 3).Value = "Exclusive"
Case 2
.Cells(row, 3).Value = "Shared"
End Select
Next
End With
End Sub
HOW TO GO ABOUT IT? Anybody with some really bright ideas, which will save me time, space & memory & also will make the worksheets secure, so that nobody can change the time once they have clicked the buttons.
Awaiting a favourable response.
Eijaz
Throw in a UserForm with four buttons: Shift Start, Break Start, Break End, Shift End. Each button pops up an input box to ask for employee number. When the employee clicks OK, code captures the date and time along with the employee number as variables and uses them to put the right things in the right places. A message box pops up saying, "Hi, Eijaz. (That's the reason for the last page with employee numbers and names - by comparing the number, you get a personalized message. Could also be used by administration to give that person a specific message.) You have started/ended shift/break at <this time>."
Then again, I'm not experienced enough to know what happens when 200 employees try to access the same UserForm at the same time! So maybe another way is to have the code generate a separate log file (text file) that gets sent to a network folder; save the text file as "EmpNo_Date_Action_Time.txt" (for example), then have a macro that reads the names of the docs in that folder, breaks out the elements needed using Left, Right, and Mid into variables, and puts the info in the appropriate place on your spreadsheet.
Don't know if any of the above is valid, but that's what fell out of my head! Good luck.
Ed
Hi All,
I have made a Employee Break Monitoring Sheet which has 31 sheets for the 31 days (e.g "Apr_1_04") of a month & a Final Sheet which will show the Total Break Calculations for all Employees.
In a 9:30 hr shift, there is a 1st 15 min break, 2nd 30 mins break & 3rd again a 15 mins break. that totals upto 1hr break for every employee. so 9.30-1 = 8.30 hr is the productivity time they have to spend every day at work.
To achieve this, for every employee, i have made a specific workbook to calculate his downtime. so for 200 employees i have made 200 workbook. each workbook is around 1mb in size, so it takes up a lot of network drive space, which i donot want to happen.
Now, I have used Command Buttons (using Control Toolbox, not the Forms Toolbar) on each Employee worksheet (all for 31 sheets) like this:
_____________________________
| Shift Start |
|______________________________|
______________1st Break__________________
| Logout | | Login |
|___________________| |__________________|
______________2nd Break_________________
| Logout | | Login |
|___________________| |__________________|
______________3rd Break__________________
| Logout | | Login |
|___________________| |__________________|
______________________________
| Shift End |
|______________________________|
An employee has to click the Command button "Start Shift" When his/her shift Starts, which will enter a date & time (date long format) into a cell the Final sheet for that particular day.
On click the Command Buttons enable property is made false, & its caption property shows the Date & Time he logged in, so he/she cannot click the Button again & manipulate the time. Please note Final sheet protected.
Private Sub CommandButton1_click()
CommandButton1.Caption=Time
CommandButton1.Enable=False
Worksheets("Final").Activate
Range.Cells("c5").value=Time
End Sub
Similary, for other buttons, but here the format value entered on Final Sheet will be just Short Format e.g. "hh:mm".
Q1] Instead of making 200 Workbooks, Could i not make only 1 Shared Workbook with suppose say, 1 Entry sheet which will contain the above buttons, & also will show a ListBox which will show the users logged in to the worksheet & 31 Sheets for 31 days, containing usernames along with their breaktimes for that day & a Final Sheet which will show the usernames & their monthly logged in break times & productivity hours
.
But is it possible for the buttons to show different time to different users & change their state when multiple users are accessing the same buttons at the same time. IS IT POSSIBLE?
Is there a Better Alternative to this Idea which will make sense? Also, the reason for the ListBox is so that the users can click their respective names & then click the buttons to register their break time in the final sheet for that day.
There is a Coding for the listbox found in Excel which i want to use :
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Users = ActiveWorkbook.UserStatus
With Workbooks.Application.Worksheets(2)
For row = 1 To UBound(Users, 1)
.Cells(row, 1) = Users(row, 1)
.Cells(row, 2) = Users(row, 2)
Select Case Users(row, 3) 'this is not required & can be removed.
Case 1
.Cells(row, 3).Value = "Exclusive"
Case 2
.Cells(row, 3).Value = "Shared"
End Select
Next
End With
End Sub
HOW TO GO ABOUT IT? Anybody with some really bright ideas, which will save me time, space & memory & also will make the worksheets secure, so that nobody can change the time once they have clicked the buttons.
Awaiting a favourable response.
Eijaz