Hi Sykes,
Thanks for responding. Sorry, I was not too clear in my query. I presently have a Timecard worksheet which lays out the days of the pay period. By entering the employee name and the in and out times each day from a dropdown list, we get the hours per shift worked by the employee, converted to decimal, with a total at the bottom for each pay rate that employee has. At the present time, we write the totals on the real timecard, clear the sheet and do the next employee.
Once all the timecards are done, we manually enter all the hours for each employee in another worksheet, named pay. That sheet is used for several tracking functions, ie. Hours per shift, day per category, etc., etc. What I'm trying to accomplish is-when we have completed calculating the hours for the employee, we can press a button and upload the information into the pay sheet. What I'm asking is can I use this timecard sheet, basically, as a userform.
I hope that helps a little. I'm old and tend to ramble a bit, something like hand quicker than brain.
Thanks,
Dean
I am a little confused. Are all employees located on a single Time sheet or is it only one employee? To me it sounds like this is the case. Personally I would approach this differently in that I would use some method of having all the data saved on one summary type of sheet for all employees after they are entered by some method such as a userform or a worksheet.
I have one example I use where work requests are sent from a userform and stored on a network drive in the form of an appended text file. The text file is then imported into another Workbook. In your case the userform would probably be part of the 'my' target workbook. When I say userform I am not excluding the possibility of this actually being a sheet used for entry.
I am assuming that each employee has an employee id or some other unique identifier. Their data would be store on the master sheet. As you select the employee on the master sheet their data is transferred to the Timecard which then could be printed after data is updated on it. The pay sheet would look at this data for its information.
In my case the master sheet has vba code which records the row number selected. Initially I did this by placing an x in the first column and then using vloopup for finding the x, but now I use 'Target.Row'. You can store this value either as a global variable in vba or even on the worksheet itself. In that way the other sheets, the Timecard and Pay, now know what row is selected on the master sheet.
On your sheet 'Master' you would have a worksheet_selection change piece of code similar to this assuming you are storing the row number in cell k1:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim wb As String
Dim z As Integer
On Error GoTo EndMacro
wb = ThisWorkbook.Name
z = Target.Row
Workbooks(wb).Worksheets("Master").Range("k1") = z
EndMacro:
End Sub
Now whenever you make a selection on the 'Master' sheet the row of the selection is stored in cell 'k1'.
What you do now is decide how you want the data on this row transferred to the Timecard.
This can be done as soon as the selection change is made by including in the selection change code something like this:
Code:
Dim EmployeeName As String
EmployeeName=Workbooks(wb).Worksheets("Master").Cells(z,2)
Workbooks(wb).Worksheets("Timecard").Range("B1") = EmployeeName
As soon as the selection change is made the Employee Name located in my example in column 2, on the row selected to the 'Timecard' sheet in cell 'B1'.
Now if you want to have the 'Pay' sheet reflect this just add a line and the Employee Name would be added to that sheet as well. No need to use lookup or any other search method, although you can for other reasons.
Note this is a two way street for me. On my second sheet I can make changes there and the changes are taken back to the 'Master' sheet, or I can make changes directly on the 'Master' sheet and they are transferred to the second sheet. This is done using the sheet code:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'your code goes here
End Sub
Hope this gives you some ideas on how you may want to do this, Target.Row works here as well as does Target.Column, Target.Address, etc. Each sheet in Excel has these types of events where you can place code which is run whenever one of these events occur. You do want to avoid an event which causes another event to run ending up in a loop, such as a cell change which causes a Worksheet_Change event which in turn is seen as a Worksheet_Change event and a loop starts. To prevent this add at the start of the code:
Code:
Application.EnableEvents = False
'and then at the end of the code after the error check, EndMacro: in my 'case
Application.EnableEvents = True
To speed up the transfer of data you may want to do this at the start as well:
Code:
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'after the code runs turn these back on with:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
This will often make things run much faster but this may depend on what you need to do with the data while it is running. You can turn these on or off on a line by line basis if need to make your code do what you want it to do (such as calculation).
Perry
