Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Break monitoring workbook
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA to break out worksheets from a workbook | Excel Discussion (Misc queries) | |||
monitoring links | Excel Discussion (Misc queries) | |||
monitoring use | Excel Discussion (Misc queries) | |||
Monitoring Data | Excel Discussion (Misc queries) | |||
How do I break links in a workbook? | Excel Worksheet Functions |